sql - 按半年计算金额

Question

我有一个问题，我想通过日期合并我的表数据，给定表中提供的所有信息

如果聚合 = 半年

我的 table

表一

Amount |   TheDate      | Aggregation
-------+----------------+-------------    
100    |  2013-01-01    | Quaterly
100    |  2013-02-01    | Quaterly
100    |  2013-03-01    | Quaterly
100    |  2013-04-01    | Quaterly
100    |  2013-05-01    | Quaterly
100    |  2013-06-01    | Quaterly
200    |  2013-07-01    | Quaterly
200    |  2013-07-01    | Quaterly
200    |  2013-09-01    | Quaterly
200    |  2013-10-01    | Quaterly
200    |  2013-11-01    | Quaterly
200    |  2013-12-01    | Quaterly

我想要这样的结果

Amount |   Date      | Aggregation
-------+-------------+-------------    
600    |  2013-06-01 | Quaterly 
1200   |  2013-12-01 | Quaterly 

查询:

SELECT  
    DATEADD(MONTH, 2, DATEADD(quarter, DATEDIFF(quarter, 0, TheDate), 0)) AS Amount,
    ROUND(CONVERT(FLOAT, SUM(Amount)) / 1000, 0) AS Total
FROM   
    myTble    
GROUP BY
    DATEADD(quarter, DATEDIFF(QQ, 0, TheDate), 0)

我正在尝试上面的查询，但它只适用于季度

Answer 1

您应该按 YEAR 和 (MONTH-1)/6 = 0 表示 [1-6] 或 1 表示 [7-12]

SELECT SUM(Amount),
       MAX(TheDate)
FROM T
GROUP BY YEAR(TheDate),(MONTH(TheDate)-1)/6

SQLFiddle demo