#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    58
    Rep Power
    5

    Query for multiple date ranges at the same time


    Hi all

    I'm sure this is simpler than I think but I can't seem to find the answer anywhere.

    Using read-only access to SQL, I need to run this query 13 times - once for each month:

    Code:
      SELECT Contracts.CodeID
           , sum (Contracts.ALine) 
           + sum (Contracts.BLine) 
           - sum (Contracts.CostLine) AS revenue
        FROM Contracts
       WHERE Contracts.Period BETWEEN '2014-01-01' AND '2014-01-31' 
         AND Contracts.LocationID='AB' 
    GROUP BY Contracts.CodeID;
    To get a result like:

    Code:
    CodeID          Revenue
    CodeID_A	12345.99
    CodeID_B	54321.25
    CodeID_C	34567.82
    At the moment I'm using PHP to loop through 13 date ranges. Is there a way of sending the query once and getting 13 columns of results?

    The Contracts.Period column is datetime and I'm looking for entire months each time; with this in mind is it even possible to ask for "13 complete months starting from X" ?

    I'm anticipating a result like this:

    Code:
    CodeID		Month1		Month2		Month3
    CodeID_A	12345.99	1234.00		4567.50
    CodeID_B	54321.25	0		6789.00
    CodeID_C	34567.82	3456.99		0
    Thank you.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,407
    Rep Power
    4286
    Originally Posted by phpcog2
    ...is it even possible to ask for "13 complete months starting from X" ?
    sure

    start with this --
    Code:
    SELECT Contracts.CodeID
         , MONTH(Contracts.Period) AS mth
         , SUM(Contracts.ALine) + 
           SUM(Contracts.BLine) - 
           SUM(Contracts.CostLine) AS revenue
      FROM Contracts
     WHERE Contracts.LocationID = 'AB' 
    GROUP 
        BY Contracts.CodeID
         , MONTH(Contracts.Period)
    now you can add an additional WHERE clause to specify your stating datetime value

    but something's gonna overlap (as there are only 12 months in a year, not 13)

    so instead of MONTH(Contracts.Period) for the grouping, you'll want to use CONVERT(CHAR(7),Contracts.Period,111)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    58
    Rep Power
    5
    Thanks Rudy

    That really well, once I understood what you meant by
    add an additional WHERE clause to specify your stating datetime value
    For anyone else looking for a similar answer, you need to specify the entire date range and let MONTH and GROUP BY do the rest (or at least, that's what seems to work).

    The only part that doesn't work is the CONVERT grouping when running more than 12 months.

    For example, this works:

    Code:
    SELECT Contracts.CodeID
         , MONTH(Contracts.Period) AS mth
         , SUM(Contracts.ALine) + 
           SUM(Contracts.BLine) - 
           SUM(Contracts.CostLine) AS revenue
      FROM Contracts
     WHERE Contracts.LocationID = 'AB' 
       AND Contracts.Period BETWEEN '2014-01-01' AND '2014-12-31'
    GROUP 
        BY Contracts.CodeID
         , MONTH(Contracts.Period)
    But if I replace the last grouping with CONVERT(CHAR(7),Contracts.Period,111) in order to collect more than 12 months, I get the error:

    Code:
    Column 'Contracts.Period' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
    I think I'm just getting the syntax wrong somehow.

    Thanks again.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,407
    Rep Power
    4286
    Originally Posted by phpcog2
    The only part that doesn't work is the CONVERT grouping when running more than 12 months.
    are you sure?

    please show the entire query where you tried it

    if you have the CONVERT in the GROUP BY, it should be in the SELECT list as well
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    58
    Rep Power
    5
    Well, I'm sure that it doesn't work but I'm not sure I've applied your suggestion correctly !

    Code:
    SELECT Contracts.CodeID
         , MONTH(Contracts.Period) AS mth
         , SUM(Contracts.ALine) + 
           SUM(Contracts.BLine) - 
           SUM(Contracts.CostLine) AS revenue
      FROM Contracts
     WHERE Contracts.LocationID = 'AB' 
       AND Contracts.Period BETWEEN '2014-01-01' AND '2014-12-31'
    GROUP 
        BY Contracts.CodeID
         , CONVERT(CHAR(7),Contracts.Period,111)
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,407
    Rep Power
    4286
    Originally Posted by phpcog2
    Well, I'm sure that it doesn't work but I'm not sure I've applied your suggestion correctly !
    make sure the SELECT list and the GROUP BY both have the same columns

    Code:
    SELECT Contracts.CodeID
         , MONTH(Contracts.Period) AS mth
         , CONVERT(CHAR(7),Contracts.Period,111) AS yr_mth
         , SUM(Contracts.ALine) + 
           SUM(Contracts.BLine) - 
           SUM(Contracts.CostLine) AS revenue
      FROM Contracts
     WHERE Contracts.LocationID = 'AB' 
       AND Contracts.Period BETWEEN '2014-01-01' AND '2014-12-31'
    GROUP 
        BY Contracts.CodeID
         , CONVERT(CHAR(7),Contracts.Period,111)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    58
    Rep Power
    5
    Ah - I just saw a point you made:
    if you have the CONVERT in the GROUP BY, it should be in the SELECT list as well
    Doh.
    I've changed that, now it works perfectly.

    Thank you again - maybe you should write another book so I can buy that one as well!
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,407
    Rep Power
    4286
    see post #6

    Comments on this post

    • phpcog2 agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2003
    Posts
    804
    Rep Power
    102
    Originally Posted by phpcog2
    Thanks Rudy

    That really well, once I understood what you meant by
    For anyone else looking for a similar answer, you need to specify the entire date range and let MONTH and GROUP BY do the rest (or at least, that's what seems to work).

    The only part that doesn't work is the CONVERT grouping when running more than 12 months.

    For example, this works:

    Code:
    SELECT Contracts.CodeID
         , MONTH(Contracts.Period) AS mth
         , SUM(Contracts.ALine) + 
           SUM(Contracts.BLine) - 
           SUM(Contracts.CostLine) AS revenue
      FROM Contracts
     WHERE Contracts.LocationID = 'AB' 
       AND Contracts.Period BETWEEN '2014-01-01' AND '2014-12-31'
    GROUP 
        BY Contracts.CodeID
         , MONTH(Contracts.Period)
    But if I replace the last grouping with CONVERT(CHAR(7),Contracts.Period,111) in order to collect more than 12 months, I get the error:

    Code:
    Column 'Contracts.Period' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
    I think I'm just getting the syntax wrong somehow.

    Thanks again.
    I think you're over-thinking this. Why not just select the month and year and group by both of them:
    SQL Code:
    SELECT Contracts.CodeID,
        MONTH(Contracts.Period) AS mth,
        YEAR(Contracts.Period) AS yr,
        SUM(Contracts.ALine) + SUM(Contracts.BLine) - SUM(Contracts.CostLine) AS revenue
    FROM Contracts
    WHERE
        Contracts.LocationID = 'AB' 
        AND Contracts.Period BETWEEN '2014-01-01' AND '2014-12-31'
    GROUP BY
        Contracts.CodeID,
        MONTH(Contracts.Period),
        YEAR(Contracts.Period)
    ORDER BY yr, mth
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,407
    Rep Power
    4286
    Originally Posted by Clone53421
    I think you're over-thinking this.
    you are, too

    selecting month and year and grouping by both of them is pretty much the same as selecting the year-month and grouping by it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  20. #11
  21. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2003
    Posts
    804
    Rep Power
    102
    Yes, it does the same thing, but I think it's simpler and more obvious.

    In the one case you're using integers returned by built-in date functions. In the other case you're using a character string that you've hacked out of a yyyy/mm/dd date pattern which is hidden behind the non-obvious style code 111. Just because the end result is the same doesn't make the Rube Goldberg version equivalent.

    As a general rule, don't convert a date to text unless you must. Just yesterday I fixed a bug where suddenly a report began sorting dates like this: 1/1/2014 1/10/2014 1/11/2014 ... 1/2/2014. Yeah, because somebody decided it would be fine to force the datetime into text, and then someone upgraded their computer and the default shortdate format didn't include leading zeros anymore. The fix was simple: nix the code that converted it to text and leave the datetime alone. There was never any reason to convert it to text anyway - SQL already knows how to sort and display dates!
    Last edited by Clone53421; February 13th, 2014 at 11:41 AM.
  22. #12
  23. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,407
    Rep Power
    4286
    your anecdote is good, and you should feel good -- you've done the "show me" state proud

    i'm going to concede this one

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    58
    Rep Power
    5
    Thanks both.

    I got it working using a stylecode (I want the output as YYYY-MM-DD) then re-wrote using Clone53421's suggestions so I understand both approaches now.

    Very helpful.

IMN logo majestic logo threadwatch logo seochat tools logo