I have a question, I want merge my table data through date, all information provide in given table
If Aggregation = Semiannual
My table
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
I want result like this
Amount | Date | Aggregation
-------+-------------+-------------
600 | 2013-06-01 | Quaterly
1200 | 2013-12-01 | Quaterly
Query:
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)
I'm trying above query but it only works for quarter