How to break down time span in Categories

• shezi

  SSCommitted

  Points: 1514

  More actions

  February 10, 2017 at 2:29 pm

  #327098

  Basically I have list of the tickets (records) in the db table ticket, and I need to find how long the ticket has been opened till today and it should break down in the following way. 
  

  Basically this table tells me that  between 0 to 5 days , there are 2 tickets still opened.. 5-10 days - 10 tickets opened and so on..

  Or how do I write sql query?

  Also , I wouldn't mind seeing the report this way either. Whichever is easier

  DAYS    NO OF TICKETS
  0-5             2
  5-10          10
  10-15         3

  Here are the Fields I"m using
  TicketID
  TicketStartDate

  Please advise

  Thanks

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  February 10, 2017 at 3:04 pm

  #1927898

  I'm thinking your day ranges probably shouldn't overlap, so I'm assuming you mean 0-5, 6-10, 11-15, > 15
  
SELECT
    SUM(CASE WHEN OpenDays <= 5 THEN 1 END) AS [0-5 days],
    SUM(CASE WHEN OpenDays BETWEEN 6 AND 10 THEN 1 END) AS [6-10 days],
    SUM(CASE WHEN OpenDays BETWEEN 11 AND 15 THEN 1 END) AS [11-15 days],
    SUM(CASE WHEN OpenDays > 15 THEN 1 END) AS [more than 15 days],
    COUNT(*) AS [Total]
  FROM
    (SELECT DATEDIFF(day, TicketStartDate, GetDate()) AS OpenDays FROM dbo.Ticket WHERE TicketCloseDate IS NULL) ot


• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  February 10, 2017 at 3:39 pm

  #1927902

  The good method that Chris posted above is called a "CROSSTAB".  You can learn how it works at the following URL:
  http://www.sqlservercentral.com/articles/T-SQL/63681/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• shezi

  SSCommitted

  Points: 1514

  More actions

  February 10, 2017 at 3:45 pm

  #1927903

  Chris Harshman - Friday, February 10, 2017 3:04 PM

  I'm thinking your day ranges probably shouldn't overlap, so I'm assuming you mean 0-5, 6-10, 11-15, > 15
  
SELECT
    SUM(CASE WHEN OpenDays <= 5 THEN 1 END) AS [0-5 days],
    SUM(CASE WHEN OpenDays BETWEEN 6 AND 10 THEN 1 END) AS [6-10 days],
    SUM(CASE WHEN OpenDays BETWEEN 11 AND 15 THEN 1 END) AS [11-15 days],
    SUM(CASE WHEN OpenDays > 15 THEN 1 END) AS [more than 15 days],
    COUNT(*) AS [Total]
  FROM
    (SELECT DATEDIFF(day, TicketStartDate, GetDate()) AS OpenDays FROM dbo.Ticket WHERE TicketCloseDate IS NULL) ot


  This works really well..thank you!.
  How do I display in two columns? For example

  DAYS    COUNT
  (0-5)  ...   1
  (6-10)..     10
  1(1-15)

• shezi

  SSCommitted

  Points: 1514

  More actions

  February 13, 2017 at 9:30 am

  #1928141

  I was wondering how can I display the data in this following format? Thanks
  

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  February 13, 2017 at 10:05 am

  #1928158

  if you just want a row for each group, you need to calculate the ranges into a new column, maybe something like this:
  
SELECT dr.DaysRange, COUNT(*) AS [Count]
  FROM
    (SELECT DATEDIFF(day, TicektStartDate, GetDate()) AS OpenDays FROM dbo.Ticket WHERE TicketCloseDate IS NULL) ot
    CROSS APPLY
        (SELECT CASE
           WHEN ot.OpenDays <= 5 THEN '0-5 days'
           WHEN ot.OpenDays BETWEEN 6 AND 10 THEN '6-10 days'
           WHEN ot.OpenDays BETWEEN 11 AND 15 THEN '11-15 days'
           WHEN ot.OpenDays > 15 THEN 'over 15 days' END AS DaysRange) dr
  GROUP BY dr.DaysRange


  I learned the cross tab way of pivoting data way back in the mid 90's using FoxPro's IIF function in SQL code, did it using DECODE function in Oracle, and now this way in SQL Server.  I've found it more flexible and performs well compared to the PIVOT operator in TSQL.

  Jeff, yes I usually reference an article from a well respected person such as yourself whenever I post, sorry I missed that this time.

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  February 13, 2017 at 1:16 pm

  #1928198

  Chris Harshman - Monday, February 13, 2017 10:05 AM

  Jeff, yes I usually reference an article from a well respected person such as yourself whenever I post, sorry I missed that this time.

  Absolutely no problem, Chris.  I figured that, except for a lot of the heavy hitters (yourself included in that) know how they work  and since CROSSTABs are no longer documented in BOL and, therefor, are becoming a bit of a lost art even though it's still an incredibly useful "Black Art", I'd post a link.  Keep hitting them as hard as you have. 😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• shezi

  SSCommitted

  Points: 1514

  More actions

  February 13, 2017 at 2:41 pm

  #1928219

  Chris Harshman - Monday, February 13, 2017 10:05 AM

  if you just want a row for each group, you need to calculate the ranges into a new column, maybe something like this:
  
