SQL Query group by to find EffectiveFrom and EffectiveTo Dates for repeating key value pair
0
votes
0
answers
64
views
I have a table with a snapshot of repeated data for a key and value pair with record_timestamps. The dataset can also contain late-arriving historical record_timestamps as well.
I want to generate an output with an EffectiveFrom and EffectiveTo timestamp for each combination of key and value, where the validity closes when the value changes and a new record is created for a new combination.
Note: The same values can reappear after an interval with a different record_timestamp.
**Snapshot of repeated data**
**Expected Outcome**
I have tried the use lead and lag partitioned by the keys and ordered by the timestamp for each record to find the prev and next value and use this to find a way to group sets. However, this does not work as I am missing something since the same value can reappear after an interval, which should not be grouped together.
create table equipments_staging
(
equipment string,
item string,
value string,
timestamp timestamp
)
insert into equipments_staging
values
('M','X','1','2023-11-10 13:00')
,('M','X','2','2023-11-11 13:00')
,('M','X','2','2023-11-12 13:00')
,('M','X','1','2023-11-13 13:00')
,('M','X','1','2023-11-14 13:00')
,('M','X','1','2023-11-15 13:00')
,('M','X','2','2023-11-16 13:00')
,('M','X','2','2023-11-17 13:00')
,('M','X','1','2023-11-18 13:00')


Select equipment,item,value,
LAG(value,1) OVER (PARTITION BY equipment,item ORDER BY timestamp) AS prev,
LEAD(value,1) OVER (PARTITION BY equipment,item ORDER BY timestamp) AS next,
coalesce(LEAD(timestamp,1),'9999-12-31') OVER (PARTITION BY equipment,item ORDER BY timestamp) AS next_timestamp,
timestamp
from repeated_data
Asked by user137279
(1 rep)
Feb 19, 2024, 08:26 PM
Last activity: Feb 19, 2024, 09:21 PM
Last activity: Feb 19, 2024, 09:21 PM