Hi,

I need a query that can return the total time off between 2 dates.
I have a table call tblTimeOff which has the following fields
StartTimeOff, Interval (minute), Weekend
Sample data for tblTimeOff:-
10.00AM, 15, 0 - Timeoff period 10.00am to 10.15am on Weekday
12.00pm, 60, 0 - Timeoff period 12.00pm to 1.00pm on Weekday
15.00pm,15, 0 - - Timeoff period 3.00pm to 3.15pm on Weekday
10.30AM, 15, 1 - Timeoff period 10.30am to 10.45am on Weekend
12.30pm, 60, 1 - Timeoff period 12.30pm to 1.30pm on Weekend

Saturday and Sunday are considered as Weekend.
Mon - Fri are Weekend

I need a query when user provide me with 2 date:-
Condition 1:
--------------
Start :- 2nd March 8.30am
End :- 4th March 11.00am
The result for total time off should be:- 195 mins
2nd March - 15+60+15, 3rd March - 15+60+15, 4th March (weekend) - 15

Condition 2:
--------------
Start :- 2nd March 8.30am
End :- 2nd March 5.00pm
The result for total time off should be:- 90 mins
2nd March - 15+60+15

Anyone help ?

Thank You,
mfwoo

Where are your dates stored in your tables?

Posting the full DDL may help.
http://www.aspfaq.com/etiquette.asp?id=5006

"Woo Mun Foong" <mfwoo (AT) yahoo (DOT) com> wrote