CODEWITHSUNDEEP
sqloracle-databasewindow-functions
hatorihanso
hatorihanso

Reputation: 59

Extracting the data minus day using LEAD function

I have the table PRODUCTINFO with 2 columns: PRODUCTID (NUMBER), ALLOWFROM (DATE) and now I need to insert one more column ALLOWTO (DATE), which is equal ALLOWFROM in the next row minus 1 day. If there is no next ALLOWFROM for the PRODUCTID, '01-01-1900' is should be printed. 

CREATE TABLE PRODUCTINFO (
    PRODUCTID NUMBER (8,0)
    ALLOWFROM DATE    
);

INSERT INTO PRODUCTINFO
 (PRODUCTID, ALLOWFROM)
VALUES (1, '05.01.2018')

INSERT INTO PRODUCTINFO
 (PRODUCTID, ALLOWFROM)
VALUES (1, '06.05.2018')

INSERT INTO PRODUCTINFO
 (PRODUCTID, ALLOWFROM)
VALUES (1, '06.10.2018')

INSERT INTO PRODUCTINFO
 (PRODUCTID, ALLOWFROM)
VALUES (1, '06.20.2018')

INSERT INTO PRODUCTINFO
 (PRODUCTID, ALLOWFROM)
VALUES (2, '05.01.2018')

INSERT INTO PRODUCTINFO
 (PRODUCTID, ALLOWFROM)
VALUES (2, '06.18.2018')

INSERT INTO PRODUCTINFO
 (PRODUCTID, ALLOWFROM)
VALUES (3, '05.01.2018')

INSERT INTO PRODUCTINFO
 (PRODUCTID, ALLOWFROM)
VALUES (3, '06.08.2018')

I try doing this script, but no result:

SELECT PRODUCTID, ALLOWFROM,
    LEAD(ALLOWTO, 3,'01-01-1900') OVER (ORDER BY ALLOWFROM) - 1 AS ALLOWTO
    FROM PRODUCTINFO
    ORDER BY PRODUCTID, ALLOWFROM;

table

Where am I wrong? How to reach the result?

Upvotes: 0

Views: 1125

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

You can use LAG() analytic function as in the following query :

SELECT PRODUCTID, ALLOWFROM, 
       LEAD(ALLOWFROM - 1,1,date'1900-01-01') 
       OVER (PARTITION BY PRODUCTID ORDER BY PRODUCTID, ALLOWFROM) AS ALLOWTO
  FROM PRODUCTINFO
 ORDER BY PRODUCTID, ALLOWFROM;

in which the date literal is properly formatted as the third argument, and the value 1 in the second argument is the offset.

Demo

Upvotes: 0

Koen Lostrie
Koen Lostrie

Reputation: 18685

Is this what you need ? I ordered by product id (this was not in your question but seems obvious). Suggest you read the documentation on LEAD/LAG to familiarize yourself with the arguments.

SELECT productid,
       allowfrom,
       LEAD(allowfrom - 1,1, TO_DATE('01.01.1900', 'MM.DD.YYYY')) 
         OVER(PARTITION BY productid ORDER BY allowfrom ASC) AS allowto
  FROM productinfo;

 PRODUCTID ALLOWFROM  ALLOWTO   
---------- ---------- ----------
         1 05.01.2018 06.04.2018
         1 06.05.2018 06.09.2018
         1 06.10.2018 06.19.2018
         1 06.20.2018 01.01.1900
         2 05.01.2018 06.17.2018
         2 06.18.2018 01.01.1900
         3 05.01.2018 06.07.2018
         3 06.08.2018 01.01.1900

Upvotes: 1

Related Questions