I need to populate a column based on conditional precedence:
If O_M is not zero (eg: 0.34) , I check the Prev. record(which is sequenced by TP_N) in the same column O_M and if it is zero for 3 or more instances coded with codes (OD03,OT03,MO03) then i should populate To_Compute column with current O_M value - 0.34. I need to repeat this for every partition (DT,MNTH,P_ID,A_BR,D_BR,B_BR,DR) sequenced by TP_N. I should only look out for these codes from column O_N - (OD03,OT03,MO03)
DT MNTH P_ID A_BR D_BR B_BR TP_N DR O_M O_N TO_Compute
9/29/2016 9 QT21 1506 05Y XS-123 487,006 0 0 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,007 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,008 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,009 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,010 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,011 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,012 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,013 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,014 0 0 MO03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,015 0 0 OT03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,016 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,017 0 0.34 ? 0.34
9/29/2016 9 QT21 1506 05Y XS-123 487,018 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,019 0 1.03 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,020 0 0.3 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,021 0 1.25 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,022 0 0 OP04 0
9/29/2016 9 QT21 1506 05Y XS-123 487,023 0 10.53 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,024 0 0.37 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,025 0 0.28 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,026 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,027 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,028 0 0.6 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,029 0 0.38 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,030 0 0.4 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,031 0 0.35 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,032 0 0.45 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,033 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,034 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,035 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,036 0 0.3 ? 0.3
9/29/2016 9 QT21 1506 05Y XS-123 487,037 0 0.35 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,038 0 0.52 ? 0
However, If O_M is not zero (eg: 0.6 - 11th row from bottom for column O_M) , I check the Prev. record in the same column O_M and i only have 2 prev records as zero ( for 3 or more instances coded with codes (OD03,OT03,MO03) then i should populate To_Compute column with 0.
If O_M is not zero (eg: 0.3 Third last row for O_M) , I check the Prev. record in the same column O_M and here it is zero for 3 or more instances coded with codes (OD03,OD03,OD03) then i should populate To_Compute column with current O_M value - 0.3.
I am new to TD. Any light on this could help.
Untested, but this should work based on your description:
case
when sum(O_M) -- previous three rows are all 0 (assuming no negative values exist)
over (partition by ??
order by TP_N
rows between 3 preceding and 1 preceding) = 0
and -- prvious three rows contain any of the searched codes
sum(case when O_N IN ('OD03','OT03','MO03') then 1 else 0 end)
over (partition by ??
order by TP_N
rows between 3 preceding and 1 preceding) = 3
then O_M
else 0
end