December 8, 2004 at 9:14 am
Hi All.
Can some one help me construct appropriate T-SQL for the following problem (SQLServer 2000).
I have a contacts table with a contactDesc, DueDate and DoneDate I want to retrieve the description and an appropriate computer color for all rows.
The formula I need to use is: -
If DoneDate is null and todays date is >= DueDate color 1.
If DoneDate is null and todays date is within 7 days of DueDate color 2.
If DoneDate is null and todays date is within 8 - 14 days of DueDate color 3.
If DoneDate is null and todays date is more than 14 days away from DueDate color 4.
If DoneDate has a value then colour = 5.
I suspect I need a nested case but cannot get it quite right. Thanks in advance.
CCB
December 8, 2004 at 9:27 am
Select case
when DoneDate is null and GetDate() > DueDate then 1
when DoneDate is null and DateDiff(d, DueDate, GetDate()) 14 then 4
when Not DoneDate is null then 5
else 99 --in case we forgot a combinaison
end as Color
from dbo.contacts
This is what you asked for, however I think that the case statement will more often than not fall under the first if the duedate has passed, so I would change the first when to this:
when DoneDate is null and DateDiff(d, DueDate, GetDate()) = 1 then 1 (late by only 1 day)... but this is my interpretation of your requirements
December 8, 2004 at 9:43 am
Thanks, thats perfect. I also took your advice at the botom of the message.
Thanks again.
CCB
December 9, 2004 at 8:11 am
I guess the below query will be useful for your problem
select contactdesc,
case
when (donedate is null and getdate()>=duedate) then 1
when donedate is not null then 5
when (donedate is null and datediff(d,getdate(),duedate) <=7 ) then 2
when (donedate is null and datediff(d,getdate(),duedate) >14 ) then 4
when (donedate is null and datediff(d,getdate(),duedate) between 8 and 14 ) then 3
else
0
end 'ColourValue'
from dbo.contacts
Thanks
Suresh Ramakrishnan
December 9, 2004 at 11:59 am
Another way would be this and I only suggest it from a readability standpoint.
Also like Suresh I reversed the GetDate() and DUeDate in datediff from Remi's example.
The reason is you will get a -7 instead of positive 7 when date is 7 days away and instead of 14 you will get -14 which means you get the wrong output value.
It is a simple mistake but I think you will want to correct that eitherway before you put in place.
CASE
WHEN DoneDate IS NOT NULL THEN 5
ELSE -- No need to check DoneDate has to be null in this case.
CASE
WHEN GetDate() >= DueDate THEN 1
WHEN DateDiff(d, GetDate(), DueDate) <= 7 THEN 2
WHEN DateDiff(d, GetDate(), DueDate) between 8 and 14 THEN 3
WHEN DateDiff(d, GetDate(), DueDate) > 14 THEN 4
END
END
December 9, 2004 at 12:27 pm
Thanx for the correction Antares.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply