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

Issue

I have the following information using group by and some calculations

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.

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

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

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

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

Thanks in advance.

Solution

You can use a CTE to get the ROW_NUMBER by date, then SELECT the MAX difference, GROUPED BY date, using a subquery that will take the current col1_val and subtracting it from the subsequent row's value using LEAD partitioned by the ROW_NUMBER from the CTE. If the subsequent row is NULL, then subtract it from the current row's col1_val using COALESCE, which will return zero for the earliest year in your table (in your case, 2020).

WITH cte 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
)
SELECT
  c.date_rehearsal AS 'Dates',
  MAX(c.diff) as 'max_col1_val_difference',
  ROUND(MAX(c.diffPercent),2) as 'max_col1_val_percent',
  CONCAT(MAX(c.diff), ' (', ROUND(MAX(c.diffPercent),2), '%)') as 'max_dif_with_percentage'
  FROM (
     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,
     (((b.col1_val - COALESCE(LEAD(b.col1_val) OVER (PARTITION BY b.rn ORDER BY b.date_rehearsal DESC), b.col1_val))/b.col1_val)*100) AS diffPercent
     FROM cte b) c
GROUP BY c.date_rehearsal
ORDER BY c.date_rehearsal DESC

Result:

Dates	max_col1_val_difference	max_col1_val_percent	max_dif_with_percentage
2022-07-01	6	5.08	6 (5.08%)
2021-07-01	10	10.00	10 (10.00%)
2020-07-01	0	0.00	0 (0.00%)

db<>fiddle here.

Answered By - GRIV

Answer Checked By - David Marino (PHPFixing Volunteer)