Using Sum() with multiple where clauses

Question

I'm pretty new to this, so forgive if this has been posted (I had no idea what to even search on).

I have 2 tables, Accounts and Usage

AccountID  AccountStartDate  AccountEndDate
-------------------------------------------
1          12/1/2012         12/1/2013
2          1/1/2013          1/1/2014


UsageId   AccountID EstimatedUsage  StartDate  EndDate
------------------------------------------------------
1         1         10              1/1        1/31
2         1         11              2/1        2/29
3         1         23              3/1        3/31
4         1         23              4/1        4/30
5         1         15              5/1        5/31
6         1         20              6/1        6/30
7         1         15              7/1        7/31
8         1         12              8/1        8/31
9         1         14              9/1        9/30
10        1         21             10/1        10/31
11        1         27             11/1        11/30
12        1         34             12/1        12/31
13        2         13              1/1        1/31
14        2         13              2/1        2/29
15        2         28              3/1        3/31
16        2         29              4/1        4/30
17        2         31              5/1        5/31
18        2         26              6/1        6/30
19        2         43              7/1        7/31
20        2         32              8/1        8/31
21        2         18              9/1        9/30
22        2         20             10/1        10/31
23        2         47             11/1        11/30
24        2         33             12/1        12/31

I'd like to write one query that gives me estimated usage for each month (starting now until the last month that we serve an account) for all accounts being served during that month.

The results would be as follows:

Month-Year     Total Est Usage
------------------------------
Oct-12            0  (none being served)
Nov-12            0  (none being served)
Dec-12           34  (only accountid 1 being served)
Jan-13           23  (accountid 1 & 2 being served)
Feb-13           24  (accountid 1 & 2 being served)
Mar-13           51  (accountid 1 & 2 being served)
...
Dec-13           33  (only accountid 2 being served)
Jan-14           0   (none being served)
Feb-14           0   (none being served)

I'm assuming I need to sum and then do a Group By...but not really sure logically how I'd lay this out.

Should the usage dates have years on?

Laurence
– Laurence

2012-11-06 22:08:44 +00:00
Commented Nov 6, 2012 at 22:08 — Laurence
– Laurence, Commented Nov 6, 2012 at 22:08

Laurence · Accepted Answer · 2012-11-09 20:42:50Z

3

Revised Answer:

I've created a Months table with columns MonthID, Month with values like (201212, 12), (201301, 1), ... I've also reorganised the usage table to have a month column rather than the start date and end date, as it makes the idea clearer.

See http://sqlfiddle.com/#!3/f57d84/6 for details

The query is now:

Select
  m.MonthID,
  Sum(u.EstimatedUsage) TotalEstimatedUsage
From
  Accounts a
    Inner Join
  Usage u
    On a.AccountID = u.AccountID
    Inner Join
  Months m
    On m.MonthID Between 
      Year(a.AccountStartDate) * 100 + Month(a.AccountStartDate) And
      Year(a.AccountEndDate) * 100 + Month(a.AccountEndDate) And
      m.Month = u.Month
Group By
  m.MonthID
Order By
  1

Previous answer, for reference which assumed usages ranges were full dates rather than just months.

Select
  Year(u.StartDate),
  Month(u.StartDate),
  Sum(Case When a.AccountStartDate <= u.StartDate And a.AccountEndDate >= u.EndDate Then u.EstimatedUsage Else 0 End) TotalEstimatedUsage
From
  Accounts a
    Inner Join
  Usage u
    On a.AccountID = u.AccountID
Group By
  Year(u.StartDate),
  Month(u.StartDate)
Order By
  1, 2

edited Nov 9, 2012 at 20:42

answered Nov 6, 2012 at 21:58

Laurence

11k1 gold badge28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JeffO Over a year ago

I'm a little confused about the start/end dates. the Usage table estimates account usage by month (regardless of year). so january 2013 usage is the same estimate as january 2014 usage. So I need to return all months from current until the end of the last contract, which will be more than the number of months in the usage table.

Laurence Over a year ago

@JeffO I've updated to show the way you'd approach this for your requirement. You'd normally generate the month table programmatically. You could use a CTE to generate it on the fly if needed. If you wanted to support any start date/end date rather than whole months you'd need a similar day table. You'll also need to do a bit of mucking about to ensure a continuous range of months in the answer, if your accounts ranges didn't overlap.

Collectives™ on Stack Overflow

Using Sum() with multiple where clauses

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related