2022-09-14

How can i get substaction calculation row between multiple dates and display the percentage?

I have the following information using group by and some calculations

enter image description here

I'm trying to calculate the maximum difference value between several dates in this example are 3 dates (2022, 2021, 2020), the oldest date should calculate 0 because won't do substractions.

enter image description here

After detecting the maximun difference between the previous year, it must calculate the percentage:

enter image description here

After doing the query for maximum difference calculation between date rows. The final result should be this:

enter image description here

Demo with 4 dates: https://dbfiddle.uk/KF-d2KpR?hide=4

The following query is displaying without percentage:

 WITH cte1 AS (
 SELECT
 a.date_rehearsal,
 a.col1_val,
 ROW_NUMBER() OVER (PARTITION BY a.date_rehearsal ORDER BY a.date_rehearsal DESC) AS rn
 FROM demo a
 ),
 cte2 AS (
 SELECT
 b.date_rehearsal,
 b.col1_val - COALESCE(LEAD(b.col1_val) OVER (PARTITION BY b.rn 
 ORDER BY b.date_rehearsal DESC), b.col1_val) AS diff
 FROM cte1 b)
 SELECT
 c.date_rehearsal AS 'Dates',
 MAX(c.diff) as 'max_col1_val_difference'
 FROM cte2 c
 GROUP BY c.date_rehearsal
 ORDER BY c.date_rehearsal DESC

enter image description here

Can you please help me this operation to display with percentage?

Thanks in advance.



No comments:

Post a Comment