Help in Calculation percent

Question

Hi,

I have to compare Number1 of Latest date with the previous date's Number_1.

I also have to compare Number2 of Latest date with the previous date's Number_2.

During compare If deference of Number1 from current date with very previous date's Number_1 is 150% then this rows should be the output result.

During compare If deference of Number2 from current date with very previous date's Number_2 is 150% then this rows should be the output result.

Key columns of the table is C1,C2,C3,C4,C5.

Create table #Comp (C1 char(10), C2 char(10), C3 char(10), C4 char(10), C5 date, Number1 int, Number2 int)

Insert into #Comp ('T1','T','01','A','2021-06-01', 500, 500)

Insert into #Comp ('T1','T','01','A','2022-06-01', 600, 700)

Insert into #Comp ('T1','T','01','A','2023-06-01', 450, 750)

Insert into #Comp ('T2','T','01','A','2020-05-01', 650, 500)

Insert into #Comp ('T2','T','01','A','2021-06-01', 500, 500)

Insert into #Comp ('T2','T','01','A','2023-06-01', 1600, 550)

Insert into #Comp ('T3','T','01','A','2021-06-01', 400, 300)

Insert into #Comp ('T3','T','01','A','2023-06-01', 450, 900)

---Output (If

C1 C2 C3 C4 C5 Number_1 Number_2

T2 T 01 A 2021-06-01 500 500

T2 T 01 A 2021-06-01 1600 550

T3 T 01 A 2021-06-01 400 300

T3 T 01 A 2021-06-01 450 900

Answer 1

Hi @Martin Kevin

Try this query:

;WITH CTE AS
(
SELECT *,LAG(Number1,1,Number1)OVER(PARTITION BY C1,C2,C3,C4 ORDER BY C5)AS Prev_Number1
        ,LAG(Number2,1,Number2)OVER(PARTITION BY C1,C2,C3,C4 ORDER BY C5)AS Prev_Number2
		,LEAD(Number1,1,Number1)OVER(PARTITION BY C1,C2,C3,C4 ORDER BY C5)AS Next_Number1
        ,LEAD(Number2,1,Number2)OVER(PARTITION BY C1,C2,C3,C4 ORDER BY C5)AS Next_Number2
FROM #Comp
)
SELECT C1,C2,C3,C4,C5,Number1,Number2
FROM CTE 
WHERE Number1 > Prev_Number1 * 1.5 OR Number2 > Prev_Number2 * 1.5
UNION
SELECT C1,C2,C3,C4,C5,Number1,Number2
FROM CTE 
WHERE Number1 * 1.5 < Next_Number1 OR Number2 * 1.5 < Next_Number2

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.