Calculate amount on the basis of semiannual

Viewed 1012

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

1 Answers
Related