The difference between two dates is one thing and that can be calcualted per row but it appears you are trying to do more than that. I want to clarify the request - you are trying to count the number of events in the holiday table that occurred in between the two dates and there could be multiple events (Christmas Eve, Christmas, New Years Eve, New Years = 4) for a range of 12/20 to 1/4. Is that right?
Considering the data, I think you would need a row in that first table for every single day between start and end to match on but maybe a few brighter minds than mine might have an idea.
Rody Zakovich - I always think of you and Date tricks.. any other ideas come to mind? Willy - if you can clarify that I understood the issue correctly, that would help too.
Essentially you're right. I need to find out how many holiday (Taken from holiday table that I already have) between two date. Please note that I already include the weekend in the holiday table (hoping that this make things easier)