# Thread: Group Dates By Quarter

1. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511

## Unanswered: Group Dates By Quarter

Hi,

I have a query such as:
Code:
```SELECT SaleDate, SUM(Price) AS Price
FROM SalesTable
GROUP BY SaleDate
ORDER BY SaleDate```
I would like to group the SaleDate into quarters. My quarterly roll dates are:

20 March
20 June
20 September
20 December

each year. These dates are always unadjusted for weekends and holidays, so will always be the 20th of the 'quarterly month'.

Here are some examples of the 'conversions' I would like:

01 March 2011 would become 20 March 2011
19 March 2011 would become 20 March 2011
20 March 2011 would become 20 March 2011
21 March 2011 would become 20 June 2011

So if my current query returns:
Code:
```SaleDate            Price
01 March 2011       18
05 June 2011        16
20 June 2011        9
28 June 2011        20
18 September 2011   18
01 March 2012       2```
I would like the new query to return:
Code:
```SaleDate            Price
20 March 2011       18
20 June 2011        25
20 September 2011   38
20 March 2012       2```

I had a read through this blog but I couldn't work out how to adapt the examples to do this.

(SQL Server 2005)
Last edited by Colin Legg; 06-28-11 at 05:57.

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Code:
```SELECT CASE WHEN MONTH(SaleDate) IN ( 1, 2 )
THEN '20 March'
WHEN MONTH(SaleDate) = 3
AND DAY(SaleDate) >= 20
THEN '20 June'
WHEN MONTH(SaleDate) = 3
THEN '20 March'
WHEN MONTH(SaleDate) IN ( 4, 5 )
THEN '20 June'
WHEN MONTH(SaleDate) = 6
AND DAY(SaleDate) >= 20
THEN '20 September'
WHEN MONTH(SaleDate) = 6
THEN '20 June'
WHEN MONTH(SaleDate) IN ( 7, 8 )
THEN '20 September'
WHEN MONTH(SaleDate) = 9
AND DAY(SaleDate) >= 20
THEN '20 December'
WHEN MONTH(SaleDate) = 9
THEN '20 September'
WHEN MONTH(SaleDate) IN ( 10, 11 )
THEN '20 December'
WHEN MONTH(SaleDate) = 12
AND DAY(SaleDate) >= 20
THEN '20 March'
ELSE '20 December'  END  AS quarter
, SUM(Price) AS Price
FROM SalesTable
GROUP
BY quarter```
note that MONTH() and DAY() might not be ANSI SQL, but i figger you prolly won't mind

3. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Hi Rudy,

Two questions...

(1) How do I change the query so that the Quarter dates also display the year portion? I need the quarters to be segregated across years.

(2) With this part of the query:
Code:
```WHEN MONTH(SaleDate) = 12
AND DAY(SaleDate) >= 20
THEN '20 March'```
If the SaleDate was, for example, 25 December 2010, would it currently be allocated to 20 March 2010 or 20 March 2011? I would need the latter.

Last edited by Colin Legg; 06-28-11 at 07:08.

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
my apologies, i completely overlooked that you wanted the year included

5. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
I think I've managed to do it by adapting your suggestion as follows:

