CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012window-functions
TheGameiswar
TheGameiswar

Reputation: 28890

Calculate running totals for multiple dates

Please see bottom of question for test data..

I have data like below..

salesorder  datee
1          2016-11-10
1          2016-11-10
2          2016-11-09
2          2016-11-09

Now i want to display my data like below

datee       Summ
2016-11-09  4
2016-11-10  6

So i have used below query ,which gives me the exact result

select distinct 
datee,
sum(salesorder) over (order by datee)
from
#test

But the issue with above query is,it uses Range option and uses Disk spool(if work table has records ,then it is Disk based spool) ,and not in memory spool..You can verify the same using below

set statistics io on
select distinct 
datee,
sum(salesorder) over (order by datee)
from
#test

Table 'Worktable'. Scan count 3, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#test_______________________________________________________________________________________________________________00000001A8B8'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

One way to get InMemory spool is to use Rows option like below

select 
datee,
sum(salesorder) over ( order by datee rows between  unbounded preceding and  current row )
from
#test

The above query uses an InMemory spool and is very fast,but the output is not what i want..below is the output

datee       summ
2016-11-09  2
2016-11-09  4
2016-11-10  5
2016-11-10  6

InMemoryspool is very fast compared to Disk spool.this is documented here:Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions (Developer Reference)

So my question is :
How can i use InMemory Spool and get the result i want.I tried with Various framing options,but i am not getting desired result

Test data: 

create table #test
(
salesorder int,
datee date
)
go

insert into #test
select 1,getdate()-1
go 2


insert into #test
select 2,getdate()-2
go 2

Note :
Rows option will work ,if i don't have duplicate dates,but my data set up is like that and i have thousands of records like the sample

Update:
this is part of a large query ,so i prefer window functions (with out CTE or derived tables) to avoid rewriting

Upvotes: 2

Views: 138

Answers (2)

Steve Ford
Steve Ford

Reputation: 7753

How about using this:

SELECT  datee, 
        sum(salesorder) over 
            ( order by datee rows between  unbounded preceding and  current row )
FROM (
      SELECT datee,
             sum(salesorder) as salesorder
      from #test
      GROUP BY datee
     ) t

This gives the following:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#test_______________________________________________________________________________________________________________0000000000A3'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Edit:

If you don't want to use this technique, then try a variation of your own query wrapping it with Row_NUMBER(), still uses IN Memeory:

SELECT datee, salesorder
FROM (
      SELECT datee, salesorder, 
             ROW_NUMBER() OVER (PARTITION BY datee ORDER BY salesorder DESC) AS RN
      FROM (
              SELECT  datee,
                      sum(salesorder) over ( order by datee rows between  unbounded preceding and  current row ) As salesorder
              FROM #test
          ) t1
  ) t2
WHERE RN = 1

Upvotes: 1

Cato
Cato

Reputation: 3701

select distinct T1.dattee,X.Summ from SalesTab T1
    CROSS APPLY (SELECT SUM(T2.SalesOrder) SUMM FROM SalesTab T2 WHERE T2.dattee<=T1.dattee) X
    ORDER BY T1.Dattee

Upvotes: 1

Related Questions