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
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