Ok... "Basic Sets". Let's do that. I don't have time just now to write the full solution (on my way to work, which also means that it's not fully tested) but consider what you might be able to do with the results of the following (this code would be the basis for a high performance iTVF)...
DECLARE @Periods VARCHAR(720) = '0001001110' --Original string from example in article
WITH cteGroup AS
(--==== Subtract the running total of off hours from an enumeration of hours to form "off hour groupings".
SELECT Hr = t.N
,IsOff = ABS(CONVERT(INT,SUBSTRING(@Periods,t.N,1))-1)
,Grp = t.N-SUM(ABS(CONVERT(INT,SUBSTRING(@Periods,t.N,1))-1)) OVER (ORDER BY t.N)
FROM dbo.fnTally(1,LEN(@Periods)) t
(--==== If we find the Min and the Max of each off hour grouping, we end up with the start and end hour of each grouping.
SELECT StartOffHour = MIN(Hr)
,EndOffHour = MAX(Hr)
WHERE IsOff = 1
GROUP BY Grp
SELECT * --Total Hours is the number of hours of both time on and off since the beginning of the previous time off grouping
,TotalHours = EndOffHour-StartOffHour
+ (StartOffHour-LAG(StartOffHour,1,0) OVER (ORDER BY StartOffHour))
... and that provides the following result.
See the link for the code for the fnTally function in my signature line below, which is also a "basic set" formed by a "Psuedo-Cursor" in the form of a cCTE (Cascading CTE) (thank you Itzik Ben-Gan), which must not be confused with a slothful, resource intensive rCTE (Recursive CTE).
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)