Menu
I have a table in PostgreSQL DB like this:
Client | Rate | StartDate|EndDate
A | 1000 | 2005-1-1 |2005-12-31
A | 2000 | 2006-1-1 |2006-12-31
A | 3000 | 2007-1-1 |2007-12-31
B | 5000 | 2006-1-1 |2006-12-31
B | 8000 | 2008-1-1 |2008-12-31
C | 2000 | 2006-1-1 |2006-12-31
How to get this result?
Client | Rate | StartDate|EndDate |Pre Rate | Pre StartDate |Pre EndDate
A | 1000 | 2005-1-1 |2005-12-31 | | |
A | 2000 | 2006-1-1 |2006-12-31 | 1000 | 2005-1-1 |2005-12-31
A | 3000 | 2007-1-1 |2007-12-31 | 2000 | 2006-1-1 |2006-12-31
B | 5000 | 2006-1-1 |2006-12-31 | | |
B | 8000 | 2008-1-1 |2008-12-31 | 5000 | 2006-1-1 |2006-12-31
C | 2000 | 2006-1-1 |2006-12-31
Thanks a lot!!!
402
Overview of SQL Server LAG() function In other words, by using the LAG() function, from the current row, you can access data of the previous row, or the row before the previous row, and so on. The LAG() function can be very useful for comparing the value of the current row with the value of the previous row.
In PostgreSQL, the LAG() function is used to access a row that comes exactly before the current row at a specific physical offset. The LAG() comes in handy while comparing the values of the current row with the previous row.
SELECT client,
rate,
startdate,
enddate,
lag(rate) over client_window as pre_rate,
lag(startdate) over client_window as pre_startdate,
lag(enddate) over client_window as pre_enddate
FROM the_table
WINDOW client_window as (partition by client order by startdate)
ORDER BY client, stardate;
This assumes that enddate is always greater than startdate from the same row and that no enddate is greater than the following startdate
190
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
