Make Group Segments on Dates in SQL

[Hacktastic]

Trying to make some groupings on a date field in a query like this :

case when (CAST(FLOOR(CAST(date AS FLOAT)) AS DATETIME)) >= getdate()-8 then '7 days'
when (CAST(FLOOR(CAST(date AS FLOAT)) AS DATETIME)) >= getdate()-15 then '14 days'
when (CAST(FLOOR(CAST(date AS FLOAT)) AS DATETIME)) >= getdate()-31 then '30 days'
else '45 days' end

but it doesn't produce the results i need,

namely i want to have a selection of 7 days ago, 14 days ago, 30 days ago and 45 days ago.

so the 14 days ago population should also include the folks from 7 days ago...

This is going to be used as a filter once i pass the data into excel.

Thanks!

 

[markros]

You may want to use

datediff(day, date, getdate()) <=7 then '7 days' etc.

 

[Hacktastic]

Sorry,
this doesnt work.

 

[bborissov]

Could you provide some example data and desired result from it?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

For each row in your results, there can be only 1 output from the case when. So... the date cannot be "7 days" and "14 days" at the same time. Remember, the output of the case/when statement is going in to a single column of a single row. Basically, the output of the case/when is a scalar value.

What you could do is return several columns, one for each criteria, like this....

[tt][blue]
case when (CAST(FLOOR(CAST(date AS FLOAT)) AS DATETIME)) >= getdate()-8 then '7 days' Else '' End As [7Days],
Case when (CAST(FLOOR(CAST(date AS FLOAT)) AS DATETIME)) >= getdate()-15 then '14 days' Else '' End As [14Days],
Case when (CAST(FLOOR(CAST(date AS FLOAT)) AS DATETIME)) >= getdate()-31 then '30 days' Else '' End As [30Days],
Case When (CAST(FLOOR(CAST(date AS FLOAT)) AS DATETIME)) < getdate()-31 Then '45 days' Else '' end As [45days]
[/blue][/tt]


If you decide to use this method, then you may want to change the output so it returns a boolean (bit) instead of string. Strings take more storage and are slower to work with.

so...

case when (CAST(FLOOR(CAST(date AS FLOAT)) AS DATETIME)) >= getdate()-8 then Convert(Bit, 1) Else Convert(Bit, 0) End As [7Days],
Case when (CAST(FLOOR(CAST(date AS FLOAT)) AS DATETIME)) >= getdate()-15 then Convert(Bit, 1) Else Convert(Bit, 0) End As [14Days],
Case when (CAST(FLOOR(CAST(date AS FLOAT)) AS DATETIME)) >= getdate()-31 then Convert(Bit, 1) Else Convert(Bit, 0) End As [30Days],
Case When (CAST(FLOOR(CAST(date AS FLOAT)) AS DATETIME)) < getdate()-31 Then 'Convert(Bit, 1) Else Convert(Bit, 0) end As [45days]

-George

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

 

[Hacktastic]

you guys are so right..

just talked to my dba here and he told me exactly the same thing...I shouldve known better but thats what happens when u start coding in the middle of the night

Thanks, I'll try to do a work around.

 

[bborissov]

There is a way to do what you want with some UNIONS, but i just want to make sure what you want

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.