Select data from SQL DB per day

Question

I have a table with order information in an E-commerce store. Schema looks like this:

[Orders]
Id|SubTotal|TaxAmount|ShippingAmount|DateCreated

This table does only contain data for every Order. So if a day goes by without any orders, no sales data is there for that day.

I would like to select subtotal-per-day for the last 30 days, including those days with no sales.

The resultset would look like this:

Date | SalesSum
2009-08-01 | 15235
2009-08-02 | 0
2009-08-03 | 340
2009-08-04 | 0
...

Doing this, only gives me data for those days with orders:

select DateCreated as Date, sum(ordersubtotal) as SalesSum
from Orders
group by DateCreated

You could create a table called Dates, and select from that table and join the Orders table. But I really want to avoid that, because it doesn't work good enough when dealing with different time zones and things...

Please don't laugh. SQL is not my kind of thing... :)

Answer 1

Create a function that can generate a date table as follows:
(stolen from http://www.codeproject.com/KB/database/GenerateDateTable.aspx)

Create Function dbo.fnDateTable
(
  @StartDate datetime,
  @EndDate datetime,
  @DayPart char(5) -- support 'day','month','year','hour', default 'day'
)
Returns @Result Table
(
  [Date] datetime
)
As
Begin
  Declare @CurrentDate datetime
  Set @CurrentDate=@StartDate
  While @CurrentDate<=@EndDate
  Begin
    Insert Into @Result Values (@CurrentDate)
    Select @CurrentDate=
    Case
    When @DayPart='year' Then DateAdd(yy,1,@CurrentDate)
    When @DayPart='month' Then DateAdd(mm,1,@CurrentDate)
    When @DayPart='hour' Then DateAdd(hh,1,@CurrentDate)
    Else
      DateAdd(dd,1,@CurrentDate)
    End
  End
  Return
End

Then, join against that table

SELECT dates.Date as Date, sum(SubTotal+TaxAmount+ShippingAmount)
FROM [fnDateTable] (dateadd("m",-1,CONVERT(VARCHAR(10),GETDATE(),111)),CONVERT(VARCHAR(10),GETDATE(),111),'day') dates 
LEFT JOIN Orders
ON dates.Date = DateCreated
GROUP BY dates.Date

Answer 2

declare @oldest_date datetime
declare @daily_sum numeric(18,2)
declare @temp table(
    sales_date datetime,
    sales_sum numeric(18,2)
)
select @oldest_date = dateadd(day,-30,getdate())

while @oldest_date <= getdate()
begin
    set @daily_sum = (select sum(SubTotal) from SalesTable  where DateCreated = @oldest_date)
    insert into @temp(sales_date, sales_sum) values(@oldest_date, @daily_sum)
    set @oldest_date = dateadd(day,1,@oldest_date)
end

select * from @temp

OK - I missed that 'last 30 days' part. The bit above, while not as clean, IMHO, as the date table, should work. Another variant would be to use the while loop to fill a temp table just with the last 30 days and do a left outer join with the result of my original query.

Answer 3

including those days with no sales.

That's the difficult part. I don't think the first answer will help you with that. I did something similar to this with a separate date table.

You can find the directions on how to do so here:

Date Table