sql-server - 使用 2 个日期字段按月汇总数据的 T-SQL 查询

Question

我有一个包含 Volume、OpenDate 和 CloseDate 字段的表。我需要按月汇总成交量数据，在打开的月份计算一次，在关闭的月份再次计算。我无法找出将两个日期合并到分组中以及在 SUM() 函数中使用的条件日期检查的正确方法。

OpenDate        Volume  CloseDate
1/1/2016 00:00  8,000   1/1/2016 00:00
1/3/2016 00:00  10,000  2/3/2016 00:00
1/4/2016 00:00  20,000  Null
2/1/2016 00:00  8,000   2/5/2016 00:00
2/3/2016 00:00  10,000  3/1/2016 00:00
2/5/2016 00:00  20,000  Null

Expected Results    
Month       Volume
1/1/2016    46,000
2/1/2016    56,000
3/1/2016    10,000


    SELECT CAST(
           CAST(YEAR(OpenDate) AS VARCHAR(4) ) +
           '-' +
           CAST(MONTH(OpenDate)AS VARCHAR(2))  + 
           '-01'
           AS DATETIME) MonthYear,
           SUM (Volume) + 
           SUM( CASE WHEN CloseDate IS NOT NULL AND CloseDate = ???
                     THEN Volume
                     ELSE 0 
                 END
               )  Volume
      FROM ORDERS 
  GROUP BY CAST(
           CAST(YEAR(OpenDate) AS VARCHAR(4) ) +
           '-' +
           CAST(MONTH(OpenDate)AS VARCHAR(2))  + 
           '-01'
           AS DATETIME)

Answer 1

您可以使用union all并将OpenDate与CloseDate交换，如下所示:

select 
    OpenDate = convert(varchar(10),dateadd(month, datediff(month, 0, OpenDate )  , 0),120)
  , Volumne = sum(Volume)
from (
  select OpenDate, Volume 
  from t
  union all 
  select CloseDate, Volume 
  from t
  where CloseDate is not null
  ) s
group by dateadd(month, datediff(month, 0, OpenDate )  , 0)

rextester 演示:http://rextester.com/ZGZT62294

返回:

+------------+---------+
|  OpenDate  | Volumne |
+------------+---------+
| 2016-01-01 |   46000 |
| 2016-02-01 |   56000 |
| 2016-03-01 |   10000 |
+------------+---------+