SELECT dr.DaysRange, COUNT(*) AS [Count]
  FROM
    (SELECT DATEDIFF(day, TicektStartDate, GetDate()) AS OpenDays FROM dbo.Ticket WHERE TicketCloseDate IS NULL) ot
    CROSS APPLY
        (SELECT CASE
           WHEN ot.OpenDays <= 5 THEN '0-5 days'
           WHEN ot.OpenDays BETWEEN 6 AND 10 THEN '6-10 days'
           WHEN ot.OpenDays BETWEEN 11 AND 15 THEN '11-15 days'
           WHEN ot.OpenDays > 15 THEN 'over 15 days' END AS DaysRange) dr
  GROUP BY dr.DaysRange


  I learned the cross tab way of pivoting data way back in the mid 90's using FoxPro's IIF function in SQL code, did it using DECODE function in Oracle, and now this way in SQL Server.  I've found it more flexible and performs well compared to the PIVOT operator in TSQL.

  Jeff, yes I usually reference an article from a well respected person such as yourself whenever I post, sorry I missed that this time.

  This is working great for me. How do I order the list by Days Range?  "over 15 days" which is supposed to be the last row is showing as the second last row . Please advise

  Thank you so much!

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  February 13, 2017 at 2:53 pm

  #1928225

  ah, sorry, forgot about sorting, to get the numbers to sort right would have to use an extra space in front of the 0-5 and 6-10:
  
SELECT dr.DaysRange, COUNT(*) AS [Count]
  FROM
    (SELECT DATEDIFF(day, TicektStartDate, GetDate()) AS OpenDays FROM dbo.Ticket WHERE TicketCloseDate IS NULL) ot
    CROSS APPLY
        (SELECT CASE
           WHEN ot.OpenDays <= 5 THEN ' 0-5 days'
           WHEN ot.OpenDays BETWEEN 6 AND 10 THEN ' 6-10 days'
           WHEN ot.OpenDays BETWEEN 11 AND 15 THEN '11-15 days'
           WHEN ot.OpenDays > 15 THEN 'over 15 days' END AS DaysRange) dr
  GROUP BY dr.DaysRange
  ORDER BY dr.DaysRange


• shezi

  SSCommitted

  Points: 1514

  More actions

  February 13, 2017 at 3:01 pm

  #1928232

  Chris Harshman - Monday, February 13, 2017 2:53 PM

  ah, sorry, forgot about sorting, to get the numbers to sort right would have to use an extra space in front of the 0-5 and 6-10:
  
SELECT dr.DaysRange, COUNT(*) AS [Count]
  FROM
    (SELECT DATEDIFF(day, TicektStartDate, GetDate()) AS OpenDays FROM dbo.Ticket WHERE TicketCloseDate IS NULL) ot
    CROSS APPLY
        (SELECT CASE
           WHEN ot.OpenDays <= 5 THEN ' 0-5 days'
           WHEN ot.OpenDays BETWEEN 6 AND 10 THEN ' 6-10 days'
           WHEN ot.OpenDays BETWEEN 11 AND 15 THEN '11-15 days'
           WHEN ot.OpenDays > 15 THEN 'over 15 days' END AS DaysRange) dr
  GROUP BY dr.DaysRange
  ORDER BY dr.DaysRange


  Excellent! Thank you

• shezi

  SSCommitted

  Points: 1514

  More actions

  February 15, 2017 at 11:13 am

  #1928679

  Chris Harshman - Monday, February 13, 2017 2:53 PM

  ah, sorry, forgot about sorting, to get the numbers to sort right would have to use an extra space in front of the 0-5 and 6-10:
  
