November 7, 2007 at 2:07 pm
My requirement is following
If Employee Status = (Active or Inactive) Status Date should be Current Dates
If Employee Status = (Terminated) Status Date should be Termination_Date (only last 30 days)
I need to eliminate any termination records over the last 30 days. In this case I don’t need the record from Sep 20. My query is followed by the results. Would appreciate any kind of help.
EMPSTATUS - STATUS_DATE
Inactive , 2007-11-08
Terminate ,2007-09-20
Terminate ,2007-10-26
Active , 2007-11-08
Active , 2007-11-08
select
[EMPLOYEE_STATUS],
CASE WHEN [EMPLOYEE_STATUS]='Active'
THEN (SELECT DATEADD(day, 1, getdate()))
WHEN [EMPLOYEE_STATUS]=’Inactive’
THEN (SELECT DATEADD(day, 1, getdate()))
WHEN [EMPLOYEE_STATUS]='Terminate'
THEN( CONVERT( datetime,cast (hr.terminate_date as varchar ) , 110)) END AS [Status_Date]
from hr
November 7, 2007 at 2:24 pm
I think you might be going overboard unless I misunderstood you
SELECT
*
FROM
hr
WHERE
(CASE WHEN [EMPLOYEE_STATUS] = 'Terminate' AND hr.terminate_date < DATEADD(d,DATEDIFF(d,0,GETDATE()) - 30,0) THEN 0 ELSE 1 END) = 1
November 7, 2007 at 2:28 pm
add that criteria to your where clause by using the datediff function like the following:
WHERE
EMPLOYEE_STATUS <> 'TERMINATE'
OR (EMPLOYEE_STATUS = 'TERMINATE'
and ABS(datediff(dd, terminate_date, getdate())) < 30)
hope this helps.
November 7, 2007 at 2:29 pm
select
* from hr
WHERE
terminate_date >=( CASE WHEN [EMPLOYEE_STATUS]='Terminate' THEN DATEADD(day,-30,GETDATE()) ELSE Terminate_date END)
Prasad Bhogadi
www.inforaise.com
November 7, 2007 at 2:42 pm
Since I'm also converting the status_date into date format from numeric, it throws the error
"Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime."
November 7, 2007 at 2:48 pm
Where do you get error? I did not understand why you are adding one day to GETDATE() function for Active and Inactive Status records.
select
[EMPLOYEE_STATUS],
CASE WHEN [EMPLOYEE_STATUS]='Active'
THEN (SELECT DATEADD(day, 1, getdate()))
WHEN [EMPLOYEE_STATUS]='Inactive'
THEN (SELECT DATEADD(day, 1, getdate()))
WHEN [EMPLOYEE_STATUS]='Terminate'
THEN( CONVERT( datetime,cast (hr.terminate_date as varchar ) , 110)) END AS [Status_Date]
from hr
WHERE
terminate_date >=( CASE WHEN [EMPLOYEE_STATUS]='Terminate' THEN DATEADD(day,-30,GETDATE()) ELSE Terminate_date END)
Prasad Bhogadi
www.inforaise.com
November 7, 2007 at 2:48 pm
What do you mean numeric? your example shows yyyy-mm-dd. What is the format of the data that you must convert from?
November 7, 2007 at 3:02 pm
The datatype of terminate_date is decimal and I convert that to datetime and show as status_date so that I would be able to achieve my requirement but it gives me the same error I got before when I added this statment in the where clause terminate_date >=( CASE WHEN [EMPLOYEE_STATUS]='Terminate' THEN DATEADD(day,-30,GETDATE()) ELSE Terminate_date END)
select
[EMPLOYEE_STATUS],
CASE WHEN [EMPLOYEE_STATUS]='Active'
THEN (SELECT DATEADD(day, 1, getdate()))
WHEN [EMPLOYEE_STATUS]=’Inactive’
THEN (SELECT DATEADD(day, 1, getdate()))
WHEN [EMPLOYEE_STATUS]='Terminate'
THEN( CONVERT( datetime,cast (hr.terminate_date as varchar ) , 110)) END AS [Status_Date]
from hr
November 7, 2007 at 3:06 pm
Sorry can you provide an example of the raw date value as it is stored and if is not 20070109.071400 wheere the date is 01-09-2007 7:14 AM then how does the value you provide work?
November 7, 2007 at 3:13 pm
Raw data looks like as it shows in Terminate_date column, My mission is to show only employees who were terminated within 30 days
EMPSTATUS - STATUS_DATE - TERMINATE_DATE
Inactive , 2007-11-08,0
Terminate ,2007-09-20 ,20070920
Terminate ,2007-10-26 ,20071026
Active , 2007-11-08 ,0
Active , 2007-11-08 ,0
November 7, 2007 at 4:09 pm
OK think I got it now
select
* from hr
WHERE
terminate_date IS NULL OR
cast(cast(TERMINATE_DATE as varchar(8)) as datetime) >= DATEADD(day,-30,GETDATE())
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply