Forum Discussion
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), C char(10), D char(10), Edate datetime, FAmt decimal (6,2), G char(2))
Insert into #AR values ('AA','CCC','1','E', '2022-01-01',12.60,'2')
Insert into #AR values ('AA','CCC','1','E', '2023-01-01',15.80,'2')
Insert into #AR values ('AA','CCC','1','E', '2024-01-01',18.60,'2')
Insert into #AR values ('BB','DCC','1','E', '2022-01-01',11.40,'2')
Insert into #AR values ('BB','DCC','1','E', '2024-01-01',15.60,'2')
Insert into #AR values ('CC','DCC','1','E', '2021-01-01',12.60,'2')
Insert into #AR values ('CC','GCC','2','E', '2022-01-01',15.60,'2')
Insert into #AR values ('CC','GCC','2','E', '2023-04-01',18.60,'2')
Insert into #AR values ('CC','GCC','2','E', '2024-04-01',34.80,'2') --Note: This FAmt is more than 80% from last timeline and it should be in the expected result
Expected result:
A B C D Edate FAmt G Comments
CC GCC 2 E 2024-04-01 34.80 2 Current Amount is > 80% from last timeline
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 = 1Hi, 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?
