Grouping By the Number of Days

Question

Grouping By the Number of Days

dan minor

Grasshopper

Points: 24
More actions
November 18, 2008 at 7:31 am

#201454

Hi,
I have a field that contains the number of days between a dated field and today (utilizing datediff). I now need to group this field (called Days) in a view or stored procedure by the number of days within each group. One group would be < 31 the next group would be between 31-60 days - Next 61-90 days and so on.
The issue is that I do not know how to get this done. Any suggestions would be greatly appreciated.
Thanks in advance for your assistance
Dan

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

You can use case statement to check the amount of days that passed and then use the same case statement in the group by clause. Here is an example:

--creating the demo table

create table demo (i int not null identity(1,1), Days int not null)

go

--Insert the demo data

insert into demo (days)

select 3 union select 10 union select 34 union select 90

go

--Using case with group by

select count(*), GroupName = case when days>=1 and days <=31 then '1 - 31'

when days >= 32 and days <=63 then '32 - 63'

when days >= 64 then 'More then 63 days' end

from demo

group by case when days>=1 and days <=31 then '1 - 31'

when days >= 32 and days <=63 then '32 - 63'

when days >= 64 then 'More then 63 days' end

go

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Mark Cowne One Orange Chip Points: 26984 More actions · Answer 2

You could join to a table of ranges

create table Ranges(RangeStart int, RangeEnd int, RangeDesc varchar(20))

insert into Ranges(RangeStart , RangeEnd , RangeDesc )

select 0,30,'<31' union all

select 31,60,'31-60' union all

select 61,90,'61-90'

select r.RangeDesc,count(*)

from mytable t

inner join Ranges r on t.Days between r.RangeStart and r.RangeEnd

group by r.RangeDesc

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

dan minor Grasshopper Points: 24 More actions · Answer 3

Mark and Adi,

Both of your ideas worked like a charm. Thanks so much for your help. I really appreciate it.

Dan 🙂

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 4

Adi... the link in your signature line is broken... it should be...

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Notice... no trailing square bracket.

And, thanks for including it. Wish we could get everyone to do that...

--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)

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 5

Thank you for notifying me. Just fixed it.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/