CODEWITHSUNDEEP
sql
dipgirl
dipgirl

Reputation: 690

How to calculate the time in different row but in one column ing sql

I would like to calculate how many minutes the type is running. The process will calculate every hour. But if on that hour got 2 types running, I want to know how many minutes for that process.

Type Start End Result I want
A 1:15 1:30 1:00 - 1:45
b 1:45 1:50 1:45-1:52
A 1:52 1:59 1.52-2:00
A 2:02 2:10
A 2:13 2:20 2:00 - 2:30
B 2:30 2:45 2:30 - 2:46
A 2:46 2:58 2:46 - 3:00

How can I do it using SQL query?

Upvotes: 1

Views: 67

Answers (1)

DannySlor
DannySlor

Reputation: 4620

Here's a solution with Postgres using lead, concat, and coalesce.

select *
      ,concat("Start", '-', coalesce(lead("Start") over(order by "Start"), "End")) as "Result I want is"
from   t
Type Start End Result I want is
A 01:15:00 01:30:00 01:15:00-01:45:00
b 01:45:00 01:50:00 01:45:00-01:52:00
A 01:52:00 01:59:00 01:52:00-02:00:00
A 02:00:00 03:00:00 02:00:00-03:00:00

Fiddle

Upvotes: 1

Related Questions