Oracle SQL: LAG over a range of values

Question

How do we use analytic function LAG over the range of values. It should return null if there is no record in the partition with an earlier in_date otherwise, year and month of the previous in_date

Example:

CID  IN_DATE        
1    2020-05-29
1    2020-06-10
1    2020-06-21
1    2020-07-08
1    2020-08-11
1    2020-10-01
2    2020-05-05
2    2020-05-03
2    2020-06-01
2    2020-06-02
2    2020-06-03

Expected Output,

CID  IN_DATE        LAG
1    2020-05-29     null
1    2020-06-10     2020-05
1    2020-06-21     2020-05
1    2020-07-08     2020-06
1    2020-08-11     2020-07
1    2020-10-01     2020-08
2    2020-05-05     null 
2    2020-05-03     null
2    2020-06-01     2020-05
2    2020-06-02     2020-05
2    2020-06-03     2020-05
2    2020-07-03     2020-06
2    2020-08-13     2020-07

My current query using LAG returns below

with data as (
select 1  CID,  TO_DATE('2020-05-29','YYYY-MM-DD')  IN_DATE from dual union all
select 1,  TO_DATE('2020-06-10','YYYY-MM-DD') from dual union all
select 1,  TO_DATE('2020-06-21','YYYY-MM-DD')  from dual union all
select 1,  TO_DATE('2020-07-08','YYYY-MM-DD') from dual union all
select 1,  TO_DATE('2020-08-11','YYYY-MM-DD') from dual union all
select 2,  TO_DATE('2020-05-05','YYYY-MM-DD') from dual union all
select 2,  TO_DATE('2020-05-03','YYYY-MM-DD') from dual union all
select 2,  TO_DATE('2020-06-01','YYYY-MM-DD') from dual union all
select 2,  TO_DATE('2020-06-02','YYYY-MM-DD') from dual union all
select 2,  TO_DATE('2020-06-03','YYYY-MM-DD') from dual 
    ) 
    select CID, 
           to_char(TO_DATE(IN_DATE), 'YYYY-MM-DD') IN_DATE, 
           LAG(to_char(TO_DATE(IN_DATE), 'YYYY-MM')) OVER (PARTITION BY CID 
                                                           ORDER BY to_char(TO_DATE(IN_DATE), 'YYYY-MM') ) LAG 
    from data

Current Result

    CID IN_DATE     LAG
1   1   2020-05-29  NULL
2   1   2020-06-10  2020-05
3   1   2020-06-21  2020-06
4   1   2020-07-08  2020-06
5   1   2020-08-11  2020-07
6   2   2020-05-05  NULL
7   2   2020-05-03  2020-05
8   2   2020-06-01  2020-05
9   2   2020-06-02  2020-06
10  2   2020-06-03  2020-06

Looks like, LAG does not allow us to use partition over range Is there an alternate approach for this

score 3 · Accepted Answer · answered Sep 08 '20 at 15:49

You don't want LAG() if you want the previous month before this one. I would suggest:

with data as (
    select 1  CID,  TO_DATE('2020-05-29','YYYY-MM-DD')  IN_DATE from dual union all
    select 1,  TO_DATE('2020-06-10','YYYY-MM-DD') from dual union all
    select 1,  TO_DATE('2020-06-21','YYYY-MM-DD')  from dual union all
    select 1,  TO_DATE('2020-07-08','YYYY-MM-DD') from dual union all
    select 1,  TO_DATE('2020-08-11','YYYY-MM-DD') from dual union all
    select 2,  TO_DATE('2020-05-05','YYYY-MM-DD') from dual union all
    select 2,  TO_DATE('2020-05-03','YYYY-MM-DD') from dual union all
    select 2,  TO_DATE('2020-06-01','YYYY-MM-DD') from dual union all
    select 2,  TO_DATE('2020-06-02','YYYY-MM-DD') from dual union all
    select 2,  TO_DATE('2020-06-03','YYYY-MM-DD') from dual 
    ) 
select CID, 
       to_char(TO_DATE(IN_DATE), 'YYYY-MM-DD') IN_DATE, 
       TO_CHAR(MAX(IN_DATE) OVER (PARTITION BY CID 
                                  ORDER BY TRUNC(IN_DATE, 'MON')
                                  RANGE BETWEEN UNBOUNDED PRECEDING AND INTERVAL '1' MONTH PRECEDING
                                 ),
               'YYYY-MM') as LAG 
from data;

Here is a db<>fiddle.

Thank you. This answers my question – Darshan Puttaswamy Sep 08 '20 at 18:32 — Darshan Puttaswamy, Sep 08 '20 at 18:32

score 0 · Answer 2 · answered Sep 08 '20 at 15:48

The Lag field for record 7 is not null because the lag function is finding record 6.

I can see two ways to clarify your question:

You want to return null if there is no record in the partition with an earlier in_date
You want to return null if there is no record in the partition where the in_date is from the month before the in_date of the current record

Oracle SQL: LAG over a range of values

2 Answers2