question

samn265 avatar image
samn265 asked

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?

sql2014
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

samn265 avatar image samn265 commented ·

Any ideas?

0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·

can you post the table ddl and some sample data as code rather than an image

0 Likes 0 ·
samn265 avatar image samn265 Kev Riley ♦♦ commented ·
0 Likes 0 ·
test031621a.xml (93.3 KiB)
Kev Riley avatar image Kev Riley ♦♦ commented ·

No that's an XML document defining an Excel spreadsheet, we need DDL statements

0 Likes 0 ·
samn265 avatar image samn265 Kev Riley ♦♦ commented ·

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;
0 Likes 0 ·

1 Answer

·
samn265 avatar image
samn265 answered

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;

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image Jeff Moden commented ·

@samn265

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!

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.