SQL group by day, show orders for each day

Question

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)?

Answer 1

SQL isn't "skipping" dates... because queries run against data that is actually in the table. So, if you don't have a DATE in the table for January 14th, then why would SQL show you a result :)

What you need to do is make a temp table, and JOIN to it.

CREATE TABLE #MyDates ( TargetDate DATETIME )
INSERT INTO #MyDates VALUES CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 0, 101))
INSERT INTO #MyDates VALUES CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 1, 101))
INSERT INTO #MyDates VALUES CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 2, 101))
INSERT INTO #MyDates VALUES CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 3, 101))
INSERT INTO #MyDates VALUES CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 4, 101))
INSERT INTO #MyDates VALUES CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 5, 101))
INSERT INTO #MyDates VALUES CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 6, 101))
INSERT INTO #MyDates VALUES CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 7, 101))

SELECT CONVERT(VARCHAR, TargetDate, 101) AS Date, COUNT(*) AS OrderCount
FROM dbo.Orders INNER JOIN #MyDates ON Orders.Date = #MyDates.TargetDate
GROUP BY blah blah blah (you know the rest)

There you go!

Answer 2

I had the same problem and this is how I solved it:

SELECT datename(DW,nDays) TimelineDays, 
    Convert(varchar(10), nDays, 101) TimelineDate,
    ISNULL(SUM(Counter),0) Totals 
FROM (Select GETDATE() AS nDays
    union Select GETDATE()-1
    union Select GETDATE()-2
    union Select GETDATE()-3
    union Select GETDATE()-4
    union Select GETDATE()-5
    union Select GETDATE()-6) AS tDays

Left Join (Select * From tHistory Where Account = 1000) AS History
            on (DATEPART(year,nDays) + DATEPART(MONTH,nDays) + DATEPART(day,nDays)) = 
            (DATEPART(year,RecordDate) + DATEPART(MONTH,RecordDate) + DATEPART(day,RecordDate)) 
GROUP BY nDays
ORDER BY nDays DESC

The ouput is:

TimelineDays,   TimelineDate,     Totals

Tuesday         10/26/2010        0
Monday          10/25/2010        6
Sunday          10/24/2010        3
Saturday        10/23/2010        2
Friday          10/22/2010        0
Thursday        10/21/2010        0
Wednesday       10/20/2010        0