SQL group by day, show orders for each day
|November 30, 2008||Posted by forumadmin under TechQns||
I have an SQL 2005 table, let’s call it Orders, in the format:
OrderID, OrderDate, OrderAmount 1, 25/11/2008, 10 2, 25/11/2008, 2 3, 30/1002008, 5
Then I need to produce a report table showing the ordered amount on each day in the last 7 days:
Day, OrderCount, OrderAmount 25/11/2008, 2, 12 26/11/2008, 0, 0 27/11/2008, 0, 0 28/11/2008, 0, 0 29/11/2008, 0, 0 30/11/2008, 1, 5
The SQL query that would normally produce this:
select count(*), sum(OrderAmount) from Orders where OrderDate>getdate()-7 group by datepart(day,OrderDate)
Has a problem in that it will skip the days where there are no orders:
Day, OrderCount, OrderAmount 25/11/2008, 2, 12 30/11/2008, 1, 5
Normally I would fix this using a tally table and outer join against rows there, but I’m really looking for a simpler or more efficient solution for this. It seems like such a common requirement for a report query that some elegant solution should be available for this already.
So: 1. Can this result be obtain from a simple query without using tally tables?
and 2. If no, can we create this tally table (reliably) on the fly (I can create a tally table using CTE but recursion stack limits me to 100 rows)?
|Asked By – Radu094||Read Answers|