# question

## 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?

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

·

Any ideas?

0 Likes 0 ·
·

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

0 Likes 0 ·
Kev Riley ♦♦ ·
0 Likes 0 ·
test031621a.xml (93.3 KiB)
·

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

0 Likes 0 ·
Kev Riley ♦♦ ·

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 ·

·

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

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

·

@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 ·