Code:
```SELECT
SaleIMMDate, Sum(Price) As Price
FROM
(SELECT
CASE
WHEN MONTH(SaleDate) IN (1,2) THEN CAST(RTRIM(YEAR(SaleDate)*10000+3*100+20) As Datetime)
WHEN MONTH(SaleDate) = 3 AND DAY(SaleDate)<=20 THEN CAST(RTRIM(YEAR(SaleDate)*10000+3*100+20) As Datetime)
WHEN MONTH(SaleDate) = 3 THEN CAST(RTRIM(YEAR(SaleDate)*10000+6*100+20) As Datetime)

WHEN MONTH(SaleDate) IN (4,5) THEN CAST(RTRIM(YEAR(SaleDate)*10000+6*100+20) As Datetime)
WHEN MONTH(SaleDate) = 6 AND DAY(SaleDate)<=20 THEN CAST(RTRIM(YEAR(SaleDate)*10000+6*100+20) As Datetime)
WHEN MONTH(SaleDate) = 6 THEN CAST(RTRIM(YEAR(SaleDate)*10000+9*100+20) As Datetime)

WHEN MONTH(SaleDate) IN (7,8) THEN CAST(RTRIM(YEAR(SaleDate)*10000+9*100+20) As Datetime)
WHEN MONTH(SaleDate) = 9 AND DAY(SaleDate)<=20 THEN CAST(RTRIM(YEAR(SaleDate)*10000+9*100+20) As Datetime)
WHEN MONTH(SaleDate) = 9 THEN CAST(RTRIM(YEAR(SaleDate)*10000+12*100+20) As Datetime)

WHEN MONTH(SaleDate) IN (10,11) THEN CAST(RTRIM(YEAR(SaleDate)*10000+12*100+20) As Datetime)
WHEN MONTH(SaleDate) = 12 AND DAY(SaleDate)<=20 THEN CAST(RTRIM(YEAR(SaleDate)*10000+12*100+20) As Datetime)
WHEN MONTH(SaleDate) = 12 THEN  CAST(RTRIM((YEAR(SaleDate)+1)*10000+3*100+20) As Datetime)
END As SaleIMMDate,
SUM(Price) AS Price
FROM
SalesTable
GROUP BY
SaleDate)
AS T
GROUP BY
SaleIMMDate
ORDER BY
SaleIMMDate```
It's about the best I could do... if you know a better way of deriving the quarterly dates then I'm all ears.

Also, given the above query, to group by SaleIMMDate I had to use a subquery. Is there a better way to do that?

Thanks again...
Last edited by Colin Legg; 06-28-11 at 08:31.

6. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
WHEN MONTH(SaleDate) = 12
THEN CAST(RTRIM((YEAR(SaleDate)+1)*10000+3*100+20) As Datetime)

that's pretty good

it also has the advantages that the ORDER BY can now be easily applied to the pure date, rather than being fudged (you may have noticed i dropped the ORDER BY), and also since it's a pure date you can format the results easily in your application language

you don't need the RTRIM, by the way

7. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
I take no credit for it. I made a minor adaptation to Uri Dimant's post here.
I don't pretend to understand the logic so I left the RTRIM in there!

By the way, is using nested SELECT statements (as I have done - I just amended my previous post to show the full query) the correct way to group by SaleIMMDate?

8. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Code:
```SELECT z1.d
,    3 * ((DateDiff(month, '2000-01-01', z1.d)
+       CASE WHEN 20 < DATEPART(day, z1.d)
THEN 1
ELSE 0
END) / 3), '2001-03-20')
FROM (SELECT DATEADD(day, v.number, '2011-01-01') AS d
FROM master.dbo.spt_values AS v
WHERE  'P' = v.type) AS z1```
-PatP

9. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by Colin Legg
By the way, is using nested SELECT statements (as I have done - I just amended my previous post to show the full query) the correct way to group by SaleIMMDate?
yes

the way you've done it, with the subquery in the FROM clause, it's called a derived table or inline view

quite handy for resolving complex expressions to single alias names which you want to use elsewhere

10. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Thanks again, Rudy and Pat.

11. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Uff-da, zinged by another typo!!!

Someone pointed out a typo for me (thanks for that). Since I had to repost for a fix, I decided to "guild-the-lilly: and make a function to make it easier to use.
Code:
```--  ptp  20110628  See http://www.dbforums.com/microsoft-sql-server/1667705-group-dates-quarter.html

CREATE FUNCTION dbo.CollinLeggQuarterEnd(
@pdArg		DATETIME
)  RETURNS DATETIME AS
BEGIN
,     3 * ((DateDiff(month, '2000-01-01', @pdArg)
+        CASE WHEN 20 < DATEPART(day, @pdArg)
THEN 1
ELSE 0
END) / 3), '2000-03-20')
END
GO

--  Sample run to see the function work

SELECT z1.d, dbo.CollinLeggQuarterEnd(z1.d)
FROM (SELECT DATEADD(day, v.number, '2011-01-01') AS d
FROM master.dbo.spt_values AS v
WHERE  'P' = v.type) AS z1```
-PatP

12. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
It's not entirely without irony that you spelt my name wrong too then!

Thanks Pat, I'll study it tomorrow at work.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•