CODEWITHSUNDEEP
sqlsql-server
Hafizullah Mahmudi
Hafizullah Mahmudi

Reputation: 61

SQL Server: finding change between selected month and previous month's sales

I have the following table for example. I would like to calculate the changes increase or decrease from previous month. This will show a percentage of change from previous month. 

Location    Month   Sales
A           Jan     1753
B           Jan     32130
C           Jan     71353
D           Jan     74885
E           Jan     50241
F           Jan     66393
A           Feb     80633
B           Feb     67918
C           Feb     73330
D           Feb     33269
E           Feb     78915
F           Feb     98817
A           Mar     80633
B           Mar     67918
C           Mar     73330
D           Mar     33269
E           Mar     78915
F           Mar     98817

I wan to create a table like following. I searched stack overflow but was not able to get table. 

Location    Selected    Current_Month   Prvisous_Month  Change
A           Feb         80633           1753            4500%
B           Feb         67918           32130           111%
C           Feb         73330           71353           3%
D           Feb         33269           74885           -56%
E           Feb         78915           50241           57%
F           Feb         98817           66393           49%

Upvotes: 0

Views: 159

Answers (4)

Radim Bača
Radim Bača

Reputation: 10701

Assuming that you change the Month attribute to date then you can use a LAG window function easily like this

SELECT location,
       month,
       sales,
       lag(sales) over (order by month) previous, 
       (sales/lag(sales) over (order by month) - 1) * 100 as change
FROM your_table
WHERE month = 'feb'

The major issue now in your task is the correct ordering of Month which would be much more easier with date, or numbers.

EDIT: You can use the ordering solution of Cool_Br33ze for current data:

SELECT location,
       month,
       sales,
       lag(sales) over (order by MONTH([T].[Month] + ' 1 1900')) previous, -- taken from Cool_Br33ze solution
       (sales/lag(sales) over (order by MONTH([T].[Month] + ' 1 1900')) - 1) * 100 as change
FROM your_table
WHERE month = 'feb'

However, the best option is to change the data type of Month ...

Upvotes: 0

Siyon DP
Siyon DP

Reputation: 514

SELECT A.Location, 'Feb' AS Selected, A.Sales AS Current_Month 
                   , B.Sales AS Prvisous_Month, (A.Sales - B.Sales)/ B.Sales AS Change 
FROM YourTable A JOIN YourTable B
                  ON A.Month = B.Month + 1   -- You will have to represent Months by numbers
WHERE A.Month = 2                        -- Selected month

Upvotes: 0

Mazhar
Mazhar

Reputation: 3837

If you can't change the datatype of the "Month" column, for whatever reason, then this solution may work

DECLARE @Table TABLE ([Location] CHAR(1), [Month] NVARCHAR(3), Sales INT )
INSERT INTO @Table
    ([Location], [Month], Sales)
VALUES
    ('A',N'Jan',1753),
    ('B',N'Jan',32130),
    ('C',N'Jan',71353),
    ('D',N'Jan',74885),
    ('E',N'Jan',50241),
    ('F',N'Jan',66393),
    ('A',N'Feb',80633),
    ('B',N'Feb',67918),
    ('C',N'Feb',73330),
    ('D',N'Feb',33269),
    ('E',N'Feb',78915),
    ('F',N'Feb',98817),
    ('A',N'Mar',80633),
    ('B',N'Mar',67918),
    ('C',N'Mar',73330),
    ('D',N'Mar',33269),
    ('E',N'Mar',78915),
    ('F',N'Mar',98817)

DECLARE @Selection NVARCHAR(3) = N'Feb' -- Enter Selected Month here

;WITH cteX
AS(
    SELECT
          T.[Location]
        , T.[Month]
        , MonthNum = MONTH([T].[Month] + ' 1 1900') --Use some dummy date here
        , T.Sales
    FROM @Table T
)
SELECT
      T.[Location]
    , Selected      = T.Month
    , CurrentMonth  = T.Sales
    , PreviousMonth = T1.Sales
    , Change        = CAST((T.Sales - T1.Sales) / (T1.Sales * 1.0) * 100.0 AS DECIMAL)
FROM cteX   T
INNER JOIN
    cteX    T1  ON  T1.MonthNum = T.MonthNum - 1
                AND T1.[Location] = T.[Location]
WHERE
    T.[Month] = @Selection

Output

enter image description here

Upvotes: 1

Jason Pease
Jason Pease

Reputation: 62

Something like this should be a good start. As Cool_Br33ze noted, you should rethink the date column of this table.

SELECT 
  *,
  referenceTimePeriod / NULLIF(comparisonTimePeriod, 0) -- avoid DIV0 errors
FROM (
  SELECT Location, Sales
  FROM myTable
  WHERE month = 'jan'
) AS referenceTimePeriod
FULL JOIN(
  SELECT Location, Sales
  FROM myTable
  WHERE month = 'feb'
) AS comparisonTimePeriod ON referenceTimePeriod.Location = comparisonTimePeriod .Location

Upvotes: 0

Related Questions