October 27, 2003 at 8:32 am
I'm pretty new with SQL Server and stored procedures, but I need to come up with a way to show weekly sales figures from a table, broken down by day of the week, IE totals for Sunday, then Monday, Tuesday and so on.
That's fine, but there might be no rows for a certain day of the week, and I just can't skip over it, I need to show it as Wednesday - $0 or something. I'm not really sure how to do this, and haven't been unable to unearth any helpful examples.
I'm assuming this would be a stored provedure, which I could access via Crystal Reports.
Can anyone point me to an example?
Thanks,
Brian
October 27, 2003 at 8:58 am
SELECT date=
CASE
WHEN datepart(weekday,<date_column>)='1' THEN 'SUNDAY'
WHEN datepart(weekday,<date_column>)='2' THEN 'MONDAY'
WHEN datepart(weekday,<date_column>)='3' THEN 'TUESDAY'
WHEN datepart(weekday,<date_column>)='4' THEN 'WEDNESDAY'
WHEN datepart(weekday,<date_column>)='5' THEN 'THURSDAY'
WHEN datepart(weekday,<date_column>)='6' THEN 'FRIDAY'
WHEN datepart(weekday,<date_column>)='7' THEN 'SATURDAY'
END
,ISNULL(COUNT(*),0) as count FROM <table name>
GROUP BY datepart(weekday,<date_column>)
ORDER BY datepart(weekday,<date_column>)
October 27, 2003 at 8:58 am
This doesn't need to be a stored procedure, as it's just one query:
USE Northwind
go
SELECT n.dow, ISNULL(SUM(o.Freight),0)
FROM Orders o RIGHT JOIN
(SELECT 1 dow
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7) n
ON DATEPART(w,Orderdate) = n.dow
GROUP BY n.dow
--Jonathan
--Jonathan
October 27, 2003 at 11:03 am
First of all, thanks to both of you. Jonathans code gave me the right total for the correct day, but didn't give me other days, and Bruce's code gave me all days, but with a count of 1 for days without sales.
However, combining them resulted in:
SELECT COUNT(vw_rdbitem.TransactionDate) as 'Count',
case
WHEN n.dow ='1' THEN 'SUNDAY'
WHEN n.dow ='2' THEN 'MONDAY'
WHEN n.dow ='3' THEN 'TUESDAY'
WHEN n.dow ='4' THEN 'WEDNESDAY'
WHEN n.dow ='5' THEN 'THURSDAY'
WHEN n.dow ='6' THEN 'FRIDAY'
WHEN n.dow ='7' THEN 'SATURDAY'
END as 'Weekday'
FROM vw_rdbitem RIGHT JOIN
(SELECT 1 dow
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7) n
ON DATEPART(weekday,transactiondate) = n.dow
GROUP BY n.dow
order by n.dow
Which gave me:
Count Weekday
----------- ---------
0 SUNDAY
32081 MONDAY
0 TUESDAY
0 WEDNESDAY
0 THURSDAY
0 FRIDAY
0 SATURDAY
Which is exactly what I wanted.
Only, I got a warning "Null value is eliminated by an aggregate or other SET operation." which I suppose must be from the Count(vw_rdbitem.TransactionDate). I'm not sure what to do about that...
October 27, 2003 at 11:18 am
quote:
First of all, thanks to both of you. Jonathans code gave me the right total for the correct day, but didn't give me other days, and Bruce's code gave me all days, but with a count of 1 for days without sales.However, combining them resulted in:
Your analysis is backwards...
You wrote you wanted "weekly sales figures" with "totals" where no sales would be "$0", so I didn't imagine you needed a count vs. sum...
SELECT n.dtnm, COUNT(TransactionDate)
FROM vw_rdbitem r RIGHT JOIN
(SELECT 1 dow, 'Sunday' dtnm
UNION ALL SELECT 2, 'Monday'
UNION ALL SELECT 3, 'Tuesday'
UNION ALL SELECT 4, 'Wednesday'
UNION ALL SELECT 5, 'Thursday'
UNION ALL SELECT 6, 'Friday'
UNION ALL SELECT 7, 'Saturday') n
ON DATEPART(w,r.TransactionDate) = n.dow
GROUP BY n.dtnm, n.dow
ORDER BY n.dow
Be aware that this will be sensitive to the DATEFIRST setting.
--Jonathan
--Jonathan
October 28, 2003 at 8:22 am
As an aside, I just discovered a cool function yesterday that could be useful in queries that look at day of week: the DateName function. Rather than use a case statement to assign day of week text names, you can use DateName(dw, n.Dow). This is available in SQL Server 2K, I don't know about prior versions.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply