Sessions Sessions - 4 months ago 11
SQL Question

Get dates in 4 quarters in SQL Server

I am trying to get a report that gives me data in four quarters.

For example from

1/1/2015-3/31/2015 as Q1
4/1/2015-6/30/2015 as Q2
7/1/2015-9/30/2015 as Q3
10/1/2015- 12/31/2015 as Q4


the dates are in this format : 2015-01-01 00:00.000.

How can I get the output of dates in 4 quarters in SQL Server?

edits--

so i tried:

select count (cctransactions), avg(visits)
(CASE WHEN MONTH(1/1/2015) >= 1 AND MONTH(3/31/2015) <=3 THEN'Q1'
WHEN MONTH(4/1/2015) >= 4 AND MONTH(6/30/2015) <=6 THEN'Q2'
WHEN MONTH(7/1/2015) >= 7 AND MONTH(9/31/2015) <=9 THEN'Q3'
WHEN MONTH(10/1/2015) >= 10 AND MONTH(12/31/2015) <=12 THEN 'Q4' END) AS 'QUARTER'
FROM [Database].[dbo].[transactionMaster]
Group by cctransactions, ‘Quarter’


It gave me an error saying GROUP BY expression must contain at least one column that is not an outer reference. I am not sure how to do an outer select.

Answer

Not sure if this is what you want to achieve. But I'd suggest using MONTH and CASE statement. This will properly label data which quarter they belong to.

SELECT 
    CASE
        WHEN MONTH(FieldDate) >= 1 AND MONTH(FieldDate) <=3
        THEN
            'Q1'
        WHEN MONTH(FieldDate) >= 4 AND MONTH(FieldDate) <=6
        THEN
            'Q2'
        WHEN MONTH(FieldDate) >= 7 AND MONTH(FieldDate) <=9
        THEN
            'Q3'
        WHEN MONTH(FieldDate) >= 10 AND MONTH(FieldDate) <=12
        THEN
            'Q4'
    END 'QUARTER',
    Field1,
    Field2,
    ...
    FieldX 
FROM 
    [DataBase].[dbo].[SomeTable];
WHERE
    YEAR(FieldDate) = @YEAR
Comments