Calculate percent difference between two numbers in SQL Server

elvisidrizi1
 
on Aug 24, 2020 05:19 AM
4873 Views

Hi Everyone, 

How do I calculate a Percentage difference between months in SQL In my SQL I have a DataTable with records which I am trying to create a SQL script that shows me the difference percentage between months.

So the difference from previous sum of prices to the current month sum of prices. But I just want to insert the current month and based on current month the system to take the last month automatic.

SELECT
        Sum(Price) As Total,
        CONVERT(char(7), date, 120) As [year],
        Department ,
         Sum Dinstict (CONVERT(INT, CONVERT(VARCHAR(MAX),quantity))) as Lesh
    FROM
        dbo.Expenses
    WHERE
    CONVERT(char(7), date, 120) ='2020-01' AND
        Department = 'FixCost'
    GROUP BY
       CONVERT(char(7), date, 120),
       Department
       )
SELECT
    Total,
     [year],
     Department as 'Department',
      Concat(SUM(Convert(int,Convert(Varchar(MAX),(Lesh)))) OVER (),'') As GrandTotal
FROM
    cte
WHERE
     [year] Is Not Null

Best Regards,

Elvis

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Aug 25, 2020 06:58 AM
on Aug 26, 2020 12:52 AM

Hi elvisidrizi1,

Refer below query and change the percentage calculation logic as per your need.

DECLARE @Year1 DECIMAL,@Year2 DECIMAL,@Current VARCHAR(7),@Previous VARCHAR(7)
SET @Current='2020-07'
SET @Previous =CAST(SUBSTRING(@Current,1,6) AS VARCHAR(5)) + RIGHT('0' + CAST(SUBSTRING(@Current,6,2) - 1 AS VARCHAR(2)),2)

SELECT @Year1 = Sum(Price) FROM dbo.Expenses WHERE CONVERT(char(7), date, 120) = @Previous AND Department = 'Grocery'
SELECT @Year2 = Sum(Price) FROM dbo.Expenses WHERE CONVERT(char(7), date, 120) = @Current AND Department = 'Grocery'
SELECT 100.0*(@Year2 - @Year1) / @Year1 As PercentageDiff