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

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 2

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 3

I created the Function DateTable as JamesMLV pointed out to me.

And then the SQL looks like this:

SELECT dates.date, ISNULL(SUM(ordersubtotal), 0) as Sales FROM [dbo].[DateTable] ('2009-08-01','2009-08-31','day') dates
LEFT JOIN Orders ON CONVERT(VARCHAR(10),Orders.datecreated, 111) = dates.date
group by dates.date

Answer 4

(Revised a bit--I hit enter too soon)

I started poking at this, and as it hits some pretty tricky SQL concepts it quickly grew into the following monster. If feasible, you might be better off adapting THEn's solution; or, like many others advise, using application code to fill in the gaps could be preferrable.

--  A temp table holding the 30 dates that you want to check
DECLARE @Foo Table (Date  smalldatetime  not null)

--  Populate the table using a common "tally table" methodology (I got this from SQL Server magazine long ago)
;WITH
  L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
  L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows
  L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
  L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
  Tally AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS Number FROM L3)
INSERT @Foo (Date)
 select dateadd(dd, datediff(dd, 0, dateadd(dd, -number + 1, getdate())), 0)
 from Tally
 where Number < 31

Step 1 is to build a temp table containint the 30 dates that you are concerned with. That abstract wierdness is about the fastest way known to build a table of consecutive integers; add a few more subqueries, and you can populate millions or more in mere seconds. I take the first 30, and use dateadd and the current date/time to convert them into dates. If you already have a "fixed" table that has 1-30, you can use that and skip the CTE entirely (by replacing table "Tally" with your table).

The outer two date function calls remove the time portion of the generated string.

(Note that I assume that your order date also has no time portion -- otherwise you've got another common problem to resolve.)

For testing purposes I built table #Orders, and this gets you the rest:

SELECT f.Date, sum(ordersubtotal) as SalesSum
 from @Foo f
  left outer join #Orders o
   on o.DateCreated = f.Date
 group by f.Date

Answer 5

I have a Log table table with LogID an index which i never delete any records. it has index from 1 to ~10000000. Using this table I can write

select 
     s.ddate, SUM(isnull(o.SubTotal,0))
from 
    (
        select 
            cast(datediff(d,LogID,getdate()) as datetime) AS ddate 
        from
            Log 
        where 
            LogID <31
    ) s right join orders o on o.orderdate = s.ddate
group by s.ddate

Answer 6

Where is this ultimately going to end up? I ask only because it may be easier to fill in the empty days with whatever program is going to deal with the data instead of trying to get it done in SQL.

SQL is a wonderful language, and it is capable of a great many things, but sometimes you're just better off working the finer points of the data in the program instead.

Answer 7

I actually did this today. We also got a e-commerce application. I don't want to fill our database with "useless" dates. I just do the group by and create all the days for the last N days in Java, and peer them with the date/sales results from the database.

Answer 8

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

Answer 9

SELECT DateCreated,
SUM(SubTotal) AS SalesSum
FROM Orders
GROUP BY DateCreated