Search code examples
sql-servert-sqlsql-updatedateinterval

Update field in table using SUM of the same field in a specific date interval

I have the following table in SQL Server:

Sales

Business rule that must be applied to table: if a Staff_Id reaches Staff_Sales of 2500+ for the past 6 months, the sales quota is reached (..and thus QuotaReached = 1).

In short, I need to UPDATE the column Staff_Sales_Last_6_Months. This column must be calculated, so that we get a result like this:

SalesUpdated

Notice how the last row has calculated the SUM of Staff_Sales for the past 6 months. This is exactly what I need.

My attempt (which does not work):

WITH cte_SumSales AS
(
    SELECT
        Staff_Id, SUM(Sales) AS SumSales
    FROM 
        Sales
    WHERE 
        [Date] BETWEEN [Date] AND DATEADD(month, -6, [Date])
    GROUP BY 
        Staff_Id
)
UPDATE t1
SET t1.Staff_Sales_Last_6_Months = t2.SumSales
FROM Sales AS t1
INNER JOIN cte_SumSales AS t2 ON T1.Staff_Id = T2.Staff_Id

Any ideas on how I can fix this / better approaches?

Thanks.

Solution

  • Try this:

    UPDATE te
SET Staff_Sales_Last_6_Month = SS
    , QuotaReached = IIF(SS < 3000, 0, 1)
FROM Table1 as te
OUTER APPLY (
    SELECT SUM(ti.Staff_Sales) 
    FROM Table1 as ti
    WHERE te.Staff_Id = ti.Staff_Id 
        and ti.Date1 <= te.Date1
        and ti.Date1 >= DATEADD(MONTH,-6,te.Date1)
) as ta(SS);