SELECT dr.DaysRange, COUNT(*) AS [Count]
  FROM
    (SELECT DATEDIFF(day, TicektStartDate, GetDate()) AS OpenDays FROM dbo.Ticket WHERE TicketCloseDate IS NULL) ot
    CROSS APPLY
        (SELECT CASE
           WHEN ot.OpenDays <= 5 THEN ' 0-5 days'
           WHEN ot.OpenDays BETWEEN 6 AND 10 THEN ' 6-10 days'
           WHEN ot.OpenDays BETWEEN 11 AND 15 THEN '11-15 days'
           WHEN ot.OpenDays > 15 THEN 'over 15 days' END AS DaysRange) dr
  GROUP BY dr.DaysRange
  ORDER BY dr.DaysRange


  One more question, how do you display the rows (6-10 days) even if the conditions are not met. I would like to display all the rows and have default value set to 0. Currently it will only show rows when condition is met in case statement. Please advise

  Thanks!

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  February 15, 2017 at 12:18 pm

  #1928696

  shezi - Wednesday, February 15, 2017 11:13 AM

  Chris Harshman - Monday, February 13, 2017 2:53 PM

  ah, sorry, forgot about sorting, to get the numbers to sort right would have to use an extra space in front of the 0-5 and 6-10:
  
SELECT dr.DaysRange, COUNT(*) AS [Count]
  FROM
    (SELECT DATEDIFF(day, TicektStartDate, GetDate()) AS OpenDays FROM dbo.Ticket WHERE TicketCloseDate IS NULL) ot
    CROSS APPLY
        (SELECT CASE
           WHEN ot.OpenDays <= 5 THEN ' 0-5 days'
           WHEN ot.OpenDays BETWEEN 6 AND 10 THEN ' 6-10 days'
           WHEN ot.OpenDays BETWEEN 11 AND 15 THEN '11-15 days'
           WHEN ot.OpenDays > 15 THEN 'over 15 days' END AS DaysRange) dr
  GROUP BY dr.DaysRange
  ORDER BY dr.DaysRange


  One more question, how do you display the rows (6-10 days) even if the conditions are not met. I would like to display all the rows and have default value set to 0. Currently it will only show rows when condition is met in case statement. Please advise

  Thanks!

  Try this:

  
WITH RANGES AS (

      SELECT '0-5 days' AS DAYS_RANGE, 1 AS SORT_VALUE, 0 AS RANGE_LOW, 6 AS RANGE_HIGH
    UNION ALL
    SELECT '6-10 days', 2, 6, 10
    UNION ALL
    SELECT '11-15 days', 3, 10, 15
    UNION ALL
    SELECT 'over 15 days', 4, 15, 2147483647
),
    RAW_DATA AS (

          SELECT DATEDIFF(day, TicektStartDate, GETDATE()) AS OpenDays
        FROM dbo.Ticket
        WHERE TicketCloseDate IS NULL
)
SELECT R.DAYS_RANGE, ISNULL(COUNT(*), 0) AS [Count]
FROM RANGES AS R
    LEFT OUTER JOIN RAW_DATA AS RD
        ON RD.OpenDays >= R.RANGE_LOW
        AND RD.OpenDays < R.RANGE_HIGH
GROUP BY R.DAYS_RANGE
ORDER BY R.SORT_VALUE;


  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• shezi

  SSCommitted

  Points: 1514

  More actions

  February 16, 2017 at 10:55 am

  #1928901

  sgmunson - Wednesday, February 15, 2017 12:18 PM

  shezi - Wednesday, February 15, 2017 11:13 AM

  Chris Harshman - Monday, February 13, 2017 2:53 PM

  ah, sorry, forgot about sorting, to get the numbers to sort right would have to use an extra space in front of the 0-5 and 6-10:
  
SELECT dr.DaysRange, COUNT(*) AS [Count]
  FROM
    (SELECT DATEDIFF(day, TicektStartDate, GetDate()) AS OpenDays FROM dbo.Ticket WHERE TicketCloseDate IS NULL) ot
    CROSS APPLY
        (SELECT CASE
           WHEN ot.OpenDays <= 5 THEN ' 0-5 days'
           WHEN ot.OpenDays BETWEEN 6 AND 10 THEN ' 6-10 days'
           WHEN ot.OpenDays BETWEEN 11 AND 15 THEN '11-15 days'
           WHEN ot.OpenDays > 15 THEN 'over 15 days' END AS DaysRange) dr
  GROUP BY dr.DaysRange
  ORDER BY dr.DaysRange


  One more question, how do you display the rows (6-10 days) even if the conditions are not met. I would like to display all the rows and have default value set to 0. Currently it will only show rows when condition is met in case statement. Please advise

  Thanks!

  Try this:

  
