Forum Discussion
Kenny_Gua
Nov 29, 2024Copper Contributor
Help in Query to calculate the % from last two timelines
I want to compare the FAmt from last recent two lines and if difference is more than 80% then it should be in the result. The group/index keys are A,B,C,D, Edate. Create table #AR (A int, B char(10)...
- Dec 04, 2024
Check this code and comments
SELECT A,B,C,D,CAST(Edate AS DATE) AS EDate,FAmt,G, 'Current Amount is > 80% from last timeline' AS Comments FROM ( SELECT A,B,C,D,Edate,FAmt,G, IIF ( LEAD(FAmt, 1, -1) OVER(PARTITION BY A,B,C,D ORDER BY EDATE DESC) = 0.00 AND FAmt <> 0.00, --If previous value is ZERO and the last one is not, the difference can be considered as >80% 999999, IIF ( LEAD(FAmt, 1, -1) OVER(PARTITION BY A,B,C,D ORDER BY EDATE DESC) = 0.00 AND FAmt = 0.00, --If previous value and the last one both ZERO, the difference is ZERO 0, FAmt/LEAD(FAmt, 1, -1) OVER(PARTITION BY A,B,C,D ORDER BY EDATE DESC) - 1 --OR calculate as normal way ) ) AS P, ROW_NUMBER() OVER(PARTITION BY A,B,C,D ORDER BY EDATE DESC) AS RN FROM #AR )T WHERE P>0.8 AND RN = 1
Kenny_Gua
Dec 02, 2024Copper Contributor
Hi, When i added the following additional data then gets the following error message. I tried to modify the script and tried but still getting the same following error:
Insert into #AR values ('ZZ','SEE','2','E', '2022-01-01',0.0,'2')
Insert into #AR values ('ZZ','SEE','2','E', '2023-01-01',0.0,'2')
Insert into #AR values ('ZZ','SEE','2','E', '2024-01-01',0.0,'2')
Error: Divide by Zero error encountered.
rodgerkong
Dec 03, 2024Iron Contributor
What is your expected result when previous value is 0 and the last value is not?
- Kenny_GuaDec 03, 2024Copper Contributor
if FAmt's difference is more than 80% of very latest last two timelines then it should be in the result. If difference is less than 80% then it should not be in the result. Here only problem is that Query is no executing successfully. It is getting error. Error: Divide by Zero error encountered.
- rodgerkongDec 04, 2024Iron Contributor
Check this code and comments
SELECT A,B,C,D,CAST(Edate AS DATE) AS EDate,FAmt,G, 'Current Amount is > 80% from last timeline' AS Comments FROM ( SELECT A,B,C,D,Edate,FAmt,G, IIF ( LEAD(FAmt, 1, -1) OVER(PARTITION BY A,B,C,D ORDER BY EDATE DESC) = 0.00 AND FAmt <> 0.00, --If previous value is ZERO and the last one is not, the difference can be considered as >80% 999999, IIF ( LEAD(FAmt, 1, -1) OVER(PARTITION BY A,B,C,D ORDER BY EDATE DESC) = 0.00 AND FAmt = 0.00, --If previous value and the last one both ZERO, the difference is ZERO 0, FAmt/LEAD(FAmt, 1, -1) OVER(PARTITION BY A,B,C,D ORDER BY EDATE DESC) - 1 --OR calculate as normal way ) ) AS P, ROW_NUMBER() OVER(PARTITION BY A,B,C,D ORDER BY EDATE DESC) AS RN FROM #AR )T WHERE P>0.8 AND RN = 1
- Kenny_GuaDec 06, 2024Copper Contributor
Hi, Thanks a lot for your help