Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Calculate Growth Percentage Resulting in 0

I have a table that store total account of a product every last day of a month. From that table, I want to calculate how much the account growing in percentage. My table looks like this

PERIOD GROUP_PRODUCT ACCOUNT
2022-01-31 DEPOSITO 22860
2022-02-26 DEPOSITO 22848
2022-03-31 DEPOSITO 22555
2022-04-30 DEPOSITO 22297
2022-05-31 DEPOSITO 22281

And below is my query for calculating the growth percentage

SELECT *
       ,  DIFF / ACC_YSTR GROWTH_PERCENTAGE
FROM(
SELECT *
       , (ACCOUNT - ACC_YSTR) DIFF
FROM(
SELECT PERIOD
       , GROUP_PRODUCT
       , ACCOUNT
       , COALESCE(LAG(ACCOUNT) OVER (PARTITION BY GROUP_PRODUCT ORDER BY PERIOD ASC), ACCOUNT) ACC_YSTR
FROM BASE_TABLE
))

But the problem is, my result for the growth percentage always resulting in 0. How to get the right result?

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

This is my dbfiddle: https://dbfiddle.uk/7kWmsvxM

>Solution :

WITH BASE_TABLE(PERIOD, GROUP_PRODUCT, ACCOUNT) AS(
VALUES
(DATE('2022-01-31'), 'DEPOSITO',    22860),
(DATE('2022-02-26'), 'DEPOSITO',    22848),
(DATE('2022-03-31'), 'DEPOSITO',    22555),
(DATE('2022-04-30'), 'DEPOSITO',    22297),
(DATE('2022-05-31'), 'DEPOSITO',    22281)
)

SELECT *
       ,  1.0*DIFF / ACC_YSTR GROWTH_PERCENTAGE
FROM(
SELECT *
       , (ACCOUNT - ACC_YSTR) DIFF
FROM(
SELECT PERIOD
       , GROUP_PRODUCT
       , ACCOUNT
       , COALESCE(LAG(ACCOUNT) OVER (PARTITION BY GROUP_PRODUCT ORDER BY PERIOD ASC), ACCOUNT) ACC_YSTR
FROM BASE_TABLE
))
PERIOD GROUP_PRODUCT ACCOUNT ACC_YSTR DIFF GROWTH_PERCENTAGE
2022-01-31 DEPOSITO 22860 22860 0 0.0000000000000000000
2022-02-26 DEPOSITO 22848 22860 -12 -0.0005249343832020997
2022-03-31 DEPOSITO 22555 22848 -293 -0.0128238795518207282
2022-04-30 DEPOSITO 22297 22555 -258 -0.0114387053868321879
2022-05-31 DEPOSITO 22281 22297 -16 -0.0007175853253800959

Fiddle

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading