Progress SQL looping throug table and calculate date diff on repeated entities

Question

I have made a query that returns data like this:

code        title   date
i1.000182   Gate 1  2019-08-22
i1.000182   Gate 2  2019-08-22
i1.000182   Gate 3  2019-08-26
i1.000182   Gate 4  2019-10-01
I1.000156   Gate 4  2020-07-02
i1.000194   Gate 4  2020-01-06
i1.000194   Gate 4  2020-05-31
i1.000090   Gate 4  2020-05-14

For each time a "code" is represented, I need to calculate the datediff between the Gate-dates, e.g.:

code        title   date        datediff
i1.000182   Gate 1  2019-08-22  0
i1.000182   Gate 2  2019-08-22  0
i1.000182   Gate 3  2019-08-26  4
i1.000182   Gate 4  2019-10-01  36
I1.000191   Gate 3  2019-09-19  0
I1.000191   Gate 4  2020-10-16  393
i1.000194   Gate 4  2020-01-06  0
i1.000194   Gate 4  2020-05-31  146
i1.000090   Gate 4  2020-05-14  0

I know how to compare dates, but can I loop though X times a code is repeated to calculate date?

Maybe something like this? https://stackoverflow.com/questions/2357515/calculate-time-difference-between-two-rows — Claus Jepsen, Jan 13 '21 at 16:22
Progress or PostgreSQL? Those are two very different database products. — , Jan 15 '21 at 06:41
Sorry my bad: Progess SQL https://documentation.progress.com/output/ua/OpenEdge_latest/ — Claus Jepsen, Jan 15 '21 at 09:01

eshirvana · Answer 1 · 2021-01-15T04:01:56.827

0

by using LAG() windowing function like so: If "date" column has date data type

select *
,"date" - LAG("date",1,"date") OVER (PARTITION BY code ORDER BY "date")  datediff
from table

If "date" column has timestamp data type:

select *
,date_part('day', "date" - LAG("date",1,"date") OVER (PARTITION BY code ORDER BY "date"))  datediff
from table

edited Jan 15 '21 at 04:01

answered Jan 13 '21 at 16:27

eshirvana

23,227
3
22
38

DATEDIFF does not exists in PostgreSQL, I guess DATE_PART must be used. SELECT *, DATE_PART('day',LAG(fr.flash_report_date::date) OVER (PARTITION BY pr.code ORDER BY fr.flash_report_date::date) ) as DateDifference FROM rt_flash_report_project fr INNER JOIN rt_project pr ON fr.project_id=pr.id WHERE LEFT(fr.title, 4) ='Gate' AND pr.status <> 'Draft' ORDER BY fr.project_id, fr.title Result: code Title Date DateDifference i1.000191 Gate 3 19-09-2019 i1.000191 Gate 4 16-10-2020 4 In this case the Datedifferece should be: 393 days – Claus Jepsen Jan 14 '21 at 21:53
@ClausJepsen - i used your example and it works perfectly *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_13&fiddle=d541dac05e76f126b3be84703f811f31)* – eshirvana Jan 15 '21 at 04:09
In your example you add two dates (ddate and tdate) to each row in the table. I do not have that information. I only have a "Created date" for each row; hence, I need to compare with the next in line. Your example does not show that. – Claus Jepsen Jan 15 '21 at 09:08
@ClausJepsen - no I'm comparing the ddate with previous ddtae and the smae with tdaate , I just wanted to showcase you both timestamp and date data type – eshirvana Jan 15 '21 at 14:52

Progress SQL looping throug table and calculate date diff on repeated entities

1 Answers1