SQL query to join data based on transaction date to get this month's & previous month's cost

Question

I have a transactional data set with columns like product_id, cost_unit, date, etc. The cost of the product changes month to month. I would like to have a table which has the following structure

Product | Cost_from_this_Month | Cost_from_Previous_month

There can be multiple rows for a product in each month, but the cost will be the same for that particular month. I.e, the cost is fixed for one month. How do I construct a query to get the result I want?

I know how to get the most recent cost:

  SELECT * FROM (
  SELECT Product, Cost_Unit
                    , Date
                        , zrank = row_number() OVER ( PARTITION BY Product ORDER BY Date DESC)
                FROM    MY_Table
            ) a where a.zrank = 1

Can anyone help me?

Answer 1

If you are using SQL Server 2012 or higher, LEAD or LAG can be used:

DECLARE @Products TABLE (
    Product VARCHAR(100),
    Cost_Unit MONEY,
    [Date] DATETIME
)
INSERT @Products VALUES
    ('Widget', 10, '1/1/2014'),
    ('Widget', 12, '2/1/2014'),
    ('Widget', 15, '3/1/2014'),
    ('Widget', 17, '4/1/2014'),
    ('Gizmo', 20, '1/1/2014'),
    ('Gizmo', 25, '2/1/2014'),
    ('Gizmo', 26, '3/1/2014'),
    ('Gizmo', 27, '4/1/2014')

SELECT
    Product,
    [Date],
    Products.Cost_Unit AS [Cost_This_Month],
    LAG(Products.Cost_Unit) OVER (PARTITION BY Product ORDER BY [Date]) AS [Cost_Last_Month]
FROM @Products Products
ORDER BY Product, [Date] DESC

The output here produces:

Product         Date                    Cost_This_Month       Cost_Last_Month
--------------- ----------------------- --------------------- ---------------------
Gizmo           2014-04-01 00:00:00.000 27.00                 26.00
Gizmo           2014-03-01 00:00:00.000 26.00                 25.00
Gizmo           2014-02-01 00:00:00.000 25.00                 20.00
Gizmo           2014-01-01 00:00:00.000 20.00                 NULL
Widget          2014-04-01 00:00:00.000 17.00                 15.00
Widget          2014-03-01 00:00:00.000 15.00                 12.00
Widget          2014-02-01 00:00:00.000 12.00                 10.00
Widget          2014-01-01 00:00:00.000 10.00                 NULL

If you are not using SQL 2012 or higher and can't use those new functions, then CTE with ROW_NUMBER will also give you the capability:

;WITH CTE AS (
    SELECT
        Product,
        [Date],
        Cost_Unit,
        ROW_NUMBER() OVER (PARTITION BY Product ORDER BY [Date] DESC) AS RowNum
    FROM @Products
)
    SELECT
        CM.Product,
        CM.Product,
        CM.Cost_Unit AS [Cost_This_Month],
        LM.Cost_Unit AS [Cost_Last_Month]
    FROM CTE CM
        LEFT OUTER JOIN CTE LM
            ON CM.Product = LM.Product
                AND CM.RowNum = LM.RowNum - 1

UPDATE Updates to account for multiple transactions in a month. This assumes the last transaction of the month is the cost you use for the month (for example, if $10 on 5/1 and $11 on 5/5, then May would have Cost_Unit of $11).

-- LAG Solution
SELECT
    Product,
    [Date],
    Products.Cost_Unit AS [Cost_This_Month],
    LAG(Products.Cost_Unit) OVER (
        PARTITION BY Product ORDER BY [Date]) AS [Cost_Last_Month]
FROM (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY Product, YEAR([Date]), MONTH([Date])
        ORDER BY [Date] DESC) AS RowNum
    FROM @Products
    ) Products
WHERE RowNum = 1
ORDER BY Product, [Date] DESC

-- ROW_NUMBER() Solution
;WITH CTE AS (
    SELECT
        Product,
        [Date],
        Cost_Unit,
        ROW_NUMBER() OVER (PARTITION BY Product ORDER BY [Date] DESC) AS RowNum
    FROM (
        SELECT *, ROW_NUMBER() OVER (
            PARTITION BY Product, YEAR([Date]), MONTH([Date])
            ORDER BY [Date] DESC) AS RowNum
        FROM @Products
        ) Products
    WHERE Products.RowNum = 1
)
    SELECT
        CM.Product,
        CM.[Date],
        CM.Cost_Unit AS [Cost_This_Month],
        LM.Cost_Unit AS [Cost_Last_Month]
    FROM CTE CM
        LEFT OUTER JOIN CTE LM
            ON CM.Product = LM.Product
                AND CM.RowNum = LM.RowNum - 1

Answer 2

How about conditional aggregation and the lag() function if you are using SQL Server 2012 or above?

select year(date), month(date), max(cost_unit) as thismonthcost,
       lag(max(cost_unit)) over (order by year(date), month(date)) as lastmonthcost
from my_table
group by year(date), month(date)