4

I'm trying to create a view in SQL which merge start datetime and last datetime and splits when value column changed knowing that the value of the date difference is not fixed. For example I have a table name Sensors:

SensorID StartTime EndTime value
14033 2023-05-01 00:00:00.000 2023-05-01 00:05:00.000 0
14033 2023-05-01 00:05:00.000 2023-05-01 00:20:00.000 1
14033 2023-05-01 00:20:00.000 2023-05-01 03:00:00.000 1
14033 2023-05-01 03:00:00.000 2023-05-01 12:00:00.000 1
14033 2023-05-01 12:00:00.000 2023-05-01 20:00:00.000 0
14033 2023-05-01 20:00:00.000 2023-05-01 22:59:59.000 0

the result must be like the table below:

SensorID StartTime EndTime value
14033 2023-05-01 00:00:00.000 2023-05-01 00:05:00.000 0
14033 2023-05-01 00:05:00.000 2023-05-01 12:00:00.000 1
14033 2023-05-01 12:00:00.000 2023-05-01 22:59:59.000 0

1 Answer 1

6

Base on this sample, here is some piece of code, inspired by Itzik Ben-Gan , Special Islands solution here is the link.
(I added CTEs to be better understood)

CREATE VIEW dbo.vw_Sensors
AS

    with CTE_Source AS
    (
        SELECT *    ,
            LAG(EndTime,1,NULL) OVER(PARTITION BY SensorID ORDER BY StartTime) as EndTime_prev,
            LAG(Value,1,NULL) OVER(PARTITION BY SensorID ORDER BY StartTime) as Value_prev
        FROM dbo.Sensors
    )
    ,CTE_Source2 AS
    (
        SELECT * ,
            CASE WHEN s.StartTime <> ISNULL(s.EndTime_prev,'19000101') THEN 1
                 WHEN s.Value <> ISNULL(s.Value_prev,-1) THEN 1
                 ELSE 0 
            END as IsGrp
        FROM CTE_Source as s
    )
    ,CTE_Source3 AS
    (
        SELECT * ,
            SUM(s.isGrp) OVER(PARTITION BY s.SensorID  ORDER BY StartTime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sumGrp
        FROM CTE_Source2 as s
    )
    SELECT s.SensorID,
        MIN(s.StartTime) as StartTime,
        MAX(s.EndTime) as EndTime,
        MAX(s.Value) as Value
    FROM CTE_Source3 as s
    GROUP BY 
        s.SensorID,
        s.sumGrp
GO

output:

SensorID StartTime EndTime Value
14033 2023-05-01 00:00:00.000 2023-05-01 00:05:00.000 0
14033 2023-05-01 00:05:00.000 2023-05-01 12:00:00.000 1
14033 2023-05-01 12:00:00.000 2023-05-01 22:59:59.000 0

dbfiddle

In step 1 (first select), we just calculate the previous values for EndTime and for Value

SensorID StartTime EndTime value EndTime_prev Value_prev
14033 2023-05-01 00:00:00.000 2023-05-01 00:05:00.000 0 null null
14033 2023-05-01 00:05:00.000 2023-05-01 00:20:00.000 1 2023-05-01 00:05:00.000 0
14033 2023-05-01 00:20:00.000 2023-05-01 03:00:00.000 1 2023-05-01 00:20:00.000 1

The step 2, if there is a difference between StartTime and previous EndTime or a difference between Value and previous Value , then we mark this row as 1 = IsGrp

SensorID StartTime EndTime value EndTime_prev Value_prev IsGrp
14033 2023-05-01 00:00:00.000 2023-05-01 00:05:00.000 0 null null 1
14033 2023-05-01 00:05:00.000 2023-05-01 00:20:00.000 1 2023-05-01 00:05:00.000 0 1
14033 2023-05-01 00:20:00.000 2023-05-01 03:00:00.000 1 2023-05-01 00:20:00.000 1 0

In the step 3, we apply SUM on this new field (isGrp) .

SensorID StartTime EndTime value EndTime_prev Value_prev IsGrp sumGrp
14033 2023-05-01 00:00:00.000 2023-05-01 00:05:00.000 0 null null 1 1
14033 2023-05-01 00:05:00.000 2023-05-01 00:20:00.000 1 2023-05-01 00:05:00.000 0 1 2
14033 2023-05-01 00:20:00.000 2023-05-01 03:00:00.000 1 2023-05-01 00:20:00.000 1 0 2
14033 2023-05-01 03:00:00.000 2023-05-01 12:00:00.000 1 2023-05-01 03:00:00.000 1 0 2

The final step/select is just to aggregate these information to display the desired output

2
  • 1
    Thank you Sabin for answering the question. It works fine. I will test more the function and if there is an issue I will reply here. Jun 7 at 11:31
  • @MohammadNajem, great, glad to help!
    – Sabin B
    Jun 7 at 11:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.