Forum Discussion

Kenny_Gua's avatar
Kenny_Gua
Copper Contributor
Nov 29, 2024

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)...
  • rodgerkong's avatar
    rodgerkong
    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

     

Resources