SQLTeam.com | Weblogs | Forums

Adding up time in certain status's


#1

I have a database that tracks time in different status's. I want to write a code that will total the hours that a unit is in any status that starts with a "C". Each line has a Start_Date and End_Date along with the status it was in for that time.

Something like this:
Select Sum(End_Date-Start_Date)
From wo_lines
Where Status like 'C%'

The twist is, If the unit was ever in status W010, I need it to ignore all "C" status time before the End_Date of W010 and only count the total hours a unit is in "C" status after the End_date of W010.

Units proceed through our shop. We use multiple status's as it goes through. All status's that start with "W" represent times we are working the unit. Status's that start with "C" represent time where the unit is paused waiting on the customer for direction. We are tracking our turn times by totaling all the time the unit is in "W" status's minus the time sitting in "C" status's. The issue is sometimes a unit have a work scope change, status "W010". If the work scope changes, we restart the clock and only count "W" time after status "W010" and "C" status after "W010". Any help would be appreciated.

I'm sort of new at this and I'm using sql 2008.


#2

please post your table DDL, sample data DML and expected result


#3
Select
    wl.Unit_number,
    SUM(case when wl.start_date > ISNULL(wl_w010.w010_end_date, '19000101') then End_Date-Start_Date else 0 end) as total_hours
From wo_lines wl
Left outer join (
    select Unit_number, MAX(end_date) as w010_end_date
    From wo_lines
    where status = 'W010'
) as wl_w010 on wl_w010.unit_number = wl.unit_number
Where wl.Status like 'C%'

#4

Thank you both. Scott, I gave this to my guy and he can work with your suggestion here.


#5

Great! Sorry, just noticed I left the GROUP BY off the inner query, but you probably already caught it anyway:

Select
    wl.Unit_number,
    SUM(case when wl.start_date > ISNULL(wl_w010.w010_end_date, '19000101') then End_Date-Start_Date else 0 end) as total_hours
From wo_lines wl
Left outer join (
    select Unit_number, MAX(end_date) as w010_end_date
    From wo_lines
    where status = 'W010'
    Group by Unit_number --<-- ***add this***
) as wl_w010 on wl_w010.unit_number = wl.unit_number
Where wl.Status like 'C%'