CODEWITHSUNDEEP
sqlsql-server-2008cumulative-sum
PeterH
PeterH

Reputation: 1040

Calculate running total without using Rows Unbounded Preceding in 2008

I have the below query that can calculate a running total in SQL Server 2016

Select
    BranchNo
    ,FiscalWeek
    ,SalesExVAT
    ,Sum(SalesExVAT) Over (Partition By BranchNo Order By FiscalWeek Rows Unbounded Preceding) As cumulative_sales
From 
    [EUUKSQL01].[DASHBOARD].[dbo].[SalesAggregateWeek]

Where 
    BranchNO in (1,4,7)
    And FiscalYear = 2017

However, I have now been tasked with using it on an old server that only uses Server 2008, and the function wasn't available then.

I have tried a self join to recreate it, but it can only work for a single branch as i cant seem to partition the data as i did in the above.

Is there an alternative i could use ?

This is what i tried with the self join:

Select  
    sa.BranchNo
    ,sa.FiscalWeek
    ,sa.SalesExVAT
    ,Sum(sb.SalesExVAT) As cumulative_sales

From
    [EUUKSQL01].[DASHBOARD].[dbo].[SalesAggregateWeek] sa

Inner Join
    [EUUKSQL01].[DASHBOARD].[dbo].[SalesAggregateWeek] sb
    On sa.FiscalWeek >= sb.FiscalWeek

Where
    sa.FiscalYear = 2017
    And sa.BranchNo = 1
    And sb.FiscalYear = 2017
    And sb.BranchNo = 1

Group By
    sa.BranchNo
    ,sa.FiscalWeek
    ,sa.SalesExVAT

Order By
    sa.BranchNo
    ,sa.FiscalWeek
    ,sa.SalesExVAT

Upvotes: 0

Views: 874

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270371

I tend to use outer apply or a correlated subquery for this purpose. The equivalent to your original query should be:

select daw.BranchNo, daw.FiscalWeek, daw.SalesExVAT,
       (select Sum(daw2.SalesExVAT)
        from [EUUKSQL01].[DASHBOARD].[dbo].[SalesAggregateWeek] daw2
        where daw2.BranchNo = daw.BranchNo and
              daw2.FiscalYear = daw.FiscalYear and
              daw2.FiscalWeek <= daw.FiscalWeek
       ) As cumulative_sales
from [EUUKSQL01].[DASHBOARD].[dbo].[SalesAggregateWeek] daw
where daw.BranchNO in (1, 4, 7) and daw.FiscalYear = 2017;

This assumes that the BranchNo/FiscalYear/FiscalWeek combination is unique in the table (which seems like a reasonable assumption).

Upvotes: 1

Related Questions