Forum Discussion
Help in Query to calculate the % from last two timelines
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
- Use LEAD() to fetch FAmt value from next row with OVER clause, which has PARTITION BY group keys, ORDER BY Edate DESC.
- Calculate (current FAmt/next FAmt) - 1 as your expect, named P.
- Note: set -1 as default value of LEAD(), when the group has only one row, the LEAD() will return -1, the result of step 2 will be negative, always less than 0.8.
- Use ROW_NUMBER() assign a row number to each row, with the same OVER clause with LEAD().
- Build a outter query to filter the row which is first row of each group and P>0.8.
BTW, your definition of column A is not fit on sample data.
Here is code:
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,
FAmt/LEAD(FAmt, 1, -1) OVER(PARTITION BY A,B,C,D ORDER BY EDATE DESC) - 1 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
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.
What is your expected result when previous value is 0 and the last value is not?
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.
