CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
CodeNinja
CodeNinja

Reputation: 3278

sql query to find the difference in values from previous month

I have my sql tables and query as shown below :

CREATE TABLE #ABC([Year] INT, [Month] INT, Stores INT);
CREATE TABLE #DEF([Year] INT, [Month] INT, SalesStores INT);
CREATE TABLE #GHI([Year] INT, [Month] INT, Products INT);

INSERT #ABC VALUES (2013,1,1);
INSERT #ABC VALUES (2013,1,2);
INSERT #ABC VALUES (2013,2,3);

INSERT #DEF VALUES (2013,1,4);
INSERT #DEF VALUES (2013,1,5);
INSERT #DEF VALUES (2013,2,6);

INSERT #GHI VALUES (2013,1,7);
INSERT #GHI VALUES (2013,1,8);
INSERT #GHI VALUES (2013,2,9);
INSERT #GHI VALUES (2013,3,10);

My current query is

I have @Year and @Month as parameters , both integers , example @Year = '2013' , @Month = '11'

SELECT T.[Year],
       T.[Month]
       -- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
       ,
       (SELECT SUM(Stores)
        FROM   #ABC
        WHERE  [Year] = T.[Year]
               AND [Month] = T.[Month]) AS [Sum_Stores],
       (SELECT SUM(SalesStores)
        FROM   #DEF
        WHERE  [Year] = T.[Year]
               AND [Month] = T.[Month]) AS [Sum_SalesStores],
       (SELECT SUM(Products)
        FROM   #GHI
        WHERE  [Year] = T.[Year]
               AND [Month] = T.[Month]) AS [Sum_Products]
