Running totals

Question

Post reply

Running totals

cidr

Ten Centuries

Points: 1353
More actions
August 26, 2011 at 4:50 am

#390282

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
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.
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

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

This article[/url] by Jeff Moden should put you in the right direction.

Hope this helps

Gianluca

-- Gianluca Sartori

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

cidr (8/26/2011)
That's fine and I have created the query like so
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

Yowch! That bit of code actually has two problems... the first one is the one you mention, the missing days. In order to get around that, you'll need to generate the missing days using a Tally Table or some equivalent code.

The second one is an insidious performance killer known as the "Triangular Join" and it can cause performance problems literally millions of times worse than a Cursor or While Loop. See the following article for information on the performance problems associated with "Triangular Joins" where aggregates are used.

http://www.sqlservercentral.com/articles/T-SQL/61539/

I've got to go to work in a couple of minutes so I can't take the time to provide a demo for your problem this instant, but I will get back to it tonight (if no one beats me to it). In the meantime, read the article that Gianluca pointed you to... it's the method I'll use to solve your problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cidr Ten Centuries Points: 1353 More actions · Answer 3

I appreciate the help Jeff.

I think I should add that day 2 does not exist in the current table at all. It would look more like this.

Day _______ Salesman______ActualSales_____CumulativeSales

1 _______ Bob ______ 10 ____________ 10

2 _______ Bob ______ 0 _____________10 record (day and salesman) does not exists

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'.

cheers

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

Yep... Thanks for the clarification but I got that the first time. I'll fix you up tonight.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

I had a thought about this last night and didn't want to proceed until I had an answer because, depending on the answer, we might be able to simplify your life quite a bit.

You're getting your data from a table call Net_Sales. The "Day" column is pretty much useless unless the Net_Sales table only contains one month of sales. With that in mind, does the Net_Sales table have something like a "Sales_Date" column (or combination of columns where we could synthesize one)? If so, please post the CREATE TABLE statement for the Net_Sales table.

If the Net_Sales table DOESN'T have anything like a "Sales_Date" column, does the "Net_Sales" table only contain 1 month of information? If not, do you have another source table for the sales data? If so, can you post the CREATE TABLE statement for that?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cidr Ten Centuries Points: 1353 More actions · Answer 6

Hi Jeff,

Please see below more info about the data and schema. The data I'm using has been extracted from a cube into a text file. the 'Day' column has the format 'yyyymmdd' and is the date of sales. As the data has been extracted from a cube, the data is a cumulation of sales for that day for each sales guy.

You've noticed that there is a productID. I wasn't going to include this as I was assuming pointers on how to create the running total would have sufficed. for the running total, it would be against Salesman and product for each day. For example, Salesman1 sells product1 on day1 and then he sells product1 on day two, you have a running total. but if he sells product2 on day2, that would be a new running total.

I've uploaded the text file to a table in SQL to perform the running total. The text file has several months data but within each month there are dates missing - mainly weekends but some other dates. The finished query results will be loaded into a table and back into a cube as it will be used for a graph (unfortunately the product we use does not have the ability to perform running totals). I was thinking of using a calendar table to accomodate the missing dates.

However, that said, if salesman1 sold product1 for £10 on day1 and did not sell anything on day2, I'd still like to see a record for salesman1 with the running total of £10, even if the day doesn't exist in the table, i'e, weekend date.

I hope that answers your question, please let me know if you require anything else. I had a look at this again but I'm stumped as to resolve it.

create table Net_Sales

(

ID int identity(1,1) primary key,

Day int,

SalesManID int,

ProductID int,

ActualSales decimal(18,10)

);

Format data for Day column

DaySalesManID ProductIDActualSales

20110811 1 4 145.1115

20110812 1 3 745.254

20110811 2 5 457.2541

20110813 2 4 47.2001

0

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

Perfect. Now that I have that information, this is actually a fairly simple task. I'll work on it after work tonight.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 8

Alright... I'm trying to simulate what you might have for data just to be sure performance is ok so I need to ask just a couple of more questions.

On average...

1. How many months will be in the table?

2. How many different sales people per month?

3. How many different products overall?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cidr Ten Centuries Points: 1353 More actions · Answer 9

I think we could break the query into separate months so each query would only show days of the same month and then load that into a table.

There is around 180 sales guys per month

There is around 2702 products sold per month.

I have done a distinct in Product and sales guy and there is 9578 records, approx per month

Hope that helps

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 10

To be clear on the "2702 products sold per month"... that's 2702 unique product IDs?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cidr Ten Centuries Points: 1353 More actions · Answer 11

That's right Jeff. I did a Distinct on the ProductID column and that was the amount of unique productID's that were returned.

Cheers

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 12

Sorry for peppering you with individual questions... how many total rows (orer of magnitude) do have in a given month ?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cidr Ten Centuries Points: 1353 More actions · Answer 13

cidr

Ten Centuries

Points: 1353

August 31, 2011 at 7:56 am

#1375488

There around 18000 rows in total.

🙂

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 14

Perfect. Thanks. I'll spend some time on it afterwork and try to git'er done.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)