0

I am finding difficulty in writing a query to get the desired output. I need to get date difference between two consecutive rows and If date difference is <=6 months and reason code is valid "Yes" Then new column should be populate with values "Yes" other wise "NO"

I have to populate new column with values "Yes" or "NO" based on criteria given below
Criteria 1:
For first row, New column value should be always "No" From second row, If two dates difference is <=6 moths and reason code is valid then new column value should be "Yes" other wise "No"
Date difference of first row and second row is <=6 months and code valid "yes" hence new column value is Yes.
Date diff of second row and third row is <=6 months but valid is "No" hence new column value is "No".
Date diff of third row and forth row is more than >6 months and but valid is "Yes" hence new column value is "No".
Out put should be like below

Custid enter_date Rscode Valid New_column
123 2020-04-11 BCB Yes No
123 2020-06-13 ABC Yes Yes
123 2020-09-01 FBS No No
123 2021-05-01 ABC Yes NO
123 2021-07-05 ABC Yes Yes

Criteria 2:
If reason code is starts with "RQT" and any of the valid code is followed By RQT(even date difference is 6 months and reason code is valid) then new column should be "No" from next consecutive row, the output is same as is.

Custid enter_date Rscode Valid New_column
345 2020-02-19 RQT Yes NO
345 2020-03-22 BCB Yes NO
345 2020-06-18 RQT Yes Yes
345 2020-10-29 BCB Yes Yes
345 2021-03-24 ABC Yes Yes
Custid enter_date Rscode Valid New_column
346 2020-02-19 RQT Yes NO
346 2020-03-22 RQT Yes NO
346 2020-06-18 RQT Yes Yes
346 2020-10-29 BCB Yes Yes
346 2021-03-24 RQT Yes Yes
ramesh
  • 13
  • 3

1 Answers1

0

You need some Window Functions plus CASE like this:

case 
       -- If reason code is starts with "RQT"
  when first_value(Rscode) over (partition by custid order by enter_date) = 'RQT'
       -- and any of the valid code is followed By RQT
   and lead(Rscode) over (partition by custid order by enter_date) = 'RQT' then 'No'

       -- If two dates difference is <=6 moths and reason code is valid then new column value should be "Yes" other wise "No"
  when enter_date <= add_months(lag(enter_date) over (partition by custid order by enter_date), 6)
   and valid = 'Yes'
    then 'Yes'

  else 'No' -- also covers: For first row, New column value should be always "No"
end
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • Thank You so much its working but if RQT is followed By RQT than New column should be "NO" but new column showed "Yes" , in second criteria, I replaced BCB with RQT and tested it. the new columns showed "Yes" for second row. – ramesh Aug 04 '21 at 18:28
  • My query returns exactly the result you specified for you example data. If there's another issue you should add more details. – dnoeth Aug 04 '21 at 20:28
  • Yes your correct given query is working fine for the specified example data , I added new example (custid 346) under criteria 2. – ramesh Aug 05 '21 at 02:48
  • Works for 346, too, see http://sqlfiddle.com/#!17/8f7fae – dnoeth Aug 05 '21 at 09:15
  • Sorry I gave wrong example data and i made one change in the data for custid 346, I am getting new column value as "NO" for forth row if the fifth row is "RQT". – ramesh Aug 05 '21 at 16:05
  • What's the logic behind `Yes` in row #4? – dnoeth Aug 05 '21 at 17:13
  • I don't have logic for row #4, In the example mentioned only expected result. – ramesh Aug 05 '21 at 20:29