Sql-server – Updating total using values from previous row – SQL Server 2016

sql serversql-server-2016

I have a table that depends on the previous row’s values for its new total. We have discovered that a couple of years ago a value was incorrectly altered and has subsequently made all following values incorrect. I have inherited a formula that uses the previous date to calculate but there appears to be dates missing in the table and at this point the formula stops. I would like to make the update use the previous row rather than the date. I have tried using LAG but had issues using it with an UPDATE.

This is an example of the type of data I am using

|  Date   |Value1|Value2|Value3|Total
--------------------------------
|20151112 |  2   |  2   |  3   | 20

I need to add Value1 + Value2 + Value3 * Previous Day Total. The answer to this will update the following row's total. As mentioned I need to use an UPDATE because there is existing data in the total, but it's currently incorrect.

The results I would like to achieve are in the Table below. Please be aware that dates are not sequential so I can't use date as a condition. An answer with LAG would be great.

|  Date   |Value1|Value2|Value3|Total
--------------------------------
|20151112 |  2   |  2   |  3   | 20
|20151114 |  2   |  3   |  1   | 140
|20151115 |  1   |  1   |  1   | 420
|20151117 |  1   |  3   |  1   | 2100
|20151119 |  2   |  1   |  3   | 12600

Example – for the 20151114 – 2 + 2 + 3 * 20 (Total from the previous row) = 140

Best Answer

I had to do something similar once and had a good result from using a windowing function like below:

SELECT RunningTotal = History.Total + ISNULL((SUM(History.Total) OVER 
                    (PARTITION BY PartitionID 
                    ORDER BY History.EffectiveDate ASC, History.HistoryID ASC 
                    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)), 0)
FROM   History
ORDER BY History.EffectiveDate, History.HistoryID

This sums up all of the rows before the current row.

You can't update straight from that, but you could put it into a CTE or insert the data into a temp table and then update from one of those.

The performance of it isn't great, but for a one off script in a maintenance window it was good enough for us.

Related Solutions

Sql-server – Best table schema to store daily hits for many items

I would want to attempt to insert a row for every hit and use SQL partitioning to store the table in managable chronological slices (by week or by month). This way, by keeping the original timestamp for each hit, you'll be able to aggregate the data using different methods as needed (by year one day, by hour the next, etc). For doing the aggregation, I would build a SSAS cube or Excel PivotTable, or T-SQL grouping functions. By using paritioning, you can store data for different time periods into individual filegroups spread across different disks to maintain scalability. When it comes to archival, you can perform what's called a sliding window to detach and later drop old unneeded time periods of data without expensive DELETE operations.

Sql-server – Need help with a Running Total Issue

You can calculate a running total in SQL 2008 R2 by using the ROW_NUMBER() window function along with a correlated sub-query to calculate the running total column.

Here is an example using some data I have available. You would need to amend the query accordingly to suit your tables/data and would likely need to use a PARTITION BY within the ROW_NUMBER function so you get a running total for the different projects/record types.

If you are able to post some sample data I can provide you with an example using your data structures.

WITH ContactMonthOrdered AS
(
    SELECT
            ContactMonthStartDate
        ,   COUNT(*) AS Total
        ,   ROW_NUMBER() OVER(ORDER BY ContactMonthStartDate) AS MonthOrder

    FROM
        Clinical.ContactDetail

    WHERE
        ContactFiscalYearLongName = '2014/2015'

    GROUP BY
        ContactMonthStartDate
)

SELECT
        ContactMonthStartDate
    ,   Total
    ,   MonthOrder
    ,   (
            SELECT  SUM(Total)
            FROM    ContactMonthOrdered
            WHERE   MonthOrder <= c.MonthOrder
        ) AS RunningTotal

FROM
    ContactMonthOrdered AS c

ORDER BY
    c.MonthOrder

UPDATED The query below is derived from the example query you posted in your question. Provided I understand your schema correctly, this will give you the total for each period for a project and also a running total across the periods for a project. The assumption is that the sequence of PeriodId is meaningful.

;WITH CommitmentPeriodCost AS
(
    SELECT
        Commitments.ProjectId
        , CommitmentDetails.PeriodId
        , SUM(CommitmentDetails.TotalCost) AS TotalCost

    FROM
        CostManagement_Commitments AS Commitments
        LEFT JOIN CostManagement_CommitmentDetails AS CommitmentDetails
            ON  Commitments.Id = CommitmentDetails.CommitmentId

    WHERE
        Commitments.ProjectId = 1

    GROUP BY
        Commitments.ProjectId
        , CommitmentDetails.PeriodId
)

SELECT
    ProjectId
    , PeriodId
    , TotalCost
    , (
            SELECT  SUM(TotalCost)
            FROM    CommitmentPeriodCost AS RunningCost
            WHERE   ProjectId = CommitmentPeriodCost.ProjectId
                    AND PeriodId <= CommitmentPeriodCost.PeriodId
    ) AS RunningTotal

FROM
    CommitmentPeriodCost

ORDER BY
    ProjectId
    , PeriodId

Best Answer

Related Solutions

Sql-server – Best table schema to store daily hits for many items

Sql-server – Need help with a Running Total Issue

Related Question