Search code examples
sqlsql-servernorthwind

SQL add column to table base another column and row

I create a table show the total revenue for year and month, I want to add a column to table show if the total revenue after is greater than 10 precents revenue before.

I tried:

select DATEPART(YEAR, OrderDate) AS OrderYear,
DATEPART(MONTH, OrderDate) AS OrderMonth,
ROUND(SUM(UnitPrice*Quantity-Discount),2) AS Total_Revenue,
case when   SUM(UnitPrice*Quantity-Discount)  > 10 THEN '>10' ELSE '<=10' end  my_col
FROM [Order Details], Orders
WHERE Orders.OrderID = [Order Details].OrderID
GROUP BY DATEPART(YEAR, OrderDate), DATEPART(MONTH, OrderDate)
ORDER BY DATEPART(YEAR, OrderDate), DATEPART(MONTH, OrderDate)

I got:

enter image description here

I need to get:

enter image description here

The problem is that I need to calculate the percentage of change between the previous row and the current row, how can I do that?

Solution

  • You can use LAG(). I would recommend a subquery, to simplify naming:

    select ym.*,
       (case when Total_Revenue > 1.1 * lag(Total_Revenue) over (order by orderyear, ordermonth)
             then 'Reached'
             else 'Missed'
        end)
from (select DATEPART(YEAR, o.OrderDate) AS OrderYear,
             DATEPART(MONTH, o.OrderDate) AS OrderMonth,                
     ROUND(SUM(od.UnitPrice * od.Quantity - od.Discount), 2) AS Total_Revenue
      from [Order Details] od join
           Orders o
           on o.OrderID = od.OrderID
      group by DATEPART(YEAR, o.OrderDate), DATEPART(MONTH, o.OrderDate)
     ) ym
order by orderyear, ordermonth;

    Notes:

    • Never use commas in the FROM clause.
    • Always use proper, explicit, standard JOIN syntax.
    • Use table aliases, which are abbreviations for the table names.
    • Qualify all column references, especially when your query references more than one table.