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.