The just of what I am trying to do for a pay date field is as follows:
The data reported in the Pay Date column should be the first day of the pay period, the first time an employee receives a paycheck or the first pay after being terminated for 6 months
The first time an employee recieves a pay check would be Min(pay.CheckDate), But I am not sure how to apply the check for the first pay check after an employee was terminated for 6 months
I thought of something like this:
Case
I am not sure if the logic after the THEN is correct THEN MIN(Payroll.CheckDate), I think this will just return the first checkDate ever, and not really the first checkDate after 6 month's termination period.
Any help?
The Table structure for this is basically an Emp table with Emp info, hire date, termination Date and so on... Pay table stores info about Pay, Check Date, Amount.... for employees.
when Pay.CheckDate >= DATEADD(Month,6, EMP.TerminationDate)THEN MIN(Payroll.CheckDate) else MIN(Payroll.CheckDate) end as FirstCheckDate
Posted May 31, 2011, 10:39 AM
First take all the records those employees are not terminated.
select min(checkDate) from empl where EMP.TerminationDate is null //
In the second query,
Select the terminated employee records.
select min(checkDate) from empl where Pay.CheckDate >= DATEADD(Month,6, EMP.TerminationDate)//
Union
select min(checkDate),emp -- whatever info you want from empl where EMP.TerminationDate is null //do the group by accordingly
union
select min(checkDate) from empl where Pay.CheckDate >= DATEADD(Month,6, EMP.TerminationDate)
In the above query, please select the proper columns and do the group by also.
Now, do the union of the above two output then I guess you will get the desired output.
Anyway I will think some other alternatives for this...