Query help? Return the date which is n business days before d (from a table with all days in it)

Hi all,

I've got a table with columns:

DATE (date type; contains all of the actual calendar days) IS_WORKDAY (0 or 1, depending on whether the DATE in that row is a work day)

What I want:

Suppose today is 1/20/2007. I want to know how many ACTUAL days back = 5 BUSINESS days back. For this example, the answer would be 6 (the 15th was apparently a holiday, according to our table).

Another example: If today is a Monday, and I want the ACTUAL # of days corresponding to 1 BUSINESS day ago, three would be the answer (assuming Friday was a work day).

What query will answer this question for me in general? A co-worker suggested the LAG function, but didn't have time to explain it to me in greater detail (and I don't totally follow the online documentation).

This is going to be responding to a c# client, so hopefully an answer won't involve a lot of round-tripping.

Thanks for any assistance,

cdj Received on Wed Mar 14 2007 - 19:45:19 CDT