Date Differences & CASE - 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

General SQL Server Forums

New to SQL Server Programming

Date Differences & CASE

Author

Topic

sampcuk
Starting Member

32 Posts

Posted - 2008-06-03 : 10:52:13

I have written the SQL below and need to change the case. I need to say that if today’s date (get date) is between <> 4 weeks of sv_latest_appraisal then = Outstanding. Can this be done in SQL? I know it is very specific so not sure. Please help! Sam

SELECT Employee.FORENAME AS Forename,
Employee.SURNAME AS Surname,
Employee.LOCATION AS Location,
Employee.DEPARTMENT AS Department,
Employee.STARTDATE AS Startdate,
Sv_latest_appraisal.NEXT_APP AS Next_app,
Sv_latest_appraisal.USR_EAR_TYPE AS Usr_ear_type,
Sv_latest_appraisal.USR_EAR_TYPE_NEW AS Usr_ear_type_new,
CASE
WHEN Sv_latest_appraisal.NEXT_APP <= getdate() THEN 'OVERDUE'
WHEN Sv_latest_appraisal.NEXT_APP >= getdate() THEN 'NOT DUE'
WHEN Sv_latest_appraisal.NEXT_APP = getdate() THEN 'DUE TODAY'
ELSE 'UNKNOWN'
END
FROM (dbo.EMPLOYEE AS Employee
INNER JOIN dbo.SV_latest_appraisal
ON Employee.EMPLOY_REF = Sv_latest_appraisal.EMPLOY_REF)
INNER JOIN dbo.JOB AS Job
ON Employee.JOB_REF = Job.JOB_REF
WHERE (Employee.LEAVER = 0)AND (Employee.LOCATION LIKE 'GE')

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-06-03 : 11:37:18

>>is between <> 4 weeks of

not sure what you mean by that but it sounds like you need to incorporate a DATEADD function ie:
dateadd(week, 4, sv_latest_appraisal)

check out DATETIME functions in Books Online

Be One with the Optimizer
TG

sampcuk
Starting Member

32 Posts

Posted - 2008-06-03 : 11:49:29

so by using the DATEADD function I am adding 4 weeks on to the date? I basically want to say that if todays date is within 4 weeks of the appraisal date then it is just outstanding. Then I will add another clause to state if todays date is greater than 4 weeks after the appraisal date then it is overdue....

Thanks so much for any help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-03 : 12:38:04

may be this is what you want:-
CASE WHEN ABS(DATEDIFF(wk,Sv_latest_appraisal.NEXT_APP,getdate()))=4 THEN 'OUTSTANDING'
....

sampcuk
Starting Member

32 Posts

Posted - 2008-06-04 : 06:43:36

This is great thank you. Another question. Can i add in less than and more than operators? I have tried adding them in before the 4 but I am getting an error.... Thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-04 : 06:48:16

quote:
Originally posted by sampcuk

This is great thank you. Another question. Can i add in less than and more than operators? I have tried adding them in before the 4 but I am getting an error.... Thanks again

yup you can use <,>,=,...

sampcuk
Starting Member

32 Posts

Posted - 2008-06-04 : 07:37:58

Sorry to be a complete pain but I am still having difficulties. What I need the query to bring back is as follows:
1. Appraisal dates that fall within the next 4 weeks from todays date then 'DUE'
2. If todays date is greater than 4 weeks from the appraisal due date then 'OVERDUE'
3. If the appraisal is due over 4 weeks after todays date then 'NOT DUE'
4. If the appraisal was due on todays date but is within 4 weeks then is 'OUTSTANDING'

Are you able to help using my script I posted? Once again thank you.

sampcuk
Starting Member

32 Posts

Posted - 2008-06-04 : 07:41:56

Sorry I meant to say for OUTSTANDING, If todays date is within 4 weeks of the due appraisal date (greater than) then OUTSTANDING.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-04 : 07:51:22

quote:
Originally posted by sampcuk

Sorry to be a complete pain but I am still having difficulties. What I need the query to bring back is as follows:
1. Appraisal dates that fall within the next 4 weeks from todays date then 'DUE'
2. If todays date is greater than 4 weeks from the appraisal due date then 'OVERDUE'
3. If the appraisal is due over 4 weeks after todays date then 'NOT DUE'
4. If the appraisal was due on todays date but is within 4 weeks then is 'OUTSTANDING'

