Running totals issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Running totals issue

Author

Topic

cidr
Posting Yak Master

207 Posts

Posted - 2011-08-26 : 09:43:44

Hi there,

I have a problem with running totals.

Lets say I have a salesman 'bob' who sells a product on day 1 for £10. Then on day 2 he sells a product for £10. This would look like this


Day______ActualSales_____CumulativeSales
1   ______ 10 ____________ 10
2   ______ 10 ____________ 20

That's fine and I have created the query like so (albeit with a fairly low performance technique)


SELECT a.Day, a.Salesman, sum(Sales),

  (select sum(Sales) 
   from  Net_Sales b 
   where b.DaY <= a.DaY 
        and a.Salesman = b.Salesman
  ) as Cumulative
  
FROM 
        Net_Sales a

GROUP BY 
  a.DaY,
  a.Salesman

However, the sales are accumulated for each month, so for every day there should be an amount. If salesman 'bob' makes £10 on day 1, and nothing on day 2 and then makes £10 on day 3 I'd like it to look like this


Day______ActualSales_____CumulativeSales
1   ______ 10 ____________ 10
2   ______  0 _____________10
3   ______ 10 ____________ 20

I'm unsure how to do this because at the moment I get this


Day______ActualSales_____CumulativeSales
1   ______ 10 ____________ 10
3   ______ 10 ____________ 20

Day 2 is missing. This is because there is absolutely no entry for salesman 'bob' for day2.

I'd like to find a way to have day two in there and also have the totals running from the cumulation of previous days even if there is not a sale made that day. I have an idea that I can list all days using a date table and a cross join or left join from the date table to the above query but I don't know how to keep the totals running.

Does anyone know of a way to do this??

many thanks in advance

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-26 : 10:03:00

join to a sequence table (or a calendar table). This can fill in the missing gaps. Just treat NULL's arising from the LEFT JOIN to Net_Sales as 0

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-26 : 10:10:09

This might work:


DECLARE @days AS TABLE (
	[day] INT PRIMARY KEY
	)

INSERT @days ([day])
SELECT ROW_NUMBER() OVER (ORDER BY a.[a])
FROM
		   ( SELECT 1 AS [a] UNION SELECT 2 AS [a] ) AS a
CROSS JOIN ( SELECT 1 AS [a] UNION SELECT 2 AS [a] ) AS b
CROSS JOIN ( SELECT 1 AS [a] UNION SELECT 2 AS [a] ) AS c
CROSS JOIN ( SELECT 1 AS [a] UNION SELECT 2 AS [a] ) AS d
CROSS JOIN ( SELECT 1 AS [a] UNION SELECT 2 AS [a] ) AS e
CROSS JOIN ( SELECT 1 AS [a] UNION SELECT 2 AS [a] ) AS f

SELECT * FROM @days

; WITH daySales AS (
	SELECT
		d.[day] AS [Day]
		, s.[Salesman] AS [Salesman]
		, ISNULL(n.[Sales], 0) AS  AS [Sales]
	FROM
		@days AS d
		CROSS JOIN ( SELECT DISTINCT [Salesman] AS [Salesman] FROM Net_Sales ) AS s
		LEFT JOIN Net_Sales AS n ON
			n.[Day] = d.[day]
			n.[Salesman] = s.[Salesman]
	)

SELECT a.Day, a.Salesman, sum(Sales),

  (select sum(Sales) 
   from  daySales b 
   where b.DaY <= a.DaY 
        and a.Salesman = b.Salesman
  ) as Cumulative
  
FROM 
        daySales a

GROUP BY 
  a.DaY,
  a.Salesman

The CTE has a pretty horrible cludge to generate days for all your salespeople. Assuming you have a normalised design that has a row per salesperson then just join to that instead.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-26 : 10:12:59

As an aside.

Denali will have a running aggregate function that means that we will finally be able to do this kind of stuff without resorting to horrible workarounds or the dark arts of quirky updates.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

cidr
Posting Yak Master

207 Posts

Posted - 2011-08-26 : 11:06:50

Hi Charlie and thanks for your help.

I understand your query to give me all days of a particular month and 0 for days where there are no records (sales).

I mentioned that I have used a date table and cross join already and the problem is not returning all days, regardless of sales, but the issue is keeping the total running. so, where there are no entries and the for a particular day, I want to see the total for that day and not 0. If that makes sense. the total has to keep running even on no sales days.

Is this something you can help me with?

Cheers

sanjnep
Posting Yak Master

191 Posts

Posted - 2011-08-26 : 12:01:42

create table Sales1 (DY int, ActualSale int)

go

insert into Sales1 values (1,10)
insert into Sales1 values (2,0)
insert into Sales1 values (3,10)

go

select a.DY as Day,a.ActualSale,sum(b.ActualSale) as CumulativeSales
from Sales1 a inner join Sales1 b
on (b.DY <= a.DY)
group by a.DY,a.ActualSale
order by a.DY,a.ActualSale

Sanjeev Shrestha
12/17/1971

cidr
Posting Yak Master

207 Posts

Posted - 2011-08-26 : 12:25:06

Hi Sanjnep, and thanks

I should have elaborated that day 2 does not exist in the current table. It would look more like this.

Day _______ Salesman______ActualSales_____CumulativeSales

1 _______ Bob ______ 10 ____________ 10
2 _______ Bob ______ 0 _____________10
3 _______ Bob ______ 10 ____________ 20

First of all, there can be days missing in the month so I can add them in with a cross join to a date table. However, that would result in 0. as there are no records for the Salesman bob for day 2, I have to find a way to add his cumulative (10 on day1) to day2 which doesn't exist 'yet'.

cidr
Posting Yak Master

207 Posts

Posted - 2011-08-26 : 12:59:49

Transact Charlie 08/26/2011
wrote

Denali will have a running aggregate function



About time:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources