Janine

Janine

  • NA
  • 8
  • 41.5k

Date Comparison in Query

May 31 2011 10:29 AM
Hi, I am wanting to have a report run based on the output of a sql query (SQL server 2005)
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

Answers (1)