Have Geek Brain

TSQL is the best way to select data where the vacation date falls within the invoice range

Reference Information. I have a payroll system where leave is paid only if it falls into the bill pay area. Thus, if the invoice covers the last 2 weeks, then only for the last 2 weeks you pay.

I want to write sql query for vacation selection.

Suppose the table is named DailyLeaveLedger

, among which there is a flag LeaveDate

and Paid

. Suppose the table with the name Invoice

was a field WeekEnding

and a field NumberWeeksCovered

.

Now suppose the end date of the week is 15/05/09 and NumberWeeksCovered

= 2 and a is LeaveDate

from 11/05/09.

This is an example of how I want this to be written. The actual query is quite complex, but I want the validation to LeaveDate

be an In subquery.

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

Not sure if this is possible as I mention?

Malcolm

+1


a source to share


1 answer


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

If I got it right, you could use the 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 */
              )
+3


a source






More articles:


All Articles