I have a table consists of 5 columns and millions of rows. I want to capture the start and end times when the Scrap column is true (1) to calculate the total duration in minutes.
Can you help me write an SQL query to get the following result?
Answer by samn265 ·
I figure out my question. Here is one way to do what I wanted: select min(timestamp), max(timetamp), datediff(minute, min(timestamp), max(timestamp)) as minutes, max(velocity) as velocity, scrap, max(run) as run, max(length) as length from (select t.*, sum(case when scrap = 0 then 1 else 0 end) over (order by order by timestamp) as grp from t ) t where scrap = 1 group by grp;