Date Difference Case Statement

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 2000 Forums

Transact-SQL (2000)

Date Difference Case Statement

Author

Topic

pe1826
Starting Member

6 Posts

Posted - 2006-09-22 : 06:02:37

Hi,

I hope that somebody maybe able to help; I have a table with a column named ExpiresOn, this is a date field in the format of dd/mm/yyyy. I would like to create a query/view with a CASE statement that creates a new column with the following calculated entries:
ACTIVE: Where the ExpiresOn value is equal to or greater than current date.
LAPSED: Where the ExpiresOn value is within the past 6 months of the current date.
REMOVE: Where the ExpireOn value is greater than 6 months ago.

I have managed to get the ACTIVE option to work oK using
CASE WHEN ExpiresOn >= DATEADD([day] , DATEDIFF([day] , 0 , GETDATE()) , 0) THEN 'ACTIVE'
But I just cannot seem to get to grips with the other two options LAPSED and REMOVED

Any help would be greatly recieved.

Paul

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-22 : 06:08:24

[code]
select case when ExpiresOn >= dateadd(day, datediff(day, 0, getdate()), 0) then 'ACTIVE'
when ExpiresOn >= dateadd(month, -6, dateadd(day, datediff(day, 0, getdate()), 0))
and ExpiresOn < dateadd(day, datediff(day, 0, getdate()), 0) then 'LAPSED'
when ExpiresOn < dateadd(month, -6, dateadd(day, datediff(day, 0, getdate()), 0)) then 'REMOVE'
[/code]

pe1826
Starting Member

6 Posts

Posted - 2006-09-22 : 07:59:08

Hi KH,

That worked a treat thank you, I didnt give it a thought to use the AND function

Paul

Originally posted by pe1826

Hi,

I hope that somebody maybe able to help; I have a table with a column named ExpiresOn, this is a date field in the format of dd/mm/yyyy. I would like to create a query/view with a CASE statement that creates a new column with the following calculated entries:
ACTIVE: Where the ExpiresOn value is equal to or greater than current date.
LAPSED: Where the ExpiresOn value is within the past 6 months of the current date.
REMOVE: Where the ExpireOn value is greater than 6 months ago.

I have managed to get the ACTIVE option to work oK using
CASE WHEN ExpiresOn >= DATEADD([day] , DATEDIFF([day] , 0 , GETDATE()) , 0) THEN 'ACTIVE'
But I just cannot seem to get to grips with the other two options LAPSED and REMOVED

Any help would be greatly recieved.

Paul
[/quote]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources