Backing Out Durations Between Dates

[Memento]

SQL Server 2000
Windows 2003 Server

Goal: I need to “Back Out” non-billable time from billable time. I really only need a total time for the week. This is for an Hours Report with 40 hours a week being the standard.

Tables:
BillableTime(BT)
StaffID(int), StartTime(datetime), EndTime(datetime), ProcCode(int), Duration(int)

NonBillableTime(NBT)
StaffID(int), StartTime(datetime), EndTime(datetime), ProcCode(int)


Results:
NonBillableTime
StaffID StartTime EndTime ProcCode
269 2006-12-15 08:00:00.000 2006-12-15 12:00:00.000 30
269 2006-12-15 13:00:00.000 2006-12-15 17:00:00.000 30

BillableTime
StaffID StartTime EndTime ProcCode Duration
269 2006-12-15 00:00:00.000 2006-12-15 01:00:00.000 601 60
269 2006-12-15 01:00:00.000 2006-12-15 03:00:00.000 230 120
269 2006-12-15 03:00:00.000 2006-12-15 04:00:00.000 110 60
269 2006-12-15 04:00:00.000 2006-12-15 07:00:00.000 134 180
269 2006-12-15 07:00:00.000 2006-12-15 07:30:00.000 230 30
269 2006-12-15 09:00:00.000 2006-12-15 09:15:00.000 600 15
269 2006-12-15 10:00:00.000 2006-12-15 10:30:00.000 601 30
269 2006-12-15 11:00:00.000 2006-12-15 13:00:00.000 230 120
269 2006-12-15 13:00:00.000 2006-12-15 13:15:00.000 401 15
269 2006-12-15 14:00:00.000 2006-12-15 15:00:00.000 210 60
269 2006-12-15 15:28:00.000 2006-12-15 16:13:00.000 401 45
269 2006-12-15 17:00:00.000 2006-12-15 19:00:00.000 210 120
269 2006-12-15 19:00:00.000 2006-12-15 20:00:00.000 400 60
269 2006-12-15 20:00:00.000 2006-12-15 20:30:00.000 210 30
269 2006-12-15 21:00:00.000 2006-12-15 23:00:00.000 134 120
269 2006-12-15 23:00:00.000 2006-12-15 23:59:59.000 134 180

 

[SQLSister]

so if all your billable time is in one table, what are you backing out? The nonbillable time is already out of that table. Or am I misunderstanding your data. Why not just run the report on billable time?

Questions about posting. See faq183-874

 

[Memento]

Imagine the schedule in Microsoft Outlook. Someone blocks out time from 8am to 12pm that they are free(non-billable). At some point people start arriving and the person creates an ad-hoc appointment for 9am to 10am. The new appointment in our system simply "overlays" over the non-billiable time for that time slot but doesn't remove the non-billiable time from 9:00am to 10:00am. So there's two hours from 9am to 10am on my report one hour billiable and one hour non-billable.

In the above example, I'd like to see:
NB 8am to 9am
B 9am to 10am
NB 10am to 12pm

As it is now:
NB 8am to 12pm
B 9am to 10am

When I run my reports, the report returns I have four hours of non-billable with one hour of billable. I don't want to pay them for five hours for a four hour period. I'm making this example simple because most of the appointments are 15 minutes to 30 minutes.

 

[Catadmin]

Not sure of the T-SQL, but here's Psuedo-code.

Use DateDiff to find true length of the billable hour. Is it 1 hour or 2 hours, etc.

Use DateDiff to find out the length of the non-billable period.

Use DateDiff to find the difference between the billable hour start time and the start time of the non-billable period. Then use the result in a DateAdd to create a "new" end time for that first part.

Use the end time of the billable hour as the start time of the second non-billable period (keeping the end time of the original non-billable period).

Verify # of hours in the new periods, billable & non-billable, against total non-billable minus billiable (first 2 steps).

Now, the first 2 steps I do just so I can verify my numbers are straight at the end, so you could potentially eliminate them. And all of this should be able to be done in a SELECT statement. You may or may not need to create a temp table to do it, though.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[gmmastros]

Interesting problem. One thing you could do to make this simpler is to create a numbers table in your database. You could use this numbers table to join with the billable and non-billable tables (so you have a derived table with a record for each minute of the day for each employee). Then, you could determine whether the 'minute' in the table is billable or not.

The query I show below is pretty complicated. To start, I create some table variables with hard coded data so that I could test my solution. Then I create another table variable to store the numbers. Ideally, you should create a permanent numbers table in your database to improve performance. Finally, I show the query that I worked up.

One drawback with this query is that it ONLY works for a single day. I'll leave it up to you to modify it to work for multiple days (your weekly requirement). You could also make this a function where you pass the StaffId and the Date and have this function return the number of Billable minutes for the staff. You could then use the function in another query to generate your weekly reports.

Here's the query.

[green]-- Create some sample data[/green]
Declare @NonBillableTime Table(StaffId int, StartTime DateTime, EndTime DateTime, ProcCode Int)
Insert Into @NonBillableTime Values(269,'2006-12-15 08:00:00.000','2006-12-15 12:00:00.000',30 ) 
Insert Into @NonBillableTime Values(269,'2006-12-15 13:00:00.000','2006-12-15 17:00:00.000',30)

Declare @BillableTime Table(StaffId int, StartTime DateTime, EndTime DateTime, ProcCode Int, Duration Int)

Insert Into @BillableTime Values(269, '2006-12-15 00:00:00.000', '2006-12-15 01:00:00.000', 601, 60)
Insert Into @BillableTime Values(269, '2006-12-15 01:00:00.000', '2006-12-15 03:00:00.000', 230, 120)
Insert Into @BillableTime Values(269, '2006-12-15 03:00:00.000', '2006-12-15 04:00:00.000', 110, 60)
Insert Into @BillableTime Values(269, '2006-12-15 04:00:00.000', '2006-12-15 07:00:00.000', 134, 180)
Insert Into @BillableTime Values(269, '2006-12-15 07:00:00.000', '2006-12-15 07:30:00.000', 230, 30)
Insert Into @BillableTime Values(269, '2006-12-15 09:00:00.000', '2006-12-15 09:15:00.000', 600, 15)
Insert Into @BillableTime Values(269, '2006-12-15 10:00:00.000', '2006-12-15 10:30:00.000', 601, 30)
Insert Into @BillableTime Values(269, '2006-12-15 11:00:00.000', '2006-12-15 13:00:00.000', 230, 120)
Insert Into @BillableTime Values(269, '2006-12-15 13:00:00.000', '2006-12-15 13:15:00.000', 401, 15)
Insert Into @BillableTime Values(269, '2006-12-15 14:00:00.000', '2006-12-15 15:00:00.000', 210, 60)
Insert Into @BillableTime Values(269, '2006-12-15 15:28:00.000', '2006-12-15 16:13:00.000', 401, 45)
Insert Into @BillableTime Values(269, '2006-12-15 17:00:00.000', '2006-12-15 19:00:00.000', 210, 120)
Insert Into @BillableTime Values(269, '2006-12-15 19:00:00.000', '2006-12-15 20:00:00.000', 400, 60)
Insert Into @BillableTime Values(269, '2006-12-15 20:00:00.000', '2006-12-15 20:30:00.000', 210, 30)
Insert Into @BillableTime Values(269, '2006-12-15 21:00:00.000', '2006-12-15 23:00:00.000', 134, 120)
Insert Into @BillableTime Values(269, '2006-12-15 23:00:00.000', '2006-12-15 23:59:59.000', 134, 180)

[green]-- Create the numbers table[/green]
Declare @Numbers Table(Num int Primary Key Clustered)
Declare @i Int

Set @i = 1
While @i <=1440
  Begin	
    Insert Into @Numbers Values(@i)
    Set @i = @i + 1
  End

[green]-- Query starts here[/green]
Select StaffId,
       Sum(Case When Billable = 1 And NonBillable = 0 Then 1 Else 0 End) As BillableMinutes
From   (
        Select B.StaffId, 
               DateAdd(Minute, Num, 0) As TheTime, 
               Case When Bill.ProcCode Is NULL 
                    Then 0 
                    Else 1 End As Billable, 
               Case When Non.ProcCode Is NULL 
                    Then 0 
                    Else 1 End As NonBillable
        From   @Numbers N
               Cross Join (Select Distinct StaffId 
                           From   @BillableTime) As B
               Left Join @NonBillableTime Non
                 On DateAdd(Minute, Num, 0) 
                 Between Non.StartTime - DateAdd(Day, DateDiff(Day, 0, Non.StartTime), 0)
                 And     Non.EndTime - DateAdd(Day, DateDiff(Day, 0, Non.EndTime), 0)
               Left Join @BillableTime Bill
                 On DateAdd(Minute, Num, 0) 
                 Between Bill.StartTime - DateAdd(Day, DateDiff(Day, 0, Bill.StartTime), 0)
                 And     Bill.EndTime - DateAdd(Day, DateDiff(Day, 0, Bill.EndTime), 0)
		) as A
Group By A.StaffId

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Memento]

Catadmin and Gmmastros,

I'll try both of your suggestions. I won't be able to try them until Monday. Thanks alot!

 

[Catadmin]

NP. Let us know how it works out for you. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ESquared]

select distinct every date, start or finish, billable or billable, into a single column

join this to itself to get every start and end time in its smallest unit that is present in either billable or unbillable. Now you ahve two columns in a temp table

left join this first to billable data, then left join to unbillable data, based on date range of the temp table. By the presence or absence of the billable data, you know whether to add the value from unbillable or billable.

[tt]Billable:
10am - 11am
12pm - 1pm
Unbillable:
7am - 11am
12pm - 2pm

Distinct times:
7am
10am
11am
12pm
1pm
2pm

Self-joined into:
7am 10am
10am 11am
11am 12pm
12pm 1pm
1pm 2pm

Joined to billable and unbillable:

Start End B U ? Hours
7am 10am X U 3
10am 11am X X B 1
11am 12pm X 0
12pm 1pm X X B 1
1pm 2pm X U 1[/tt]