Results 1 to 12 of 12
  1. #1
    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.


    Thanks for your help!

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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

    Thanks for your reply.

    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.

    Thanks for your help!
    Last edited by Colin Legg; 06-28-11 at 08:08.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my apologies, i completely overlooked that you wanted the year included
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    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 09:31.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    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. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT z1.d
    , DATEADD(month
    ,    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Colin Legg View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
       RETURN DATEADD(month
    ,     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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    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
  •