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), W
endSample data for tblTimeOff:-
10.00AM, 15, 0 - Timeoff period 10.00am to 10.15am on W
day12.00pm, 60, 0 - Timeoff period 12.00pm to 1.00pm on W
day15.00pm,15, 0 - - Timeoff period 3.00pm to 3.15pm on W
day10.30AM, 15, 1 - Timeoff period 10.30am to 10.45am on W
end12.30pm, 60, 1 - Timeoff period 12.30pm to 1.30pm on W
endSaturday and Sunday are considered as W
end.Mon - Fri are W
endI 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 (w
end) - 15Condition 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,
mfwooWhere 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@.yahoo.com> wrote in message
news:B108A0E8-4084-435C-9C6E-815AD808C4AB@.microsoft.com...
> 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), W
end> Sample data for tblTimeOff:-
> 10.00AM, 15, 0 - Timeoff period 10.00am to 10.15am on W
day> 12.00pm, 60, 0 - Timeoff period 12.00pm to 1.00pm on W
day> 15.00pm,15, 0 - - Timeoff period 3.00pm to 3.15pm on W
day> 10.30AM, 15, 1 - Timeoff period 10.30am to 10.45am on W
end> 12.30pm, 60, 1 - Timeoff period 12.30pm to 1.30pm on W
end> Saturday and Sunday are considered as W
end.> Mon - Fri are W
end> 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 (w
end) - 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
>sql
No comments:
Post a Comment