WITH RANGES AS (

      SELECT '0-5 days' AS DAYS_RANGE, 1 AS SORT_VALUE, 0 AS RANGE_LOW, 6 AS RANGE_HIGH
    UNION ALL
    SELECT '6-10 days', 2, 6, 10
    UNION ALL
    SELECT '11-15 days', 3, 10, 15
    UNION ALL
    SELECT 'over 15 days', 4, 15, 2147483647
),
    RAW_DATA AS (

          SELECT DATEDIFF(day, TicektStartDate, GETDATE()) AS OpenDays
        FROM dbo.Ticket
        WHERE TicketCloseDate IS NULL
)
SELECT R.DAYS_RANGE, ISNULL(COUNT(*), 0) AS [Count]
FROM RANGES AS R
    LEFT OUTER JOIN RAW_DATA AS RD
        ON RD.OpenDays >= R.RANGE_LOW
        AND RD.OpenDays < R.RANGE_HIGH
GROUP BY R.DAYS_RANGE
ORDER BY R.SORT_VALUE;


  it gave an aggregate error but when I added the column names in the GROUP BY then it "partially" worked. I noticed it didn't give accurate readings when there is no data in the Ticket table. All the rows (0-5, 6-10..etc) returned 1 instead of 0.

  Thank you for your help, we are pretty close

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  February 16, 2017 at 11:20 am

  #1928912

  shezi - Thursday, February 16, 2017 10:55 AM

  sgmunson - Wednesday, February 15, 2017 12:18 PM

  shezi - Wednesday, February 15, 2017 11:13 AM

  Chris Harshman - Monday, February 13, 2017 2:53 PM

  ah, sorry, forgot about sorting, to get the numbers to sort right would have to use an extra space in front of the 0-5 and 6-10:
  
SELECT dr.DaysRange, COUNT(*) AS [Count]
  FROM
    (SELECT DATEDIFF(day, TicektStartDate, GetDate()) AS OpenDays FROM dbo.Ticket WHERE TicketCloseDate IS NULL) ot
    CROSS APPLY
        (SELECT CASE
           WHEN ot.OpenDays <= 5 THEN ' 0-5 days'
           WHEN ot.OpenDays BETWEEN 6 AND 10 THEN ' 6-10 days'
           WHEN ot.OpenDays BETWEEN 11 AND 15 THEN '11-15 days'
           WHEN ot.OpenDays > 15 THEN 'over 15 days' END AS DaysRange) dr
  GROUP BY dr.DaysRange
  ORDER BY dr.DaysRange


  One more question, how do you display the rows (6-10 days) even if the conditions are not met. I would like to display all the rows and have default value set to 0. Currently it will only show rows when condition is met in case statement. Please advise

  Thanks!

  Try this:

  
WITH RANGES AS (

      SELECT '0-5 days' AS DAYS_RANGE, 1 AS SORT_VALUE, 0 AS RANGE_LOW, 6 AS RANGE_HIGH
    UNION ALL
    SELECT '6-10 days', 2, 6, 10
    UNION ALL
    SELECT '11-15 days', 3, 10, 15
    UNION ALL
    SELECT 'over 15 days', 4, 15, 2147483647
),
    RAW_DATA AS (

          SELECT DATEDIFF(day, TicektStartDate, GETDATE()) AS OpenDays
        FROM dbo.Ticket
        WHERE TicketCloseDate IS NULL
)
SELECT R.DAYS_RANGE, ISNULL(COUNT(*), 0) AS [Count]
FROM RANGES AS R
    LEFT OUTER JOIN RAW_DATA AS RD
        ON RD.OpenDays >= R.RANGE_LOW
        AND RD.OpenDays < R.RANGE_HIGH
GROUP BY R.DAYS_RANGE
ORDER BY R.SORT_VALUE;


  it gave an aggregate error but when I added the column names in the GROUP BY then it "partially" worked. I noticed it didn't give accurate readings when there is no data in the Ticket table. All the rows (0-5, 6-10..etc) returned 1 instead of 0.

  Thank you for your help, we are pretty close

  I think I see my error...  here's attempt #2:

  
WITH RANGES AS (

    SELECT '0-5 days' AS DAYS_RANGE, 1 AS SORT_VALUE, 0 AS RANGE_LOW, 6 AS RANGE_HIGH
  UNION ALL
  SELECT '6-10 days', 2, 6, 10
  UNION ALL
  SELECT '11-15 days', 3, 10, 15
  UNION ALL
  SELECT 'over 15 days', 4, 15, 2147483647
),
  RAW_DATA AS (

     SELECT DATEDIFF(day, TicektStartDate, GETDATE()) AS OpenDays
   FROM dbo.Ticket
   WHERE TicketCloseDate IS NULL
)
SELECT R.DAYS_RANGE, ISNULL(COUNT(RD.OpenDays), 0) AS [Count]
FROM RANGES AS R
  LEFT OUTER JOIN RAW_DATA AS RD
   ON RD.OpenDays >= R.RANGE_LOW
   AND RD.OpenDays < R.RANGE_HIGH
