Time bucket - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Time bucket

Author

Topic

disha
Starting Member

34 Posts

Posted - 2009-12-17 : 07:35:56

i need to create a period bucket using the start date and saysdate and i want the output as below .

please advise how to achieve this using case statement or is there any other way please suggest the correct syntax

1 day
2 days
3 days
4 days
5 days
6 days
7 days
8D - 1M
2 Months
3 Months
4 Months
5 Months
6 Months
7 Months
8 Months
9 Months
10 Months
11 Months
12 Months
1-2 Years
2-3 Years
3-4 Years
4-5 Years
Beyond 5Ys

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-17 : 07:53:02

Duplicate of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=137309

Madhivanan

Failing to plan is Planning to fail

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-17 : 08:30:21

You will need to create a large CASE statement... (I dont know if you mean a Function or another field you have by "SAYSDATE", you might be trying to get a system time...dunno, just replace where applicable.)

Sample...

Case
WHEN datediff(d,[Start Date], [SYSDATE]) = 1 then '1 Day'
WHEN datediff(d,[Start Date], [SYSDATE]) = 2 then '2 Days'
.....
WHEN datediff(d,[Start Date], [SYSDATE]) >= 8 AND datediff(m,[Start Date], [SYSDATE]) <= 1then '8D - 1M'
WHEN datediff(m,[Start Date], [SYSDATE]) = 2 then '2 Months'
.....
When datediff(y,[Start Date], [SYSDATE]) BETWEEN 1 AND 2 then '1-2 Years'
.....
ELSE 'Beyond 5Ys'
END

EDIT: You May need to add a CASE for things happening on the same day, otherwise they will end up in the 'Beyond 5Ys' group

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources