How to get previous month in CASE statement in PLSQL-CodePudding

I have a table which is as below, which says - whenever value for previous month is missing or 0 then flag current month with Y else N

input table
emp    year  month value
e1     2020   12    0.5
e1     2021    1    1 
e1     2021    2    0
e1     2021    3    0.25

output table
emp    year  month value  flag
e1     2020   12    0.5    Y
e1     2021    1    1      N
e1     2021    2    0      N
e1     2021    3    0.25   Y

I tried with case statement but not getting the output expected Please help

CodePudding user response：

just small correction to Koen answer,

this line WHEN LAG(val,1) OVER (ORDER BY val) = 0 THEN 'Y' must be replace by

WHEN LAG(val,1) OVER (ORDER BY d) = 0 THEN 'Y'

CodePudding user response：

For clarity I put a CTE with an additional column that converts the year and month column into a date. Then use LAG to check if the date of the previous row equals the current date minus 1 month.

WITH test_data(emp,year,month,val)
AS
(
SELECT 'e1',2020,   12,    0.5 FROM DUAL UNION ALL
SELECT 'e1',2021,    1,    1  FROM DUAL UNION ALL
SELECT 'e1',2021,    2,    0 FROM DUAL UNION ALL
SELECT 'e1',2021,    3,    0.25 FROM DUAL 
), test_data_wd (emp,year,month,val,d)
AS
(
SELECT 
  emp,
  year,
  month,
  val,
  TO_DATE('01-'||month||'-'||year||'','DD-MM-YYYY') as month_date
  FROM test_data
) 
SELECT
  emp,
  year,
  month,
  val,
  CASE 
    WHEN LAG(val,1) OVER (ORDER BY d) = 0 THEN 'Y' 
    ELSE
      CASE 
        WHEN LAG(d,1) OVER (ORDER BY d) = ADD_MONTHS(d,-1) THEN 'N' 
        ELSE 'Y' 
      END   
   END AS yn   
  FROM test_data_wd

EM       YEAR      MONTH        VAL Y
-- ---------- ---------- ---------- -
e1       2020         12         .5 Y
e1       2021          1          1 N
e1       2021          2          0 N
e1       2021          3        .25 Y