GROUP BY R.DAYS_RANGE
ORDER BY R.SORT_VALUE;


  By counting *, I was looking at the group row count, and what I needed was a count of only records in the RAW_DATA table.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• shezi

  SSCommitted

  Points: 1514

  More actions

  February 16, 2017 at 1:37 pm

  #1928963

  sgmunson - Thursday, February 16, 2017 11:20 AM

  shezi - Thursday, February 16, 2017 10:55 AM

  sgmunson - Wednesday, February 15, 2017 12:18 PM

  shezi - Wednesday, February 15, 2017 11:13 AM

  Chris Harshman - Monday, February 13, 2017 2:53 PM

  ah, sorry, forgot about sorting, to get the numbers to sort right would have to use an extra space in front of the 0-5 and 6-10:
  
SELECT dr.DaysRange, COUNT(*) AS [Count]
  FROM
    (SELECT DATEDIFF(day, TicektStartDate, GetDate()) AS OpenDays FROM dbo.Ticket WHERE TicketCloseDate IS NULL) ot
    CROSS APPLY
        (SELECT CASE
           WHEN ot.OpenDays <= 5 THEN ' 0-5 days'
           WHEN ot.OpenDays BETWEEN 6 AND 10 THEN ' 6-10 days'
           WHEN ot.OpenDays BETWEEN 11 AND 15 THEN '11-15 days'
           WHEN ot.OpenDays > 15 THEN 'over 15 days' END AS DaysRange) dr
  GROUP BY dr.DaysRange
  ORDER BY dr.DaysRange


  One more question, how do you display the rows (6-10 days) even if the conditions are not met. I would like to display all the rows and have default value set to 0. Currently it will only show rows when condition is met in case statement. Please advise

  Thanks!

  Try this:

  
WITH RANGES AS (

      SELECT '0-5 days' AS DAYS_RANGE, 1 AS SORT_VALUE, 0 AS RANGE_LOW, 6 AS RANGE_HIGH
    UNION ALL
    SELECT '6-10 days', 2, 6, 10
    UNION ALL
    SELECT '11-15 days', 3, 10, 15
    UNION ALL
    SELECT 'over 15 days', 4, 15, 2147483647
),
    RAW_DATA AS (

          SELECT DATEDIFF(day, TicektStartDate, GETDATE()) AS OpenDays
        FROM dbo.Ticket
        WHERE TicketCloseDate IS NULL
)
SELECT R.DAYS_RANGE, ISNULL(COUNT(*), 0) AS [Count]
FROM RANGES AS R
    LEFT OUTER JOIN RAW_DATA AS RD
        ON RD.OpenDays >= R.RANGE_LOW
        AND RD.OpenDays < R.RANGE_HIGH
GROUP BY R.DAYS_RANGE
ORDER BY R.SORT_VALUE;


  it gave an aggregate error but when I added the column names in the GROUP BY then it "partially" worked. I noticed it didn't give accurate readings when there is no data in the Ticket table. All the rows (0-5, 6-10..etc) returned 1 instead of 0.

  Thank you for your help, we are pretty close

  I think I see my error...  here's attempt #2:

  
WITH RANGES AS (

    SELECT '0-5 days' AS DAYS_RANGE, 1 AS SORT_VALUE, 0 AS RANGE_LOW, 6 AS RANGE_HIGH
  UNION ALL
  SELECT '6-10 days', 2, 6, 10
  UNION ALL
  SELECT '11-15 days', 3, 10, 15
  UNION ALL
  SELECT 'over 15 days', 4, 15, 2147483647
),
  RAW_DATA AS (

     SELECT DATEDIFF(day, TicektStartDate, GETDATE()) AS OpenDays
   FROM dbo.Ticket
   WHERE TicketCloseDate IS NULL
)
SELECT R.DAYS_RANGE, ISNULL(COUNT(RD.OpenDays), 0) AS [Count]
FROM RANGES AS R
  LEFT OUTER JOIN RAW_DATA AS RD
   ON RD.OpenDays >= R.RANGE_LOW
   AND RD.OpenDays < R.RANGE_HIGH
GROUP BY R.DAYS_RANGE
ORDER BY R.SORT_VALUE;


  By counting *, I was looking at the group row count, and what I needed was a count of only records in the RAW_DATA table.

  Perfecto! 

  Thank you and God Bless