sql - 按半年计算金额

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

如果聚合 = 半年一次

我的 table

表格1

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)

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

最佳答案

你应该按年份和 (MONTH-1)/6 = 0 for [1-6] or 1 for [7-12] 分组

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

SQLFiddle demo

关于sql - 按半年计算金额，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20799118/