使用带有多个where子句的Sum（）

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

我有2个表,帐户和用法

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.

我假设我需要总结,然后做一个Group By ...但不能真正确定我是如何解决这个问题的。

1 个解决方案

#1

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.

我创建了一个Months表,其中包含MonthID,Month的值,其值为(201212,12),(201301,1),...我还重新组织了使用表,使其具有月份列而不是开始日期和结束约会,因为它使想法更清晰。

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

有关详细信息,请参见http://sqlfiddle.com/#!3/f57d84/6

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

#1