FROM   (
       -- this selects a list of all possible dates.
       SELECT [Year],
              [Month]
       FROM   #ABC where [Year] = @Year and [Month] = @Month 
       UNION
       SELECT [Year],
              [Month]
       FROM   #DEF where [Year] = @Year and [Month] = @Month
        UNION
        SELECT [Year],
               [Month]
        FROM   #GHI where [Year] = @Year and [Month] = @Month) AS T;

Which returns

+------+-------+------------+-----------------+--------------+
| Year | Month | Sum_Stores | Sum_SalesStores | Sum_Products |
+------+-------+------------+-----------------+--------------+
| 2013 |       |            |                 |              |
| 2013 |       |            |                 |              |
| 2013 |       |            |                 |              |
+------+-------+------------+-----------------+--------------+

What I want to do is to add more columns to the query which show the difference from the last month. as shown below. Example : The Diff beside the Sum_Stores shows the difference in the Sum_Stores from last month to this month.

Something like this : 

+------+-------+------------+-----------------+-----|-----|---+-----------------
| Year | Month | Sum_Stores |Diff | Sum_SalesStores |Diff | Sum_Products |Diff|
+------+-------+------------+-----|------------+----|---- |----+--------------|
| 2013 |       |            |     |                 |     |              |    |
| 2013 |       |            |     |                 |     |              |    |
| 2013 |       |            |     |                 |     |              |    |
+------+-------+------------+-----|------------+--- |-----|----+---------| ----

Can anyone tell me how I can modify this to achive my goal.

Upvotes: 1

Views: 1680

Answers (2)

Joe
Joe

Reputation: 26

Try This:

I created a temp table #XYZ with your data and used that to subtract the previous months data. I have to create 2 more variable to take care of spanning years.

If you have any questions, let me know 

DECLARE @Year varchar(4) 
SET @Year = '2013'
DECLARE @Month  varchar(2)   set @Month = '1'

DECLARE @DiffYear varchar(4) 
Set @DiffYear = DATEPART(yyyy, DATEADD(m,-1,(@Month+'/1/'+@Year) ))
DECLARE @DiffMonth  varchar(2)  
set @DiffMonth= DATEPART(mm, DATEADD(m,-1,(@Month+'/1/'+@Year) ))



SELECT T.[Year],
       T.[Month]
       -- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
       ,
       (SELECT SUM(Stores)
        FROM   #ABC
        WHERE  [Year] = T.[Year]
               AND [Month] = T.[Month]) AS [Sum_Stores],
       (SELECT SUM(SalesStores)
        FROM   #DEF
        WHERE  [Year] = T.[Year]
               AND [Month] = T.[Month]) AS [Sum_SalesStores],
       (SELECT SUM(Products)
        FROM   #GHI
        WHERE  [Year] = T.[Year]
               AND [Month] = T.[Month]) AS [Sum_Products]
INTO #XYZ

FROM   (
       -- this selects a list of all possible dates.
       SELECT [Year],
              [Month]
       FROM   #ABC --where [Year] = @Year and [Month] = @Month 
       UNION
       SELECT [Year],
              [Month]
       FROM   #DEF --where [Year] = @Year and [Month] = @Month
        UNION
        SELECT [Year],
               [Month]
        FROM   #GHI --where [Year] = @Year and [Month] = @Month
        ) 
        AS T; 







SELECT T.[Year],
       T.[Month]
       -- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
       ,
       (SELECT SUM(Stores)
        FROM   #ABC
        WHERE  [Year] = T.[Year]
               AND [Month] = T.[Month]) AS [Sum_Stores],



        ISNULL((SELECT SUM(Stores)
        FROM   #ABC
        WHERE  [Year] = T.[Year]
               AND [Month] = T.[Month]),0) - ISNULL((SELECT  SUM([Sum_Stores])
        FROM   #XYZ
        WHERE  [Year] = @DiffYear
               AND [Month] = @DiffMonth),(SELECT SUM(Stores)
        FROM   #ABC
        WHERE  [Year] = T.[Year]
               AND [Month] = T.[Month])) AS [DIFF_Sum_Stores],    

       (SELECT SUM(SalesStores)
        FROM   #DEF
        WHERE  [Year] = T.[Year]
               AND [Month] = T.[Month]) AS [Sum_SalesStores],
       ISNULL((SELECT SUM(SalesStores)
        FROM   #DEF
        WHERE  [Year] = T.[Year]
               AND [Month] = T.[Month]),0) -

       ISNULL((SELECT SUM([Sum_SalesStores])
        FROM   #XYZ
        WHERE  [Year] = @DiffYear
               AND [Month] = @DiffMonth),(SELECT SUM(SalesStores)
        FROM   #DEF
        WHERE  [Year] = T.[Year]
               AND [Month] = T.[Month])) AS [DIFF_Sum_SalesStores],




       (SELECT SUM(Products)
        FROM   #GHI
        WHERE  [Year] = T.[Year]
               AND [Month] = T.[Month]) AS [Sum_Products],

        ISNULL((SELECT SUM(Products)
        FROM   #GHI
        WHERE  [Year] = T.[Year]
               AND [Month] = T.[Month]),0) -
        ISNULL((SELECT SUM([Sum_Products])
         FROM   #XYZ
        WHERE  [Year] = @DiffYear
               AND [Month] = @DiffMonth),(SELECT SUM(Products)
        FROM   #GHI
        WHERE  [Year] = T.[Year]
               AND [Month] = T.[Month]))  AS [Diff_Sum_Products]


FROM   (
       -- this selects a list of all possible dates.
       SELECT [Year],
              [Month]
       FROM   #ABC where [Year] = @Year and [Month] = @Month 
       UNION
       SELECT [Year],
              [Month]
       FROM   #DEF where [Year] = @Year and [Month] = @Month
        UNION
        SELECT [Year],
               [Month]
        FROM   #GHI where [Year] = @Year and [Month] = @Month
        ) 
        AS T;

Upvotes: 0

sahalMoidu
sahalMoidu

Reputation: 1152

You ca use a CTE and then self join to get the desired result.

Fiddle Here

;WITH DATA AS (
SELECT T.[Year],
       T.[Month]
       -- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
       ,
       (SELECT SUM(Stores)
        FROM   #ABC
        WHERE  [Year] = T.[Year]
               AND [Month] = T.[Month]) AS [Sum_Stores],
       (SELECT SUM(SalesStores)
        FROM   #DEF
        WHERE  [Year] = T.[Year]
               AND [Month] = T.[Month]) AS [Sum_SalesStores],
       (SELECT SUM(Products)
        FROM   #GHI
        WHERE  [Year] = T.[Year]
               AND [Month] = T.[Month]) AS [Sum_Products]
FROM   (
       -- this selects a list of all possible dates.
       SELECT [Year],
              [Month]
       FROM   #ABC where [Year] = @Year and [Month] = @Month
       UNION
       SELECT [Year],
              [Month]
       FROM   #DEF where [Year] = @Year and [Month] = @Month
       UNION
       SELECT [Year],
              [Month]
       FROM   #GHI where [Year] = @Year and [Month] = @Month
) AS T )

SELECT  d1.year,d1.month ,
    d1.Sum_Stores , ( isnull(d2.Sum_Stores,0) -d1.Sum_Stores ) AS storeDiff ,
    d1.Sum_SalesStores ,( isnull(d2.Sum_SalesStores,0) -d1.Sum_SalesStores ) AS salesStoresDiff,
    d1.Sum_Products , ( isnull(d2.Sum_Products,0) -d1.Sum_Products ) AS prodDiff 

  -- self joining on month -1 to get previous month data
FROM DATA AS d1 LEFT OUTER JOIN DATA AS d2 ON d2.month = d1.month -1

The above query is for illustrative purposes only.The query provided works correctly for the sample data as it contains the data for a year only. You should apply appropriate logic in the on clause of the left outer join to retrieve data which has input data of more than one year.

Upvotes: 1

Related Questions