SQL Percentage increase/decrease-CodePudding

I've attempted to work out percentage increases/decreases but it doesn't seem to be calculating it correctly. You can see in column 4 where I've attempted this. I've tried other ways of doing this by researching on stackoverflow, but without luck.

I get:

% change should read -60%

Can someone please shed light where I am going wrong please?

Thanks.

Here is my MSSQL query:

SELECT OITM.itemcode, 
round(isnull(FO.[QTY ORDERED],0),0) as "Current Day Orders ",
round(isnull(PSO.[QTY PST ORDERED],0),0) as "Previous Sales",
round(isnull(PSO.[QTY PST ORDERED]-(FO.[QTY ORDERED])*100,0)/FO.[QTY ORDERED],0) as "% Change"
FROM OITM LEFT JOIN
                (
                 SELECT RDR1.itemcode, SUM(Quantity) "QTY ORDERED" FROM RDR1 left join ORDR ON RDR1.DocEntry = ORDR.DocEntry
                 WHERE 
                 ORDR.DocDate >= DATEADD(minute, 0, 
                 CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS float)) AS datetime)
                 ) AND CANCELED = 'N'
                 GROUP BY RDR1.itemcode
                 ) AS "FO" ON OITM.itemcode = FO.itemcode
LEFT JOIN
                (
                SELECT itemcode, SUM(Quantity)/22 "QTY PST ORDERED" FROM RDR1 left join ORDR ON RDR1.DocEntry = ORDR.DocEntry
                WHERE
                ORDR.DocDate >= DATEADD(minute, 0, 
                CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS float)) AS datetime)   
                ) -22 AND CANCELED = 'N'
                GROUP BY RDR1.itemcode
                ) AS "PSO" ON OITM.ItemCode = PSO.ItemCode```

CodePudding user response：

You can try this one:

SELECT OITM.ITEMCODE,
       round(isnull(FO.[QTY ORDERED],0),0) as Current_Day_Orders,
       round(isnull(PSO.[QTY PST ORDERED],0),0) as Previous_Sales,
       /* The expression for "% Change" updated.*/ 
       round(isnull((FO.[QTY ORDERED]-PSO.[QTY PST ORDERED])*100,0)/isnull(PSO.[QTY PST ORDERED],1),0) as "% Change"
  FROM OITM 
  LEFT JOIN (SELECT RDR1.itemcode, SUM(Quantity) "QTY ORDERED" 
               FROM RDR1 
               left join ORDR ON RDR1.DocEntry = ORDR.DocEntry
              WHERE ORDR.DocDate >= DATEADD(minute, 0, CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS float)) AS datetime)) AND CANCELED = 'N'
             GROUP BY RDR1.itemcode) AS "FO" 
    ON OITM.itemcode = FO.itemcode
  LEFT JOIN (SELECT itemcode, SUM(Quantity)/22 "QTY PST ORDERED" 
               FROM RDR1 
               left join ORDR 
                 ON RDR1.DocEntry = ORDR.DocEntry
              WHERE ORDR.DocDate >= DATEADD(minute, 0, CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS float)) AS datetime)) -22 AND CANCELED = 'N'
              GROUP BY RDR1.itemcode) AS "PSO" 
    ON OITM.ItemCode = PSO.ItemCode

CodePudding user response：

To endorse my comment above: Here I'm just isolating the problem, ignoring any possibly necessary joins, and just having the minimal input columns: the item code, a date, and an order quantity:

WITH
-- have some data to play with - not part of final query ..
indata(itemcode,dt,orderqty) AS (
            SELECT 42,'2022-02-01',10
  UNION ALL SELECT 42,'2022-02-02', 4
  UNION ALL SELECT 43,'2022-02-01',20
  UNION ALL SELECT 43,'2022-02-02', 8
  UNION ALL SELECT 44,'2022-02-01',30
  UNION ALL SELECT 44,'2022-02-02',12
)
-- end of input data - real query starts here ...
SELECT
  itemcode
, dt
, orderqty
, LAG(orderqty) OVER(PARTITION BY itemcode ORDER BY dt) AS prev_qty
, CAST (
     (orderqty - LAG(orderqty) OVER(PARTITION BY itemcode ORDER BY dt)) 
    * 100 
    / LAG(orderqty) OVER(PARTITION BY itemcode ORDER BY dt) 
  AS INTEGER
  ) AS chg_pct
FROM indata
ORDER BY 1,2;

 itemcode |     dt     | orderqty | prev_qty | chg_pct 
---------- ------------ ---------- ---------- ---------
       42 | 2022-02-01 |       10 |   (null) |  (null)
       42 | 2022-02-02 |        4 |       10 |     -60
       43 | 2022-02-01 |       20 |   (null) |  (null)
       43 | 2022-02-02 |        8 |       20 |     -60
       44 | 2022-02-01 |       30 |   (null) |  (null)
       44 | 2022-02-02 |       12 |       30 |     -60

CodePudding user response：

The problems:

Priority of operation means that * and / is taken into account before the -
Additionally you are confusing your variables (they are inverted in the formula)

Fixing these two should give you the correct value

Critical additional problems:

Be careful of division by 0 if sales of previous day 0. The current version will crash
You correctly check for null in the first to statements, but you don't in the third one, therefore if your previous or current sales are null, the code will still crash

The code suggested by the other user fixes the two first issues, but not the two additional I highlighted, which means your code will crash on null or 0 values