June 4, 2012 at 9:18 am
Hi
I am trying to get TotalDue
by Year,Quarter,Month format from Sales.SalesOrderHeader Table in AdventureWorks Database
For Example:
Query should display year as 2001
Quarter as :q1 or q2 or q3
Month as :jan,feb etc,
then totaldue
Use OrderDate and TotalDue Columns from Sales.SalesOrderHeader Table in AdventureWorks Database
Please help regarding this query
June 4, 2012 at 9:26 am
Show us what you have done so far to solve your problem. What problems are you having getting the solution you ar trying to develop?
June 4, 2012 at 10:00 am
I am struggling to write,not able to come with any logic. Any Any body guide me please
June 4, 2012 at 10:09 am
Smash125 (6/4/2012)
I am struggling to write,not able to come with any logic. Any Any body guide me please
Sorry, but from your original post, I really have no idea what you are trying to accomplish. What seems to be the problem you are running into? What have you done so far?
June 4, 2012 at 10:56 am
So you start with selecting the columns you want to group by, and finish with the column(s) you want to sum or aggregate. You should have a group by clause at the end of your query that lists the columns you want to group on. Does this make sense? This seems like homework, so I am not going to write out the SQL for you.
Jared
CE - Microsoft
June 4, 2012 at 11:54 am
I Have come up with this query.
WITH C AS(
select
year(OrderDate) AS OrderYear,
MonthForTheYear= CASE WHEN MONTH(OrderDate)=1 THEN 'January'
WHEN MONTH(OrderDate)=2 THEN 'Feburary'
WHEN MONTH(OrderDate)=3 THEN 'March'
WHEN MONTH(OrderDate)=4 THEN 'April'
WHEN MONTH(OrderDate)=5 THEN 'May'
WHEN MONTH(OrderDate)=6 THEN 'June'
WHEN MONTH(OrderDate)=7 THEN 'July'
WHEN MONTH(OrderDate)=8 THEN 'August'
WHEN MONTH(OrderDate)=9 THEN 'September'
WHEN MONTH(OrderDate)=10 THEN 'October'
WHEN MONTH(OrderDate)=11 THEN 'November'
WHEN MONTH(OrderDate)=12 THEN 'December'
End,
Quarter = CASE WHEN MONTH(OrderDate) BETWEEN 1 and 3 THEN 'Q1'
WHEN MONTH(OrderDate) BETWEEN 4 and 6 THEN 'Q2'
WHEN MONTH(OrderDate) BETWEEN 7 and 9 THEN 'Q3'
WHEN MONTH(OrderDate) BETWEEN 9 and 12 THEN 'Q4'
End,
TotalDue
from Sales.SalesOrderHeader
)
SELECT OrderYear,Quarter,MonthForTheYear,SUM(TotalDue) FROM C
GROUP BY OrderYear,Quarter,MonthForTheYear
ORDER BY OrderYear,MonthForTheYear,Quarter
June 4, 2012 at 2:44 pm
You might want to look up the DATENAME() function for the month names and the DATEPART() function for the quarters. Or you might want to use a calendar table as was previously suggested.
Is that query working for you? If not, you'll need to give more details about what is wrong with it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 4, 2012 at 10:07 pm
Yes i the query is working fine. Only issue i am facing is months are not arranged in sequential order
2001Q3August2605514.9809
2001Q3July1172359.4289
2001Q3September2073058.5385
2001Q4December3097637.3384
2001Q4November3690018.6652
2001Q4October1688963.2744
2002Q1Feburary3130823.0378
2002Q1January1605782.1915
2002Q1March2643081.0798
2002Q2April1905833.9088
2002Q2June2546121.9618
2002Q2May3758329.2949
For Quarter3 we can observe August comes first,then July and Setpember
June 5, 2012 at 8:46 am
Smash125 (6/4/2012)
Yes i the query is working fine. Only issue i am facing is months are not arranged in sequential order...
For Quarter3 we can observe August comes first,then July and Setpember
That's because, by converting it to a string, you have removed the information that it is part of a date. It's sorting them in alphabetical order. So, for example, you wouldn't be able to tell me the correct order for the following Polish month names without more information.
gruzdien
listopad
pazdziernik
To you, they are just meaningless strings. The same is true for the computer with English month names.
You could get around this by grouping and sorting on the beginning of the month and then using Max(Year(yourdate)), etc., for the labels.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 5, 2012 at 9:07 am
Try this:
select
year(OrderDate) AS OrderYear,
MonthForTheYear = CASE WHEN MONTH(OrderDate)=1 THEN 'January'
WHEN MONTH(OrderDate)=2 THEN 'Feburary'
WHEN MONTH(OrderDate)=3 THEN 'March'
WHEN MONTH(OrderDate)=4 THEN 'April'
WHEN MONTH(OrderDate)=5 THEN 'May'
WHEN MONTH(OrderDate)=6 THEN 'June'
WHEN MONTH(OrderDate)=7 THEN 'July'
WHEN MONTH(OrderDate)=8 THEN 'August'
WHEN MONTH(OrderDate)=9 THEN 'September'
WHEN MONTH(OrderDate)=10 THEN 'October'
WHEN MONTH(OrderDate)=11 THEN 'November'
WHEN MONTH(OrderDate)=12 THEN 'December'
End,
Quarter = CASE WHEN MONTH(OrderDate) BETWEEN 1 and 3 THEN 'Q1'
WHEN MONTH(OrderDate) BETWEEN 4 and 6 THEN 'Q2'
WHEN MONTH(OrderDate) BETWEEN 7 and 9 THEN 'Q3'
WHEN MONTH(OrderDate) BETWEEN 9 and 12 THEN 'Q4'
End,
SUM(TotalDue) AS TotalDue
from
Sales.SalesOrderHeader
group by
year(OrderDate),
CASE WHEN MONTH(OrderDate)=1 THEN 'January'
WHEN MONTH(OrderDate)=2 THEN 'Feburary'
WHEN MONTH(OrderDate)=3 THEN 'March'
WHEN MONTH(OrderDate)=4 THEN 'April'
WHEN MONTH(OrderDate)=5 THEN 'May'
WHEN MONTH(OrderDate)=6 THEN 'June'
WHEN MONTH(OrderDate)=7 THEN 'July'
WHEN MONTH(OrderDate)=8 THEN 'August'
WHEN MONTH(OrderDate)=9 THEN 'September'
WHEN MONTH(OrderDate)=10 THEN 'October'
WHEN MONTH(OrderDate)=11 THEN 'November'
WHEN MONTH(OrderDate)=12 THEN 'December'
End,
CASE WHEN MONTH(OrderDate) BETWEEN 1 and 3 THEN 'Q1'
WHEN MONTH(OrderDate) BETWEEN 4 and 6 THEN 'Q2'
WHEN MONTH(OrderDate) BETWEEN 7 and 9 THEN 'Q3'
WHEN MONTH(OrderDate) BETWEEN 9 and 12 THEN 'Q4'
END
ORDER BY
DATEPART(yy, OrderDate),
DATEPART(qq, OrderDate),
DATEPART(mm, OrderDate)
I don't have the AdventureWorks database available here so I could not test it.
June 5, 2012 at 11:31 am
That doesn't work Lynn, because the OrderDate is not in the GROUP BY, so it cannot be used in the ORDER BY clause unless it's in an aggregate function (which you don't use).
Here is how I would approach this.
SELECT Year(OrderDateKey) AS OrderYear
,DATENAME(MONTH, OrderDateKey) AS MonthForTheYear
,'Q' + CAST(DATEPART(QQ, OrderDateKey) AS VARCHAR(1)) AS Quarter
,SUM(TotalDue) AS TotalDue
FROM Sales.SalesOrderHeader
CROSS APPLY ( SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, OrderDate), 0) AS OrderDateKey) AS OrderDate
GROUP BY OrderDateKey
ORDER BY OrderDateKey
Notice that I was able to use the DATENAME() function to replace the first CASE statement--and also make it language independent at the same time; I used the DATEPART() function to replace the second CASE statement; I used the CROSS APPLY to calculate a consistent reference field for each day in the month for grouping/ordering purposes; and I only use the year/quarter/month in the SELECT.
Drew
EDIT: Corrected the keyword to ORDER BY.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 5, 2012 at 12:36 pm
drew.allen (6/5/2012)
That doesn't work Lynn, because the OrderDate is not in the GROUP BY, so it cannot be used in the ORDER BY clause unless it's in an aggregate function (which you don't use).Here is how I would approach this.
SELECT Year(OrderDateKey) AS OrderYear
,DATENAME(MONTH, OrderDateKey) AS MonthForTheYear
,'Q' + CAST(DATEPART(QQ, OrderDateKey) AS VARCHAR(1)) AS Quarter
,SUM(TotalDue) AS TotalDue
FROM Sales.SalesOrderHeader
CROSS APPLY ( SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, OrderDate), 0) AS OrderDateKey) AS OrderDate
GROUP BY OrderDateKey
ORDER BY OrderDateKey
Notice that I was able to use the DATENAME() function to replace the first CASE statement--and also make it language independent at the same time; I used the DATEPART() function to replace the second CASE statement; I used the CROSS APPLY to calculate a consistent reference field for each day in the month for grouping/ordering purposes; and I only use the year/quarter/month in the SELECT.
Drew
EDIT: Corrected the keyword to ORDER BY.
Well, I did say I wasn't in a place I could run a test. Thanks.
June 5, 2012 at 1:25 pm
Thanks gUYS
June 6, 2012 at 7:14 am
Modified Smash125's code a bit so that the order is sequential and eliminated the use of case in CTE.
WITH C AS(
select
year(OrderDate) AS OrderYear,
MonthForTheYear= DateName(month,DateAdd(month,Month(OrderDate),0)-1),
Quarter = CASE WHEN MONTH(OrderDate) BETWEEN 1 and 3 THEN 'Q1'
WHEN MONTH(OrderDate) BETWEEN 4 and 6 THEN 'Q2'
WHEN MONTH(OrderDate) BETWEEN 7 and 9 THEN 'Q3'
WHEN MONTH(OrderDate) BETWEEN 9 and 12 THEN 'Q4'
End,
Month(OrderDate) MonthNumber,
TotalDue
from Sales.SalesOrderHeader
)
SELECT OrderYear,Quarter,MonthForTheYear,SUM(TotalDue) FROM C
GROUP BY OrderYear,Quarter,MonthNumber,MonthForTheYear
ORDER BY OrderYear,Quarter,MonthNumber,MonthForTheYear
- Nandu
June 6, 2012 at 7:32 am
You can try this:
WITH C
AS
(
SELECT YEAR(OrderDate) AS OrderYear
,DATENAME(MONTH,OrderDate) AS MonthForTheYear
,'Q' + DATENAME(QUARTER,OrderDate) AS [Quarter]
,TotalDue AS TotalDue
FROM Sales.SalesOrderHeader
)
SELECT OrderYear,Quarter,MonthForTheYear,SUM(TotalDue) AS TotalDue
FROM C
GROUP BY OrderYear,Quarter,MonthForTheYear
ORDER BY CAST('1 ' + MonthForTheYear + ' ' + CAST(OrderYear AS VARCHAR) AS DATE)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply