Calculate time duration based on a series in a column when the series changes
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.
- Start Scrap-Time is when the value in the Scrap column changes from 0 to 1
- Scrap End Time is when the value in the Scrap column changes from 1 to 0.
Can you help me write an SQL query to get the following result?
No that's an XML document defining an Excel spreadsheet, we need DDL statements
never mind then - that is all I have.
I figure it out:
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;
Best Answer
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;
You need to be REALLY careful here. The reason why people were after you for the actual DDL of the table is because your spreadsheet has ties for the TimeStamp column because it's rounded to the nearest minute. That means that THERE IS NOTHING TO GUARANTEE THE ORDER OF THE ROWS! If your real data is also based on TimeStamps rounded to the nearest minute, your code WILL come up with incorrect answers. Even someone adding a seemingly unrelated index can change the order of processing for the code you've posted.
Heh... of course, none of the code you posted actually comes up with any answer because it's fraught with spelling errors, syntax errors, and logical errors and won't even come close to compiling.
Do yourself a favor in preventing an event that would require you to brush up your resume... post the CREATE TABLE statement and ALL the indexes (the DDL people have been asking for) and generate some "readily consumable data" in the form of INSERT/SELECT statements or INSERT/VALUES statements so that people can help you actually do it right because even after fixing the code above so that it runs, it generates the WRONG ANSWERS!
