CODEWITHSUNDEEP
sqlsql-serversql-order-by
Josh
Josh

Reputation: 3311

Using over() in SQL Server

I am trying to implement a over() query in SQL Server which results in a column that displays a sum of last 3 periods of purchase amount by name.

In example, in period3, the new column would display the sum of period1, period2, period3 grouped by name. In period4, it should show a sum of period2, period3 and period4. In period5, the sum of period3, period4 and period5. So and so forth

The idea is to use specifically the over() function in T-SQL to implement the solution.

Table:

    Date1 Name1 Purchase_Amount Period1
    Date1 Name2 Purchase_Amount Period1
    Date2 Name1 Purchase_Amount Period2
    Date2 Name2 Purchase_Amount Period2
    Date2 Name3 Purchase_Amount Period2
    Date3 Name1 Purchase_Amount Period3
    Date3 Name2 Purchase_Amount Period3
    Date3 Name3 Purchase_Amount Period3
    Date3 Name4 Purchase_Amount Period3
    Date4 Name1 Purchase_Amount Period4
    Date4 Name2 Purchase_Amount Period4
    Date4 Name3 Purchase_Amount Period4
    Date4 Name4 Purchase_Amount Period4
    ...

Upvotes: 1

Views: 68

Answers (1)

Martin Smith
Martin Smith

Reputation: 453243

This should do what you need assuming that once a name is present in the table there is a row for this name in every subsequent period.

SELECT *,
        SUM(Purchase_Amount) OVER (PARTITION BY Name 
                                       ORDER BY Date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
FROM YourTable

If there is no guarantee that there is a row in every subsequent period you would need an outer join on something to create such a row as otherwise the 3 last rows for a name being summed may be across a timespan of more than 3 periods.

Upvotes: 2

Related Questions