QUESTION POSED ON: 26 April 2002
I have a table containing following Columns plus other columns:
Start Date 04-01-2002(mm-dd-yyyy) varchar(10)
Start Time column 09:30:30(hh:mm:ss) varchar(8)
End Date 04-05-2002(mm-dd-yyyy) varchar(10)
End Time column 09:45:30(hh:mm:ss) varchar(8)
1. We want to create a view for report users, so that they could just select
the other columns and difference in start Date/time to End Date/Time.
2. The difference in time should be based on business day. i.e.
Saturday & Sunday should be excluded.
3. Each business day is defined as 8:00 AM to 5:00 PM
(1 business day = 9 hrs)
4. The time is stored as a 24-hour clock.
For example:
Start Date Start Time End Date End Time Desired Output
04/01/2002 10:00:00 04/01/2002 3:30:00 0 Days 5:30 Hrs
04/01/2002 16:00:00 04/02/2002 10:45:00 0 Days 3:45 Hrs
04/01/2002 09:00:00 04/03/2002 10:45:00 2 Days 1:45 Hrs
04/05/2002 16:30:00 04/08/2002 9:45:00 0 Days 2:15 Hrs*
*In last case as 04/06/2002 & 04/07/2002 were Saturday & Sunday, hence
excluded. So the difference of time is just 2:15 hrs.
Can you please give me a suggestion/pseudo code/logic on how I might do this?
We cannot use cursors as we want to make a view.
|
