I’m currently using the following code so that the to_date column is filled with the last date that person had come to a specific shop.
It’s given me 99% of what I want but it leave the to_date of the last shop they went to as NULL like below:
SELECT
ID_1, ID_2, SHOP_ID, FROM_DATE, LAG(FROM_DATE, 1) OVER(PARTITION BY IRN_E ORDER BY FROM_DATE DESC) AS TO_DATE
FROM
(SELECT ID_1, ID_E, MIN("YEAR") AS FROM_DATE, SHOP_ID
GROUP BY ID_1, ID_2, SHOP_ID);
| ID_1 | ID_2 | SHOP_ID | FROM_DATE | TO_DATE |
|---|---|---|---|---|
| 123 | ABC | 0001 | 01/03/1999 | 04/05/2002 |
| 123 | ABC | 002 | 04/05/2002 | NULL |
When I want it to look like this:
| ID_1 | ID_2 | SHOP_ID | FROM_DATE | TO_DATE |
|---|---|---|---|---|
| 123 | ABC | 0001 | 01/03/1999 | 04/05/2002 |
| 123 | ABC | 002 | 04/05/2002 | 07/08/2004 |
If 07/08/2004 was the last record we have of that person going to the shop.
1