Include missing dates in sequence

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Include missing dates in sequence

Author

Topic

lightscribe
Starting Member

3 Posts

Posted - 2008-01-12 : 16:35:14

I have a query which gets the order total for the day but if theres no orders for the day I cant get it to display 0 for that date there were no orders - the date dosent exist in the table.

SELECT SUM(OrderSubTotal), DATEADD(d, 0, DATEDIFF(d, 0, OrderTime)) FROM dbo.tblStoreOrders WHERE OrderTime > (getdate() - 30) Group By DATEADD(d, 0, DATEDIFF(d, 0, OrderTime)) Order By DATEADD(d, 0, DATEDIFF(d, 0, OrderTime))

Any ideas how I can get it to display missing dates?

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-01-12 : 19:13:40

Yes... the spt_Values table is being used like a small Tally table do create the missing dates with, in this case...


 SELECT dr.OrderDay,
        SUM(ISNULL(so.OrderSubTotal,0)) AS TotalDay
   FROM dbo.tblStoreOrders so
  RIGHT OUTER JOIN
        (--==== Find all dates to report for last 30 days (not including today)
         SELECT Number + DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) - 30 AS OrderDay
           FROM Master.dbo.spt_Values
          WHERE Type   = 'P'
            AND Number < 30
        ) dr
     ON DATEADD(d,DATEDIFF(d,0,so.OrderTime),0) = dr.OrderDay
  GROUP BY dr.OrderDay
  ORDER BY dr.OrderDay

--Jeff Moden

lightscribe
Starting Member

3 Posts

Posted - 2008-01-13 : 05:33:10

Thanks it works great, I would have never come up with something like that.

How do you add a criteria? So I could filter out orders which were cancelled (OrderStatusId=1)?

Tom

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-01-13 : 09:48:38

quote:
How do you add a criteria? So I could filter out orders which were cancelled (OrderStatusId=1)?

Sure, no problem... it's not really any different than any other query... Assuming that an OrderStatusID=1 means the order was cancelled, the following should do it... I've highlighted the change...

SELECT dr.OrderDay,
        SUM(ISNULL(so.OrderSubTotal,0)) AS TotalDay
   FROM dbo.tblStoreOrders so
  RIGHT OUTER JOIN
        (--==== Find all dates to report for last 30 days (not including today)
         SELECT Number + DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) - 30 AS OrderDay
           FROM Master.dbo.spt_Values
          WHERE Type   = 'P'
            AND Number < 30
        ) dr
     ON DATEADD(d,DATEDIFF(d,0,so.OrderTime),0) = dr.OrderDay
 WHERE so.OrderStatusId <> 1 --Order has NOT been cancelled
  GROUP BY dr.OrderDay
  ORDER BY dr.OrderDay

quote:
Thanks it works great, I would have never come up with something like that.

Thanks for the feedback, Tom. Makes it all worth while

--Jeff Moden

lightscribe
Starting Member

3 Posts

Posted - 2008-01-13 : 13:50:01

If I add the criteria it misses out the days with zero orders for some reason.

Any ideas?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-13 : 16:47:02

I think you need to place the "so.OrderStatusID <> 1" criteria in the JOIN criteria rather than the WHERE clause since the [so] table is OUTER JOINed.

Another solution is to change the criteria to "isNull(so.OrderStatusID,0) <> 1"

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources