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)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.