Group by Days Range

[Zurich98]

In Excel, there is a grouping function where you can enter a starting value, an end value and indicate increment by to group the data. For example,
starting at = 0, End at = 196, By: 7 so the data is group as following:

Row_Label Count
0-6 50(between 0 and 6 days, we have 50
7-13 21(between 7 and 13 days, we have 21
14-20 33
21-27 15
... ...
175 - 181 35

can I accomplish this grouping methodology in SQL? Basically, i have a TicketOpenDate (datetime data type) in a table. I calculate the # of days outstanding between the TicketOpenDate an getdate(). Now I want to group by the above scenario. Your help/suggestion is greatly appreciated.

Thanks

 

[RiverGuy]

Use a CASE statement. For example:

SELECT *,
DaysOutstanding =
CASE
  WHEN SomeColumn >= 175 THEN '175+'
  WHEN SomeColumn >= 100 THEN '100-174'
  WHEN SomeColumn >= 50 THEN '50-99'
  --etc...
END

 

[gmmastros]

I would encourage you to create another table in your database. This new table would store your ranges so that you can easily configure the ranges returned by your query.

In the sample code I show, I am hardcoding data in to a table variable called @Tickets. This would represent some sample data that you may have in your table. I create another table variable called @OpenTicketRange that would represent the new table I am suggesting that you create.

[COLOR=blue]Declare[/color] @Tickets [COLOR=blue]Table[/color](TicketId [COLOR=blue]Int[/color], TicketOpenDate [COLOR=#FF00FF]DateTime[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Tickets [COLOR=blue]Values[/color](1, [COLOR=red]'20080518'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Tickets [COLOR=blue]Values[/color](1, [COLOR=red]'20080501'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Tickets [COLOR=blue]Values[/color](1, [COLOR=red]'20080418'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Tickets [COLOR=blue]Values[/color](1, [COLOR=red]'20071128'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Tickets [COLOR=blue]Values[/color](1, [COLOR=red]'20071118'[/color])

[COLOR=blue]Declare[/color] @OpenTicketRange [COLOR=blue]Table[/color](MinDays [COLOR=blue]Int[/color], MaxDays [COLOR=blue]Int[/color], Label [COLOR=blue]VarChar[/color](20))
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @OpenTicketRange [COLOR=blue]Values[/color](0,  6,  [COLOR=red]'0 - 6'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @OpenTicketRange [COLOR=blue]Values[/color](7,  13, [COLOR=red]'7 - 13'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @OpenTicketRange [COLOR=blue]Values[/color](14, 20, [COLOR=red]'14 - 20'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @OpenTicketRange [COLOR=blue]Values[/color](21, 27, [COLOR=red]'21 - 27'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @OpenTicketRange [COLOR=blue]Values[/color](28, 174,[COLOR=red]'28 - 174'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @OpenTicketRange [COLOR=blue]Values[/color](175,181,[COLOR=red]'175 - 181'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @OpenTicketRange [COLOR=blue]Values[/color](182,1000000, [COLOR=red]'More than 182'[/color])

[COLOR=blue]Select[/color] OpenTicketRange.Label, [COLOR=#FF00FF]Count[/color](*) [COLOR=blue]As[/color] TheCount
[COLOR=blue]From[/color]   @Tickets Tickets
       [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] @OpenTicketRange OpenTicketRange
         [COLOR=blue]On[/color] [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Day[/color], TicketOpenDate, [COLOR=#FF00FF]GetDate[/color]()) Between OpenTicketRange.MinDays And OpenTicketRange.MaxDays
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] OpenTicketRange.Label

Play around with the data for @OpenTicketRange, and you will see that it is very easy to modify the results of the query by simply adding, removing or updating data in this table.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Zurich98]

Thank you both for the help/input. I was hoping there is a function in SQL similar to the one available in Excel. So I will either have to use case statement and hardcoded the range or create a temp table to hold the range data and join back to my result set. Will be lot of fun for me. Again, thank you for the excellent ideas.

 

[PDreyer]

Maybe:
Select 'from'=datediff(dd,TicketOpenDate,getdate())/7
, 'to'=datediff(dd,TicketOpenDate,getdate())/7+6
, 'TheCount'=Count(*)
From Tickets
group by datediff(dd,TicketOpenDate,getdate())/7
, datediff(dd,TicketOpenDate,getdate())/7+6