Noob SQL Question - I think.
-
I have a table that contains daily sales information one row per day. The columns to be used are Location_Code, System_Date and Royalty_Sales. I want a query that returns the highest sales level for each day of the week AND the date that occurred on. I have:
SELECT Location_Code,
MAX(CASE WHEN DATEPART(weekday, System_Date) = 1 THEN Royalty_Sales ELSE 0 END) AS
,MAX(CASE WHEN DATEPART(weekday, System_Date) = 2 THEN Royalty_Sales ELSE 0 END) AS [M],
MAX(CASE WHEN DATEPART(weekday, System_Date) = 3 THEN Royalty_Sales ELSE 0 END) AS [Tu],
MAX(CASE WHEN DATEPART(weekday, System_Date) = 4 THEN Royalty_Sales ELSE 0 END) AS [W],
MAX(CASE WHEN DATEPART(weekday, System_Date) = 5 THEN Royalty_Sales ELSE 0 END) AS [Th],
MAX(CASE WHEN DATEPART(weekday, System_Date) = 6 THEN Royalty_Sales ELSE 0 END) AS [F],
MAX(CASE WHEN DATEPART(weekday, System_Date) = 7 THEN Royalty_Sales ELSE 0 END) AS [Sa]
FROM Daily_Summary
WHERE System_Date >= @dateLow AND System_Date <= @dateHigh
GROUP BY Location_Code
ORDER BY Location_Code
This works terrific, but for the life of me I can't figure out how to also get the DATE that the MAX for each day occurs on.
Please educate.
Shawn
-
Hi Shawn,
I'm not sure how your data is actually stored (i.e. table structures), but you can handle this a couple of ways. One is just to add another CASE statement to your existing query.
Create Table #myTable
(
Location_Code int
, System_Date datetime
, Royalty_Sales int
);
Insert Into #myTable
Select 1, '2009-01-01', 100 Union All
Select 1, '2009-01-02', 100 Union All
Select 1, '2009-01-03', 100 Union All
Select 1, '2009-01-04', 100 Union All
Select 1, '2009-01-05', 100 Union All
Select 1, '2009-01-06', 100 Union All
Select 1, '2009-01-07', 100 Union All
Select 2, '2009-01-01', 100 Union All
Select 2, '2009-01-02', 100 Union All
Select 2, '2009-01-03', 100 Union All
Select 1, '2009-01-08', 200 Union All
Select 1, '2009-01-09', 200;
SELECT Location_Code,
MAX(CASE WHEN DATEPART(weekday, System_Date) = 1 THEN Royalty_Sales ELSE 0 END) AS
,MAX(CASE WHEN DATEPART(weekday, System_Date) = 1 THEN System_Date ELSE Null END) AS
,MAX(CASE WHEN DATEPART(weekday, System_Date) = 2 THEN Royalty_Sales ELSE 0 END) AS [M],
MAX(CASE WHEN DATEPART(weekday, System_Date) = 2 THEN System_Date ELSE Null END) AS [M],
MAX(CASE WHEN DATEPART(weekday, System_Date) = 3 THEN Royalty_Sales ELSE 0 END) AS [Tu],
MAX(CASE WHEN DATEPART(weekday, System_Date) = 3 THEN System_Date ELSE Null END) AS [Tu],
MAX(CASE WHEN DATEPART(weekday, System_Date) = 4 THEN Royalty_Sales ELSE 0 END) AS [W],
MAX(CASE WHEN DATEPART(weekday, System_Date) = 4 THEN System_Date ELSE Null END) AS [W],
MAX(CASE WHEN DATEPART(weekday, System_Date) = 5 THEN Royalty_Sales ELSE 0 END) AS [Th],
MAX(CASE WHEN DATEPART(weekday, System_Date) = 5 THEN System_Date ELSE Null END) AS [Th],
MAX(CASE WHEN DATEPART(weekday, System_Date) = 6 THEN Royalty_Sales ELSE 0 END) AS [F],
MAX(CASE WHEN DATEPART(weekday, System_Date) = 6 THEN System_Date ELSE Null END) AS [F],
MAX(CASE WHEN DATEPART(weekday, System_Date) = 7 THEN Royalty_Sales ELSE 0 END) AS [Sa],
MAX(CASE WHEN DATEPART(weekday, System_Date) = 7 THEN System_Date ELSE Null END) AS [Sa]
FROM #myTable
GROUP BY Location_Code
ORDER BY Location_Code
I'd probably handle it with something like this...
;With myCTE
As
(
Select Location_Code
, System_Date
, Royalty_Sales
, DatePart(weekday, System_Date) As 'week_day'
From #myTable
)
Select Location_Code
, week_day
, Max(System_Date) As 'maxDay'
, Max(Royalty_Sales) As 'maxSales'
From myCTE
Group By Location_Code
, week_day;
Drop Table #myTable;
Hope that helps, let me know if you have any more questions.
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning -
Michell, thanks for your post.
However unless I did something wrong, max(System_Date) is simply throwing back whatever the most recent date is for each day of the week. For example, 2/4/2009 for a Wednesday.
Shawn
-
Ah, I misread your initial question, sorry about that!
Try this:
Create Table #myTable
(
Location_Code int
, System_Date datetime
, Royalty_Sales int
);
Insert Into #myTable
Select 1, '2009-01-01', 100 Union All
Select 1, '2009-01-02', 300 Union All
Select 1, '2009-01-03', 250 Union All
Select 1, '2009-01-04', 100 Union All
Select 1, '2009-01-05', 100 Union All
Select 1, '2009-01-06', 100 Union All
Select 1, '2009-01-07', 100 Union All
Select 1, '2009-01-08', 200 Union All
Select 1, '2009-01-09', 100 Union All
Select 1, '2009-01-10', 150;
Select Location_Code
, DatePart(weekday, System_Date) As 'week_day'
, Max(Royalty_Sales) As 'maxSales'
, (Select Max(System_Date)
From #myTable As a
Where DatePart(weekday, a.System_Date) = DatePart(weekday, b.System_Date)
And a.Royalty_Sales = Max(b.Royalty_Sales)
) As 'maxDay'
From #myTable As b
Group By Location_Code
, DatePart(weekday, System_Date)
Drop Table #myTable;
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning -
Thanks Michelle. I actually learned a lot about nested queries in the process. I knew you could nest but comparing "a" to "b" in the manner you did was completely new. Thanks again.
Shawn
-
That's great, I'm glad I could help! Just be careful when using correlated subqueries on large data sets, it can sometimes have a pretty big (negative) performance impact.
Good luck, and happy coding!
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply