TSQL - Best way to select data where a leave date falls in range of an invoice.

Question

Hi,

Background: I have a payroll system where leave is paid only if it falls in range of the invoice being paid. So if the invoice covers the last 2 weeks then only leave in the last 2 weeks is to paid.

I want to write a sql query to select the leave.

Assume a table called DailyLeaveLedger which has among others a LeaveDate and Paid flag. Assume a table called Invoice that was a WeekEnding field and a NumberWeeksCovered field.

Now assume week ending date 15/05/09 and NumberWeeksCovered = 2 and a LeaveDate of 11/05/09.

This is an example of how I want it written. The actual query is quite complex but I want the LeaveDate check to be a In subquery.

SELECT * 
FROM DailyLeaveLedger 
WHERE Paid = 0 AND
      LeaveDate IN (SELECT etc...What should this be to do this)

Not sure if its possible the way I mention?

Malcolm

Answer 1

So LeaveDate should be between (WeekEnding-NoOfWeeksCovered) and (WeekEnding) for some Invoice?

If I've understood it right, you might be able to use an EXISTS() subquery, something like this:

SELECT * 
FROM DailyLeaveLedger dl
WHERE Paid = 0 AND
      EXISTS (SELECT *
              FROM Invoice i
              WHERE DateAdd(week,-i.NumberOfWeeksCovered,i.WeekEnding) < dl.LeaveDate 
              AND i.WeekEnding > dl.LeaveDate
              /* and an extra clause in here to make sure
              the invoice is for the same person as the dailyleaveledger row */
              )