• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

Show missing days for aggregated data across a date range

I have a table called "orders" that contains a unique ID "orderid", a datestamp field "ctime" and a money field "profit"

I want to pull out all the data for february 2009 with the profit aggregated using SUM, but I want to show days with no data even when there are no orders on those dates.

With the following data:

ORDERID  CTIME                PROFIT
122          31 jan 2009       $750
123          01 feb 2009       $1000
124          01 feb 2009       $1500
125          03 feb 2009       $1600
126          05 feb 2009       $500
127          06 feb 2009       $1000
etc....

if I use the following code:

select datepart(day,ctime) as 'day', sum(profit) as profit from orders where datepart(month,ctime) = 2 and datepart(year,ctime) = 2009 group by datepart(day,ctime) order by datepart(day,ctime)

I get :

DAY  PROFIT
1      $2500
3      $1600
5      $500
6      $1000
etc...

What I want is:

DAY  PROFIT
1      $2500
2      $0
3      $1600
4      $0
5      $500
6      $1000
etc..

I tried creating a table with numbers 1-28 in it and joining to this, but this didn't produce the desired effect.

Any assistance would be appreciated.
0
neburton
Asked:
neburton
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you need to left/right join with that table:
select t.day, s.profit
 from your_day_listing t
 left join (select datepart(day,ctime) as [day], sum(profit) as profit from orders where datepart(month,ctime) = 2 and datepart(year,ctime) = 2009 group by datepart(day,ctime)) s
   on t.[day] = s.[day] 
 order by t.[day]

Open in new window

0
 
neburtonAuthor Commented:
That solved it.  Many thanks.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now