CODEWITHSUNDEEP
sql-serversql-server-2008
Prashant16
Prashant16

Reputation: 1526

Get Data Quarterly in SQL Server

I am using SQLServer2008.

SELECT  Amount ,
        OpportunityCloseDate
FROM    dbo.OpportunityMaster AS om

and output is:

Amount      OpportunityCloseDate
------------------------------------
25458.00    2012-07-02 00:00:00.000
54555.00    2012-08-16 00:00:00.000
2154.00     2012-08-21 00:00:00.000
123458.00   2013-08-31 00:00:00.000
54546.00    2013-08-28 00:00:00.000
1235.00     2013-08-22 00:00:00.000
56454.00    2013-08-22 00:00:00.000
156.00      2013-09-13 00:00:00.000
34534.00    2013-09-04 00:00:00.000
34355.00    2013-09-02 00:00:00.000
76568.00    2013-09-01 00:00:00.000
235646.00   2013-09-10 00:00:00.000
54465.00    2013-09-18 00:00:00.000
244254.00   2013-12-19 00:00:00.000

Expected Output :

Amount      Quarter
------------------
82167.00    Q1
671417.00   Q2
244254.00   Q3

In my database Year starts from 1st April. I want to get this data quarterly. I dont know much about quarter..Thanks for help..

Upvotes: 0

Views: 11884

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239724

I assume you want to sum the data for each quarter:

;With Totals as (

    SELECT  SUM(Amount) as Total,
            DATEADD(quarter,DATEDIFF(quarter,0,OpportunityCloseDate),0) as RoundQuarter
    FROM    dbo.OpportunityMaster AS om
    GROUP BY
            DATEADD(quarter,DATEDIFF(quarter,0,OpportunityCloseDate),0)
)
select
    Total,
    DATEPART(year,RoundQuarter) -
       CASE WHEN DATEPART(quarter,RoundQuarter) = 1 THEN 1 ELSE 0 END as FinancialYear,
    CASE WHEN DATEPART(quarter,RoundQuarter) = 1 THEN 4
       ELSE DATEPART(quarter,RoundQuarter) - 1 END as FinancialQuarter
from
    Totals

DATEADD

This works by working out the (whole number) number of quarters that have occurred since 1900-01-011, and then by adding that same number of quarters on to 1900-01-01. This has the effect of rounding each date down to the 1st day in each quarter.

10 when converted to a datetime is treated as 1900-01-01.

Upvotes: 1

Related Questions