not an option i'm afraid.

this is what i have to work with.

i have done what you suggest though and used a consumable data

the...

• This reply was modified 3 years, 6 months ago by  chenks.

A negative gap is an overlap and a positive gap is a gap.

also, i need those to be 2 distinct seperate values.

i'm not sure either of those will work with SQL 2000?

i know LAG isn't supported. and i don't believe PARTITION is supported in SQL 2000 either?

a working solution has been found thanks to @aaron-n-cutshall

has been tested and works.

declare @month int;
declare @year int;

select @month = 9, @year = 2019;

select d.Diary_Engineer_ID, sum(1-c.isWeekend)...

yes time is not an issue as we are only counting full days, so the two time fields can be ignored, only the date start and date end are the...

guessing that uses another term that SQL2000 doesn't like, as it doesn't seem to like the word "PERSISTED"

but i'll just drop the table and re-create it with the extra column

• This reply was modified 5 years, 5 months ago by  chenks.

many thanks!

just for reference - this is thread explaining what i am trying to achieve

https://www.sqlservercentral.com/forums/topic/count-number-of-days-between-two-dates-excluding-weekends-for-a-particular-month

yeah i know it's obsolete, but it's what i have to work with.

a legacy application uses it and there's no requirement to upgrade. the work involved would greatly outweigh the...