I could use some expert advise on how to tackle this problem...:)

 

I'm creating a report that includes active and terminated employees. The terminated employees need to drop off the file after X number of days. I was going to include terms for a 14 day look back period, then drop them from the file.

 

EEstatus = 'Terminated'   and    
EEtermdate between (currentdate - 14 days) and Getdate()

 

where the start is dynamically generated using the currentdate, accounting for crossing into a prior month scenarios. 

 

 

 

 

I tried the following in the SQL statement, but I'm getting a error of

"invalid parameter 1 specified for datediff"

 

Any suggestions???

 

 

FROM
    EBASE    
inner join EEmploy on EBflxid = EEflxideb AND     
(EEdatebeg <= GetDate() and EEdateend >= GetDate() or EEdateend is null) AND     
(EEstatus <> 'Terminated' or EEstatus = 'Terminated' and (EETermdate = datediff('d',EEmploy.EEtermdate,currentdate) in 0 to 14))

inner join EJob on EBflxid = EJflxideb   and
(EJdatebeg <= GetDate() and  
(EJdateend > = GetDate() or EJdateend is null) AND 
(EEstatus <> 'Terminated' or EEmploy.EEstatus = 'Terminated' and EJDATEEND = EETERMDATE)) 
WHERE
    EBASE.EBFLAGEMP = 'Y'

 

I was trying to retrieve the correct records with the SQL statement, not using an additional Select....but I'm not opposed to that approach.