Are you able to help using my script I posted? Once again thank you.

CASE WHEN DATEDIFF(wk,Sv_latest_appraisal.NEXT_APP,getdate())=4 THEN 'DUE'
 WHEN DATEDIFF(wk,Sv_latest_appraisal.NEXT_APP,getdate())>4 THEN 'OVERDUE'
WHEN DATEDIFF(wk,getdate(),Sv_latest_appraisal.NEXT_APP)>4 THEN 'NOT DUE'
WHEN DATEDIFF(wk,getdate(),Sv_latest_appraisal.NEXT_APP)<=4 THEN 'OUTSTANDING'
END
...

sampcuk
Starting Member

32 Posts

Posted - 2008-06-04 : 08:11:44

Brilliant... apart from.....

The OUTSTANDING and DUE are incorrect. They are not coming out as they should be.

Outstanding basically means that the appraisal date has passed but todays date is within 4 weeks of it. for example someone whos appraisal was due on 6th May 2008 should be shown as outstanding.

DUE means that the appraisal is due within the next 4 weeks of todays date. For example someone whos appraisal is due in 2-4 weeks time would show as due.

I really appreciate your help and have spent hours trying to get this right on my own...
Sam :-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-04 : 08:22:30

CASE WHEN DATEDIFF(wk,getdate(),Sv_latest_appraisal.NEXT_APP)<=4 THEN 'DUE'
WHEN DATEDIFF(wk,Sv_latest_appraisal.NEXT_APP,getdate())>4 THEN 'OVERDUE'
WHEN DATEDIFF(wk,getdate(),Sv_latest_appraisal.NEXT_APP)>4 THEN 'NOT DUE'
WHEN DATEDIFF(wk,Sv_latest_appraisal.NEXT_APP,getdate())<=4 THEN 'OUTSTANDING'
END

sampcuk
Starting Member

32 Posts

Posted - 2008-06-04 : 08:31:19

sorry but this now means that anything overdue is coming up as due?! The outstandings are also showing as due.... Eeek!!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-06-04 : 11:09:46

sampcuk -- Just change the order of the case conditions so that "Due" is the last one.

After all this and going back and forth, you should be starting to get pretty familiar with how CASE works. Don't focus on just cutting and pasting the solutions, take some time to study them and learn how they are working and see what you can do to alter them or adjustment to suit your needs or requirements.

- Jeff
http://weblogs.sqlteam.com/JeffS

sampcuk
Starting Member

32 Posts

Posted - 2008-06-04 : 11:12:43

I can assure you I have been at this for two days now!!! I am not asking for someone to do the work for me...
Thanks for the advise on the case order

sampcuk
Starting Member

32 Posts

Posted - 2008-06-04 : 11:31:49

I have changed the order of the case which hasnt had a positive effect. I still have appraisals which are due in the future being classed as OUTSTANDING and NOT DUE.
I am on a deadline for this report and really need some guidance. I have trawled through online books and have an SQL book!
SELECT Employee_ge.SURNAME AS Surname,
Employee_ge.FORENAME AS Forename,
Employee_ge.STARTDATE AS Startdate,
Employee_ge.LEAVER AS Leaver,
Employee_ge.DEPARTMENT AS Department,
Employee_ge.COSTCENTRE AS Costcentre,
Sv_latest_appraisal.NEXT_APP AS Next_app,
Sv_latest_appraisal.USR_EAR_TYPE AS Usr_ear_type,
Sv_latest_appraisal.USR_EAR_TYPE_NEW AS Usr_ear_type_new,
CASE
WHEN DATEDIFF(wk,Sv_latest_appraisal.NEXT_APP,getdate())>4 THEN 'OVERDUE'
WHEN DATEDIFF(wk,getdate(),Sv_latest_appraisal.NEXT_APP)>4 THEN 'NOT DUE'
WHEN DATEDIFF(wk,Sv_latest_appraisal.NEXT_APP,getdate())<=4 THEN 'OUTSTANDING'
WHEN DATEDIFF(wk,getdate(),Sv_latest_appraisal.NEXT_APP)<=4 THEN 'DUE'
ELSE 'UNKNOWN'
END
FROM dbo.SV_LATEST_APPRAISAL AS Sv_latest_appraisal
INNER JOIN dbo.EMPLOYEE_GE AS Employee_ge
ON Sv_latest_appraisal.EMPLOY_REF = Employee_ge.EMPLOY_REF
WHERE (((Employee_ge.LEAVER LIKE 0)))

sampcuk
Starting Member

32 Posts

Posted - 2008-06-05 : 04:09:50

Any ideas on this anyone? Appreciate ANY assistance, worked on this last night and still no joy....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-05 : 04:23:02

quote:
Originally posted by sampcuk

Any ideas on this anyone? Appreciate ANY assistance, worked on this last night and still no joy....

Can you provide some sample data and then your expected o/p out of them May be that will make the probelm more clear. May be 5 to 10 rows

sampcuk
Starting Member

32 Posts

Posted - 2008-06-05 : 09:53:00

Hi, Please find below - Does this help?

FORENAME SURNAME Department Start Date Next Appraisal Appraisal Type CUSTOM
Fred Bloggs Front Office 04/08/2003 20/06/2008 3 Month OUTSTANDING (This is incorrect - Should be Due)
Sam Powell Banqueting 23/11/2003 13/06/2008 Main OUTSTANDING (This is incorrect - Should be Due)
Sarah Currier Accounts 24/08/2004 21/05/2008 Interim OUTSTANDING Correct
Laura Martin Spa 24/01/2006 21/08/2008 Main NOT DUE Correct
Hayley Brown Restaurant 26/01/2008 26/03/2008 3 Month OVERDUE Correct

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2008-06-05 : 15:36:15

Sampcuk,

With my limited knowledge & perspective, here is what I came up with. Please dont mind if this didnt work.

regards,

Anil Kumar.

select surname, forename, startdate, leaver, department, costcentre,
next_app, usr_ear_type, usr_ear_type_new,
AnotherColumnThatYouWant =
case when daydiff <= -28 then 'NOT DUE'
case when daydiff < 28 then 'DUE'
case when daydiff > 28 then 'Overdue'
case when (daydiff < 28 and OUTSTANDINGCOLUMN = 0) then 'OUTSTANDING'
Else 'UNKNOWN'
End
from
(SELECT Employee_ge.SURNAME AS Surname,
Employee_ge.FORENAME AS Forename,
Employee_ge.STARTDATE AS Startdate,
Employee_ge.LEAVER AS Leaver,
Employee_ge.DEPARTMENT AS Department,
Employee_ge.COSTCENTRE AS Costcentre,
Sv_latest_appraisal.NEXT_APP AS Next_app,
Sv_latest_appraisal.USR_EAR_TYPE AS Usr_ear_type,
Sv_latest_appraisal.USR_EAR_TYPE_NEW AS Usr_ear_type_new,
datediff(day, Sv_latest_appraisal.NEXT_APP, dateadd(week,4,getdate()))as DayDiff,
datediff(week, Sv_latest_appraisal.NEXT_APP, dateadd(week,4,getdate()))as WeekDiff,
datediff(day, Sv_latest_appraisal.NEXT_APP, getdate()) as OUTSTANDINGCOLUMN
FROM dbo.SV_LATEST_APPRAISAL AS Sv_latest_appraisal
INNER JOIN dbo.EMPLOYEE_GE AS Employee_ge
ON Sv_latest_appraisal.EMPLOY_REF = Employee_ge.EMPLOY_REF
WHERE (((Employee_ge.LEAVER LIKE 0)))
)tmp

sampcuk
Starting Member

32 Posts

Posted - 2008-06-06 : 03:14:28

Thanks for this but unfortunately does not work. I am dreaming about SQL at the moment!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 04:43:36

quote:
Originally posted by sampcuk

Thanks for this but unfortunately does not work. I am dreaming about SQL at the moment!

One more try

CASE WHEN DATEDIFF(wk,getdate(),Sv_latest_appraisal.NEXT_APP)>0 AND DATEDIFF(wk,getdate(),Sv_latest_appraisal.NEXT_APP)<=4 THEN 'DUE'
WHEN DATEDIFF(wk,Sv_latest_appraisal.NEXT_APP,getdate())>4 THEN 'OVERDUE'
WHEN DATEDIFF(wk,getdate(),Sv_latest_appraisal.NEXT_APP)>4 THEN 'NOT DUE'
WHEN DATEDIFF(wk,Sv_latest_appraisal.NEXT_APP,getdate())>0  AND DATEDIFF(wk,Sv_latest_appraisal.NEXT_APP,getdate())<=4 THEN 'OUTSTANDING'
END

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources