I have a table that is setup saving data for hours of the day with values attached. The table looks like so (it includes all hours, but I'm only showing a subset here):
| ID | Start | Finish | HR_8 | HR_9 | HR_10 | HR_11 | HR_12 | HR_13 | HR_14 | HR_15 |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 9 | 15 | 10 | 22 | 25 | 30 | 22 | 24 | 8 | 6 |
| 2 | 12 | 15 | 15 | 18 | 20 | 25 | 15 | 18 | 2 | 6 |
| 3 | 8 | 13 | 7 | 12 | 18 | 25 | 9 | 12 | 34 | 19 |
| 4 | 12 | 15 | 8 | 22 | 25 | 31 | 13 | 22 | 24 | 26 |
If I subtract finish from start I get a number of hours. I need to return an indicator of a 1 or a 0 based on the following rule. Return 1 if 60% of the time from Start - Finish (in 24 hour format) falls into an area with at least 3 consecutive numbers greater than 17.
Example:
ID 1 has a consecutive block in HR_9, HR_10, HR_11, HR_12, and HR_13. In this example, 9 - 15 is 100% inside of that range
ID 3 has two consecutive blocks in HR_9, HR_10, and HR_11, and also in HR_13, HR_14, and HR_15. 12 - 15 is at least 60% inside of HR_13, HR_14, and HR_15. However, it could have also fell inside of the first consecutive block.
I will be updating a separate column in this same table with the 1 or 0 derived from the rule above.
I know this table is setup strangely, but it is what I've been given to work with. Any help would be greatly appreciated.
CodePudding user response：
SELECT
ID,
Start,
Finish,
HR_8,
HR_9,
HR_10,
HR_11,
HR_12,
HR_13,
HR_14,
HR_15,
-- Calculate the number of hours between the Start and Finish times
(Finish - Start) AS num_hours,
-- Use a CASE expression to find blocks of numbers that are greater than 17
-- and return 1 if at least 60% of the hours between Start and Finish fall
-- within a block
CASE
-- Iterate over the values in the row, starting at HR_8
WHEN HR_8 > 17 THEN
-- If the value is greater than 17, increment a counter variable
-- and check if the next value is also greater than 17
CASE
WHEN HR_9 > 17 THEN
-- If the next value is also greater than 17, increment the
-- counter variable again and continue checking the next value
-- until we reach a value that is not greater than 17
CASE
...
-- If we reach a value that is not greater than 17,
-- check if the counter variable is greater than or equal to 3.
-- If it is, then we have found a block of at least 3
-- consecutive numbers that are greater than 17, so we can
-- return 1. Otherwise, we return 0.
ELSE
CASE
WHEN counter >= 3 THEN 1
ELSE 0
END
END
ELSE 0
END
ELSE 0
END AS indicator
FROM your_table;
CodePudding user response：
I believe that your first step should be to unpivot your data, either with a single hour per row or progressive groups of three values per row. While a SQL UNPIVOT provides some functionality, it is sometimes more convenient to use a CROSS APPLY, since it is more flexible.
A single-value pivot would be something like:
SELECT D.ID, D.Start, D.Finish, C.*
FROM @Data D
CROSS APPLY (
VALUES
( 8, HR_8),
( 9, HR_9),
(10, HR_10),
(11, HR_11),
(12, HR_12),
(13, HR_13),
(14, HR_14),
(15, HR_15)
) C(HR, Value)
ORDER BY D.ID, C.Hr
| ID | Start | Finish | HR | Value |
|---|---|---|---|---|
| 1 | 9 | 15 | 8 | 10 |
| 1 | 9 | 15 | 9 | 22 |
| 1 | 9 | 15 | 10 | 25 |
| 1 | 9 | 15 | 11 | 30 |
| 1 | 9 | 15 | 12 | 22 |
| 1 | 9 | 15 | 13 | 24 |
| 1 | 9 | 15 | 14 | 8 |
| 1 | 9 | 15 | 15 | 6 |
| 2 | 12 | 15 | 8 | 15 |
| 2 | 12 | 15 | 9 | 18 |
| ... |
Given that, you can apply your over-17 criteria and then use some window function techniques to check for consecutive ranges. See [sql-server] count consecutive for similar questions that may have the answers you need.
If you can reduce your problem down a 3-consecutive over-17 value condition with two inside the start/finish range, you can perhaps use a technique that unpivots your data into groups of three and then applies those tests.
Something like:
SELECT D.ID, D.Start, D.Finish, C.*, T.*
FROM @Data D
CROSS APPLY (
VALUES
(9, HR_8, HR_9, HR_10),
(10, HR_9, HR_10, HR_11),
(11, HR_10, HR_11, HR_12),
(12, HR_11, HR_12, HR_13),
(13, HR_12, HR_13, HR_14),
(14, HR_13, HR_14, HR_15)
) C (HRMid, Value1, Value2, Value3)
CROSS APPLY (
SELECT
CASE WHEN C.Value1 > 17 AND C.Value2 > 17 AND C.Value3 > 17 THEN 1 ELSE 0 END AS ThreeConsecutive,
CASE WHEN HrMid BETWEEN D.Start AND D.Finish AND D.Start <> D.Finish THEN 1 ELSE 0 END AS InRange
) T
ORDER BY D.ID, C.HrMid
| ID | Start | Finish | HRMid | Value1 | Value2 | Value3 | ThreeConsecutive | InRange |
|---|---|---|---|---|---|---|---|---|
| 1 | 9 | 15 | 9 | 10 | 22 | 25 | 0 | 1 |
| 1 | 9 | 15 | 10 | 22 | 25 | 30 | 1 | 1 |
| 1 | 9 | 15 | 11 | 25 | 30 | 22 | 1 | 1 |
| 1 | 9 | 15 | 12 | 30 | 22 | 24 | 1 | 1 |
| 1 | 9 | 15 | 13 | 22 | 24 | 8 | 0 | 1 |
| 1 | 9 | 15 | 14 | 24 | 8 | 6 | 0 | 1 |
| 2 | 12 | 15 | 9 | 15 | 18 | 20 | 0 | 0 |
| 2 | 12 | 15 | 10 | 18 | 20 | 25 | 1 | 0 |
| ... |
See this db<>fiddle for examples of the above.
You would then need to take these intermediate results and feed them into additional logic to GROUP BY ID and test for any satisfying rows in order to calculate your final result.
CodePudding user response：
Not sure about the 60% requirements, so I'm following the 3 consecutive over-17 rule. Please feel free to change the code as needed.
Assume that
- table schema is (id, start, finish, hr01, hr02..., hr24)
- all of the columns can't be NULLs.
Here's one way to do it:
Step 1. Convert the interval (start, finish) to an integer, of which there're 24 bits. Bit 1 indicate 1st hour; bit 2, 2nd hour; ...bit 24, 24th hours.
Step 2. Convert hr01, hr02...hr24 to an integer that bit n is 1 if hr_n > 17; 0, otherwise.
Step 3. Bitwise AND the above two integer and check if there are 3 consecutive 1s. If so return 1; 0, otherwise.
Here are some examples:
ID=1:
sf24bin 11111111111111000 <- start to finish: 3 - 16
hr24bin 1010001010111111000011 <- hr01 to hr24: 1 indicate > 17; 0, otherwise.
consec_3 1010111111000000 <- bitwise and of the above two
ID=4:
sf24bin 111100000000000000
hr24bin 100011110111110100111111
consec_3 110100000000000000
Query:
-- convert (start, finish) to an integer
drop function sf2int;
create function sf2int(
@start int,
@finish int)
returns int
as
begin
declare @n int = 0;
declare @i int = @start;
while @i <= @finish
begin
set @n = @n power(2,@i);
set @i = @i 1;
end;
return @n;
end;
-- check if @x has @n consecutive 1s
create function consecutive_n(
@x int,
@n int)
returns int
as
begin
declare @bitn int = dbo.sf2int(0,@n-1);
declare @lx int = @x;
while @lx > 0
begin
if (@lx & @bitn) = @bitn
return 1; -- there are n consecutive 1s
else
set @lx = @lx / 2; -- shift 1 bit to the right
end;
return 0;
end;
-- do the job
with cte as (
select *,
dbo.sf2int(start, finish) as sf24bin,
case when hr01 > 17 then power(2, 0) else 0 end
case when hr02 > 17 then power(2, 1) else 0 end
case when hr03 > 17 then power(2, 2) else 0 end
case when hr04 > 17 then power(2, 3) else 0 end
case when hr05 > 17 then power(2, 4) else 0 end
case when hr06 > 17 then power(2, 5) else 0 end
case when hr07 > 17 then power(2, 6) else 0 end
case when hr08 > 17 then power(2, 7) else 0 end
case when hr09 > 17 then power(2, 8) else 0 end
case when hr10 > 17 then power(2, 9) else 0 end
case when hr11 > 17 then power(2,10) else 0 end
case when hr12 > 17 then power(2,11) else 0 end
case when hr13 > 17 then power(2,12) else 0 end
case when hr14 > 17 then power(2,13) else 0 end
case when hr15 > 17 then power(2,14) else 0 end
case when hr16 > 17 then power(2,15) else 0 end
case when hr17 > 17 then power(2,16) else 0 end
case when hr18 > 17 then power(2,17) else 0 end
case when hr19 > 17 then power(2,18) else 0 end
case when hr20 > 17 then power(2,19) else 0 end
case when hr21 > 17 then power(2,20) else 0 end
case when hr22 > 17 then power(2,21) else 0 end
case when hr23 > 17 then power(2,22) else 0 end
case when hr24 > 17 then power(2,23) else 0 end as hr24bin
from rs)
select *,
sf24bin & hr24bin as hr_bin,
dbo.consecutive_n(sf24bin & hr24bin,3) as consecutive_3_hrs
from cte;
Some sample output:
id |start|finish|hr01|hr02|hr03|hr04|hr05|hr06|hr07|hr08|hr09|hr10|hr11|hr12|hr13|hr14|hr15|hr16|hr17|hr18|hr19|hr20|hr21|hr22|hr23|hr24|sf24bin |hr24bin |hr_bin |consecutive_3_hrs|
--- ----- ------ ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -------- -------- -------- -----------------
1| 3| 16| 26| 38| 11| 17| 4| 5| 26| 25| 34| 33| 44| 45| 15| 34| 15| 34| 9| 15| 12| 22| 14| 41| 13| 14| 131064| 2666435| 44992| 1|
2| 13| 20| 40| 11| 39| 22| 1| 3| 3| 37| 10| 1| 15| 3| 24| 6| 45| 24| 43| 37| 44| 7| 4| 47| 40| 13| 2088960| 6803597| 507904| 1|
3| 3| 20| 24| 37| 7| 35| 24| 46| 21| 16| 15| 14| 36| 11| 32| 40| 42| 28| 24| 13| 18| 5| 40| 6| 27| 40| 2097144|14021755| 1438840| 1|
4| 14| 17| 19| 38| 34| 43| 20| 26| 12| 13| 29| 15| 30| 24| 27| 19| 32| 8| 40| 30| 37| 31| 4| 4| 15| 26| 245760| 9403711| 212992| 0|
5| 7| 17| 44| 5| 47| 17| 10| 17| 37| 47| 26| 33| 31| 22| 44| 9| 49| 19| 11| 26| 48| 49| 36| 49| 23| 32| 262016|16703429| 188288| 1|
6| 5| 22| 2| 15| 21| 15| 23| 8| 20| 43| 32| 7| 15| 12| 5| 13| 37| 29| 43| 49| 26| 18| 36| 27| 32| 35| 8388576|16761300| 8372672| 1|
7| 13| 20| 28| 22| 40| 44| 32| 15| 1| 14| 14| 21| 26| 10| 10| 23| 1| 0| 27| 42| 4| 48| 21| 34| 12| 6| 2088960| 3876383| 1777664| 0|
8| 2| 16| 5| 47| 9| 24| 33| 36| 22| 34| 29| 48| 3| 37| 25| 49| 10| 40| 48| 15| 3| 32| 38| 3| 16| 36| 131068|10075130| 113656| 1|
9| 1| 21| 31| 44| 17| 43| 10| 34| 38| 8| 10| 3| 4| 40| 38| 40| 38| 26| 43| 3| 47| 40| 17| 4| 38| 30| 4194302|13498475| 915562| 1|
10| 11| 23| 3| 24| 35| 1| 31| 23| 24| 43| 4| 27| 16| 36| 37| 19| 48| 16| 39| 7| 36| 8| 26| 13| 43| 12|16775168| 5602038| 5601280| 1|