PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Tuesday, November 1, 2022

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

 November 01, 2022     database, mysql, pivot     No comments   

Issue

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.


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)
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing