Cumulative Aggregate Query

Hi,
I have a tables of sales with fields: SalesID, SalesPersonID and
DollarValue. I want to determine a cumulative daily sales total for each
sales person. The query I have right now is :
SELECT SalesPersonID, SalesDate,
CumulativeSales=CASE
WHEN SalesPersonID = SalesPersonID THEN
(SELECT SUM(DollarValue) FROM Sales s WHERE s.SalesPersonID =
Sales.SalesPersonID AND s.SalesID <= Sales.SalesID)
END
FROM Sales
I have two questions:
1. Is there a more efficient way of writing this query?
2. How would I write a query that _ranks_ sales persons by day? (I'm using
SQL Server 2000, not 2005)
Thanks, Simon
Pleas post your DDL + INSERT statements of sample data + expected results.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Simon Shutter" <a@.b.com> wrote in message
news:eh$jaMy1FHA.3724@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a tables of sales with fields: SalesID, SalesPersonID and
DollarValue. I want to determine a cumulative daily sales total for each
sales person. The query I have right now is :
SELECT SalesPersonID, SalesDate,
CumulativeSales=CASE
WHEN SalesPersonID = SalesPersonID THEN
(SELECT SUM(DollarValue) FROM Sales s WHERE s.SalesPersonID =
Sales.SalesPersonID AND s.SalesID <= Sales.SalesID)
END
FROM Sales
I have two questions:
1. Is there a more efficient way of writing this query?
2. How would I write a query that _ranks_ sales persons by day? (I'm using
SQL Server 2000, not 2005)
Thanks, Simon
|||On Sat, 22 Oct 2005 09:18:23 -0700, Simon Shutter wrote:

>Hi,
>I have a tables of sales with fields: SalesID, SalesPersonID and
>DollarValue. I want to determine a cumulative daily sales total for each
>sales person. The query I have right now is :
>SELECT SalesPersonID, SalesDate,
>CumulativeSales=CASE
> WHEN SalesPersonID = SalesPersonID THEN
> (SELECT SUM(DollarValue) FROM Sales s WHERE s.SalesPersonID =
>Sales.SalesPersonID AND s.SalesID <= Sales.SalesID)
> END
>FROM Sales
>I have two questions:
>1. Is there a more efficient way of writing this query?
Hi Simon,
You could get rid of the CASE, since the WHEN clause is always true
anyway:
SELECT SalesPersonID, SalesDate,
(SELECT SUM(DollarValue)
FROM Sales AS s
WHERE s.SalesPersonID = Sales.SalesPersonID
AND s.SalesID <= Sales.SalesID) AS CumulativeSales
FROM Sales
Or you could try if the following performs faster:
SELECT s1.SalesPersonID, s1.SalesDate,
SUM(s2.DollarValue) AS CumulativeSales
FROM Sales AS s1
INNER JOIN Sales AS s2
ON s2.SalesPersonID = s1.SalesPersonID
AND s2.SalesID <= s1.SalesID
GROUP BY s1.SalesPersonID, s1.SalesDate

>2. How would I write a query that _ranks_ sales persons by day? (I'm using
>SQL Server 2000, not 2005)
See Tom's reply. And check www.aspfaq.com/5006 for more details.
Or alternatively, use Google to try and find the answers to similar
questions asked previously in these groups, and see if you're able to
adopt the answers to your situation. Questions on how to add ranking in
SQL Server 2000 are are recurring subject in the SQL Server groups.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Tom and Hugo,
Thanks for the suggestions and pointers to etiquette. Below I have posted
my DDL, insert statements (hypothetical data), simplified query and results.
Ideally, I would like to be able to rank the results by Sales Date ASC and
CumulativeSales DESC. The complicating factor is that not every
SalesPersonID has one or more records per SalesDate so I'm not sure how to
carry forward the rankings on those days.
Simon
************************************************** ************
Here's my DDL...
CREATE TABLE [Sales] (
[SalesID] [int] NOT NULL ,
[SalesPersonID] [int] NOT NULL ,
[SalesDate] [datetime] NOT NULL ,
[DollarValue] [money] NOT NULL ,
CONSTRAINT [PK_Sales_1] PRIMARY KEY CLUSTERED
(
[SalesID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
And some insert statements...
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(1,4,'Jan 01 2005',490)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(2,4,'Jan 01 2005',756)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(3,5,'Jan 01 2005',178)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(4,5,'Jan 01 2005',933)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(5,5,'Jan 01 2005',933)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(6,1,'Jan 02 2005',181)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(7,4,'Jan 02 2005',439)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(8,1,'Jan 03 2005',255)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(9,1,'Jan 03 2005',38)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(10,2,'Jan 03 2005',490)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(11,3,'Jan 03 2005',736)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(12,4,'Jan 03 2005',879)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(13,4,'Jan 03 2005',368)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(14,4,'Jan 03 2005',726)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(15,4,'Jan 03 2005',102)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(16,4,'Jan 03 2005',184)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(17,1,'Jan 04 2005',248)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(18,2,'Jan 04 2005',86)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(19,4,'Jan 04 2005',460)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(20,4,'Jan 04 2005',265)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(21,5,'Jan 04 2005',664)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(22,1,'Jan 05 2005',207)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(23,1,'Jan 05 2005',178)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(24,4,'Jan 05 2005',613)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(25,2,'Jan 06 2005',204)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(26,5,'Jan 06 2005',664)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(27,2,'Jan 07 2005',511)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(28,3,'Jan 07 2005',322)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(29,5,'Jan 07 2005',894)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(30,2,'Jan 08 2005',81)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(31,3,'Jan 08 2005',352)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(32,4,'Jan 08 2005',675)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(33,4,'Jan 08 2005',593)
INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
(34,4,'Jan 08 2005',613)
The modified query suggested by Hugo is...
AS SELECT SalesPersonID, SalesDate,
(SELECT SUM(DollarValue)
FROM Sales AS s
WHERE s.SalesPersonID = Sales.SalesPersonID
AND s.SalesID <= Sales.SalesID) AS CumulativeSales
FROM dbo.Sales
Results....
SalesPersonID SalesDate CumulativeSales
4 01-Jan-05 $490.00
4 01-Jan-05 $1,246.00
5 01-Jan-05 $178.00
5 01-Jan-05 $1,111.00
5 01-Jan-05 $2,044.00
1 02-Jan-05 $181.00
4 02-Jan-05 $1,685.00
1 03-Jan-05 $436.00
1 03-Jan-05 $474.00
2 03-Jan-05 $490.00
3 03-Jan-05 $736.00
4 03-Jan-05 $2,564.00
4 03-Jan-05 $2,932.00
4 03-Jan-05 $3,658.00
4 03-Jan-05 $3,760.00
4 03-Jan-05 $3,944.00
1 04-Jan-05 $722.00
2 04-Jan-05 $576.00
4 04-Jan-05 $4,404.00
4 04-Jan-05 $4,669.00
5 04-Jan-05 $2,708.00
1 05-Jan-05 $929.00
1 05-Jan-05 $1,107.00
4 05-Jan-05 $5,282.00
2 06-Jan-05 $780.00
5 06-Jan-05 $3,372.00
2 07-Jan-05 $1,291.00
3 07-Jan-05 $1,058.00
5 07-Jan-05 $4,266.00
2 08-Jan-05 $1,372.00
3 08-Jan-05 $1,410.00
4 08-Jan-05 $5,957.00
4 08-Jan-05 $6,550.00
4 08-Jan-05 $7,163.00
|||I hope I've understood the requirements. You'll need a table of dates and
another of sales persons:
create table SalesPersons
(
SalesPersonID int identity
primary key
)
go
insert SalesPersons default values
insert SalesPersons default values
insert SalesPersons default values
insert SalesPersons default values
insert SalesPersons default values
go
create table Dates
(
SalesDate datetime primary key
)
go
insert Dates values ('20050101')
insert Dates values ('20050102')
insert Dates values ('20050103')
insert Dates values ('20050104')
insert Dates values ('20050105')
insert Dates values ('20050106')
insert Dates values ('20050107')
insert Dates values ('20050108')
go
SELECT sp.SalesPersonID, d.SalesDate, isnull (s1.DollarValue, 0)
DollarValue,
isnull ((SELECT SUM(s.DollarValue)
FROM Sales AS s
WHERE s.SalesPersonID = sp.SalesPersonID
AND s.SalesID <= s1.SalesID), 0) AS CumulativeSales
FROM
dbo.SalesPersons sp
cross join dbo.Dates d
left join dbo.Sales s1 on s1.SalesPersonID = sp.SalesPersonID
and s1.SalesDate = d.SalesDate
order by
d.SalesDate
, sp.SalesPersonID
, CumulativeSales desc
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Simon Shutter" <a@.b.com> wrote in message
news:e5zBN641FHA.464@.TK2MSFTNGP15.phx.gbl...
> Tom and Hugo,
> Thanks for the suggestions and pointers to etiquette. Below I have posted
> my DDL, insert statements (hypothetical data), simplified query and
> results. Ideally, I would like to be able to rank the results by Sales
> Date ASC and CumulativeSales DESC. The complicating factor is that not
> every SalesPersonID has one or more records per SalesDate so I'm not sure
> how to carry forward the rankings on those days.
> Simon
> ************************************************** ************
> Here's my DDL...
> CREATE TABLE [Sales] (
> [SalesID] [int] NOT NULL ,
> [SalesPersonID] [int] NOT NULL ,
> [SalesDate] [datetime] NOT NULL ,
> [DollarValue] [money] NOT NULL ,
> CONSTRAINT [PK_Sales_1] PRIMARY KEY CLUSTERED
> (
> [SalesID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> And some insert statements...
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (1,4,'Jan 01 2005',490)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (2,4,'Jan 01 2005',756)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (3,5,'Jan 01 2005',178)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (4,5,'Jan 01 2005',933)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (5,5,'Jan 01 2005',933)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (6,1,'Jan 02 2005',181)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (7,4,'Jan 02 2005',439)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (8,1,'Jan 03 2005',255)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (9,1,'Jan 03 2005',38)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (10,2,'Jan 03 2005',490)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (11,3,'Jan 03 2005',736)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (12,4,'Jan 03 2005',879)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (13,4,'Jan 03 2005',368)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (14,4,'Jan 03 2005',726)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (15,4,'Jan 03 2005',102)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (16,4,'Jan 03 2005',184)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (17,1,'Jan 04 2005',248)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (18,2,'Jan 04 2005',86)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (19,4,'Jan 04 2005',460)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (20,4,'Jan 04 2005',265)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (21,5,'Jan 04 2005',664)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (22,1,'Jan 05 2005',207)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (23,1,'Jan 05 2005',178)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (24,4,'Jan 05 2005',613)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (25,2,'Jan 06 2005',204)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (26,5,'Jan 06 2005',664)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (27,2,'Jan 07 2005',511)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (28,3,'Jan 07 2005',322)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (29,5,'Jan 07 2005',894)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (30,2,'Jan 08 2005',81)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (31,3,'Jan 08 2005',352)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (32,4,'Jan 08 2005',675)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (33,4,'Jan 08 2005',593)
> INSERT INTO Sales (SalesID, SalesPersonID, SalesDate, DollarValue) VALUES
> (34,4,'Jan 08 2005',613)
> The modified query suggested by Hugo is...
> AS SELECT SalesPersonID, SalesDate,
> (SELECT SUM(DollarValue)
> FROM Sales AS s
> WHERE s.SalesPersonID =
> Sales.SalesPersonID AND s.SalesID <= Sales.SalesID) AS CumulativeSales
> FROM dbo.Sales
> Results....
> SalesPersonID SalesDate CumulativeSales
> 4 01-Jan-05 $490.00
> 4 01-Jan-05 $1,246.00
> 5 01-Jan-05 $178.00
> 5 01-Jan-05 $1,111.00
> 5 01-Jan-05 $2,044.00
> 1 02-Jan-05 $181.00
> 4 02-Jan-05 $1,685.00
> 1 03-Jan-05 $436.00
> 1 03-Jan-05 $474.00
> 2 03-Jan-05 $490.00
> 3 03-Jan-05 $736.00
> 4 03-Jan-05 $2,564.00
> 4 03-Jan-05 $2,932.00
> 4 03-Jan-05 $3,658.00
> 4 03-Jan-05 $3,760.00
> 4 03-Jan-05 $3,944.00
> 1 04-Jan-05 $722.00
> 2 04-Jan-05 $576.00
> 4 04-Jan-05 $4,404.00
> 4 04-Jan-05 $4,669.00
> 5 04-Jan-05 $2,708.00
> 1 05-Jan-05 $929.00
> 1 05-Jan-05 $1,107.00
> 4 05-Jan-05 $5,282.00
> 2 06-Jan-05 $780.00
> 5 06-Jan-05 $3,372.00
> 2 07-Jan-05 $1,291.00
> 3 07-Jan-05 $1,058.00
> 5 07-Jan-05 $4,266.00
> 2 08-Jan-05 $1,372.00
> 3 08-Jan-05 $1,410.00
> 4 08-Jan-05 $5,957.00
> 4 08-Jan-05 $6,550.00
> 4 08-Jan-05 $7,163.00
>
|||On Sat, 22 Oct 2005 22:07:27 -0700, Simon Shutter wrote:

>Tom and Hugo,
>Thanks for the suggestions and pointers to etiquette. Below I have posted
>my DDL, insert statements (hypothetical data), simplified query and results.
>Ideally, I would like to be able to rank the results by Sales Date ASC and
>CumulativeSales DESC. The complicating factor is that not every
>SalesPersonID has one or more records per SalesDate so I'm not sure how to
>carry forward the rankings on those days.
Hi Simon,
First: thanks for posting the CREATE TABLE and INSERT statments. It
helps when you can easily cut and paste code in order to run some tests!
Either Tom misunderstands your requirements, or I do - or maybe even
both. If neither of our soultions fit your requirements, then please
indicate what results you'd like to have from the sample data you
posted, to help us unuderstand what you need (and to allow us to compare
the output from our queries with your requirements).
(snip)
>The modified query suggested by Hugo is...
>AS SELECT SalesPersonID, SalesDate,
> (SELECT SUM(DollarValue)
> FROM Sales AS s
> WHERE s.SalesPersonID = Sales.SalesPersonID
>AND s.SalesID <= Sales.SalesID) AS CumulativeSales
>FROM dbo.Sales
>Results....
>SalesPersonID SalesDate CumulativeSales
>4 01-Jan-05 $490.00
>4 01-Jan-05 $1,246.00
(snip)
In my previous post, I forgot to include a GROUP BY for the query. That
leads to multiple rows per SalesPerson per SalesDate.
The query below does include the GROUP BY. It also includes an ORDER BY
to display the results ranked on ascending salesdate and descending
cumulative sales.
SELECT a.SalesPersonID, a.SalesDate,
SUM(b.DollarValue) AS CumulativeSales
FROM dbo.Sales AS a
INNER JOIN dbo.Sales AS b
ON b.SalesPersonID = a.SalesPersonID
AND b.SalesID <= a.SalesID
GROUP BY a.SalesPersonID, a.SalesDate
ORDER BY a.SalesDate ASC, CumulativeSales DESC
Results (time portion of date and white space removed for readability):
SalesPersonID SalesDate CumulativeSales
-- -- --
5 2005-01-01 3333.0000
4 2005-01-01 1736.0000
4 2005-01-02 1685.0000
1 2005-01-02 181.0000
4 2005-01-03 16858.0000
1 2005-01-03 910.0000
3 2005-01-03 736.0000
2 2005-01-03 490.0000
4 2005-01-04 9073.0000
5 2005-01-04 2708.0000
1 2005-01-04 722.0000
2 2005-01-04 576.0000
4 2005-01-05 5282.0000
1 2005-01-05 2036.0000
5 2005-01-06 3372.0000
2 2005-01-06 780.0000
5 2005-01-07 4266.0000
2 2005-01-07 1291.0000
3 2005-01-07 1058.0000
4 2005-01-08 19670.0000
3 2005-01-08 1410.0000
2 2005-01-08 1372.0000
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Tom,
Thank you for your query. I figured I'd have to use a pivot table with an
outer join for the dates to pad the results so thanks for confirming that.
What you suggested works well _except_ that if a SalesPersonID has no sales
on a particular SalesDate the CumulativeSales for that date is 0. The error
doesn't carry forward - ie if a day without sales is followed by one with
sales, the cumulative amount is still aggregated correctly.
Also, I would prefer to have a single CumulativeSales value per SalesDate;
however, I'm not sure how to aggregate on the subquery. The alternative
seems to be to create a view, VIEW_Tom based on your query, and running a
new query with a MAX(CumulativeSales) and grouping on SalesDate and
SalesPersonID:
SELECT SalesPersonID, SalesDate, MAX(CumulativeSales) AS CumulativeSales
from VIEW_Tom
Group By SalesPersonID, SalesDate
ORDER BY SalesDate, CumulativeSales DESC
Finally, when I used the term "rank", I really meant that I was hoping to
have an integer rank for each SalesPersonID per SalesDate so that I get the
results:
SalesPersonID SalesDate CumulativeSales Rank
5 01-Jan-05 $2,044.00 1
4 01-Jan-05 $1,246.00 2
1 01-Jan-05 $0.00 3
2 01-Jan-05 $0.00 4
3 01-Jan-05 $0.00 5
5 02-Jan-05 $2,044.00 1
4 02-Jan-05 $1,685.00 2
1 02-Jan-05 $181.00 3
2 02-Jan-05 $0.00 4
3 02-Jan-05 $0.00 5
4 03-Jan-05 $3,944.00 1
5 03-Jan-05 $2,044.00 2
3 03-Jan-05 $736.00 3
2 03-Jan-05 $490.00 4
1 03-Jan-05 $474.00 5
4 04-Jan-05 $4,669.00 1
5 04-Jan-05 $2,708.00 2
3 04-Jan-05 $736.00 3
1 04-Jan-05 $722.00 4
2 04-Jan-05 $576.00 5
4 05-Jan-05 $5,282.00 1
5 05-Jan-05 $2,708.00 2
1 05-Jan-05 $1,107.00 3
3 05-Jan-05 $736.00 4
2 05-Jan-05 $576.00 5
4 06-Jan-05 $5,282.00 1
5 06-Jan-05 $3,372.00 2
1 06-Jan-05 $1,107.00 3
2 06-Jan-05 $780.00 4
3 06-Jan-05 $736.00 5
4 07-Jan-05 $5,282.00 1
5 07-Jan-05 $4,266.00 2
2 07-Jan-05 $1,291.00 3
1 07-Jan-05 $1,107.00 4
3 07-Jan-05 $1,058.00 5
4 08-Jan-05 $7,163.00 1
5 08-Jan-05 $4,266.00 2
3 08-Jan-05 $1,410.00 3
2 08-Jan-05 $1,372.00 4
1 08-Jan-05 $1,107.00 5
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%238G1rK91FHA.3780@.TK2MSFTNGP12.phx.gbl...
>I hope I've understood the requirements....
|||Hugo
Thanks again for your comments - unfortunately, the "GROUP BY" has affected
the aggregating and is resulting in incorrect results (see your results
versus mine). I have posted a response to Tom's query that includes the
ideal result set I am hoping for.
Simon
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:413ol1d0c6ifbjtpp2vm66c9655c0mrm47@.4ax.com...

> Hi Simon,
> First: thanks for posting the CREATE TABLE and INSERT statments. It
> helps when you can easily cut and paste code in order to run some tests!
> Either Tom misunderstands your requirements, or I do - or maybe even
> both. If neither of our soultions fit your requirements, then please
> indicate what results you'd like to have from the sample data you
> posted, to help us unuderstand what you need (and to allow us to compare
> the output from our queries with your requirements).
>
[deleted text]

> In my previous post, I forgot to include a GROUP BY for the query. That
> leads to multiple rows per SalesPerson per SalesDate.
> The query below does include the GROUP BY. It also includes an ORDER BY
> to display the results ranked on ascending salesdate and descending
> cumulative sales.
> SELECT a.SalesPersonID, a.SalesDate,
> SUM(b.DollarValue) AS CumulativeSales
> FROM dbo.Sales AS a
> INNER JOIN dbo.Sales AS b
> ON b.SalesPersonID = a.SalesPersonID
> AND b.SalesID <= a.SalesID
> GROUP BY a.SalesPersonID, a.SalesDate
> ORDER BY a.SalesDate ASC, CumulativeSales DESC
> Results (time portion of date and white space removed for readability):
> SalesPersonID SalesDate CumulativeSales
> -- -- --
> 5 2005-01-01 3333.0000
> 4 2005-01-01 1736.0000
> 4 2005-01-02 1685.0000
> 1 2005-01-02 181.0000
> 4 2005-01-03 16858.0000
> 1 2005-01-03 910.0000
> 3 2005-01-03 736.0000
> 2 2005-01-03 490.0000
> 4 2005-01-04 9073.0000
> 5 2005-01-04 2708.0000
> 1 2005-01-04 722.0000
> 2 2005-01-04 576.0000
> 4 2005-01-05 5282.0000
> 1 2005-01-05 2036.0000
> 5 2005-01-06 3372.0000
> 2 2005-01-06 780.0000
> 5 2005-01-07 4266.0000
> 2 2005-01-07 1291.0000
> 3 2005-01-07 1058.0000
> 4 2005-01-08 19670.0000
> 3 2005-01-08 1410.0000
> 2 2005-01-08 1372.0000
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||So this running total that you want is over all days and you want it per
person, is it?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Simon Shutter" <a@.b.com> wrote in message
news:eE9tmQG2FHA.904@.tk2msftngp13.phx.gbl...
Tom,
Thank you for your query. I figured I'd have to use a pivot table with an
outer join for the dates to pad the results so thanks for confirming that.
What you suggested works well _except_ that if a SalesPersonID has no sales
on a particular SalesDate the CumulativeSales for that date is 0. The error
doesn't carry forward - ie if a day without sales is followed by one with
sales, the cumulative amount is still aggregated correctly.
Also, I would prefer to have a single CumulativeSales value per SalesDate;
however, I'm not sure how to aggregate on the subquery. The alternative
seems to be to create a view, VIEW_Tom based on your query, and running a
new query with a MAX(CumulativeSales) and grouping on SalesDate and
SalesPersonID:
SELECT SalesPersonID, SalesDate, MAX(CumulativeSales) AS CumulativeSales
from VIEW_Tom
Group By SalesPersonID, SalesDate
ORDER BY SalesDate, CumulativeSales DESC
Finally, when I used the term "rank", I really meant that I was hoping to
have an integer rank for each SalesPersonID per SalesDate so that I get the
results:
SalesPersonID SalesDate CumulativeSales Rank
5 01-Jan-05 $2,044.00 1
4 01-Jan-05 $1,246.00 2
1 01-Jan-05 $0.00 3
2 01-Jan-05 $0.00 4
3 01-Jan-05 $0.00 5
5 02-Jan-05 $2,044.00 1
4 02-Jan-05 $1,685.00 2
1 02-Jan-05 $181.00 3
2 02-Jan-05 $0.00 4
3 02-Jan-05 $0.00 5
4 03-Jan-05 $3,944.00 1
5 03-Jan-05 $2,044.00 2
3 03-Jan-05 $736.00 3
2 03-Jan-05 $490.00 4
1 03-Jan-05 $474.00 5
4 04-Jan-05 $4,669.00 1
5 04-Jan-05 $2,708.00 2
3 04-Jan-05 $736.00 3
1 04-Jan-05 $722.00 4
2 04-Jan-05 $576.00 5
4 05-Jan-05 $5,282.00 1
5 05-Jan-05 $2,708.00 2
1 05-Jan-05 $1,107.00 3
3 05-Jan-05 $736.00 4
2 05-Jan-05 $576.00 5
4 06-Jan-05 $5,282.00 1
5 06-Jan-05 $3,372.00 2
1 06-Jan-05 $1,107.00 3
2 06-Jan-05 $780.00 4
3 06-Jan-05 $736.00 5
4 07-Jan-05 $5,282.00 1
5 07-Jan-05 $4,266.00 2
2 07-Jan-05 $1,291.00 3
1 07-Jan-05 $1,107.00 4
3 07-Jan-05 $1,058.00 5
4 08-Jan-05 $7,163.00 1
5 08-Jan-05 $4,266.00 2
3 08-Jan-05 $1,410.00 3
2 08-Jan-05 $1,372.00 4
1 08-Jan-05 $1,107.00 5
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%238G1rK91FHA.3780@.TK2MSFTNGP12.phx.gbl...
>I hope I've understood the requirements....
|||Yes, per person and day over all days
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23VDt4OJ2FHA.1132@.TK2MSFTNGP10.phx.gbl...
> So this running total that you want is over all days and you want it per
> person, is it?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Simon Shutter" <a@.b.com> wrote in message
> news:eE9tmQG2FHA.904@.tk2msftngp13.phx.gbl...
> Tom,
> Thank you for your query. I figured I'd have to use a pivot table with an
> outer join for the dates to pad the results so thanks for confirming that.
> What you suggested works well _except_ that if a SalesPersonID has no
> sales
> on a particular SalesDate the CumulativeSales for that date is 0. The
> error
> doesn't carry forward - ie if a day without sales is followed by one with
> sales, the cumulative amount is still aggregated correctly.
> Also, I would prefer to have a single CumulativeSales value per SalesDate;
> however, I'm not sure how to aggregate on the subquery. The alternative
> seems to be to create a view, VIEW_Tom based on your query, and running a
> new query with a MAX(CumulativeSales) and grouping on SalesDate and
> SalesPersonID:
> SELECT SalesPersonID, SalesDate, MAX(CumulativeSales) AS CumulativeSales
> from VIEW_Tom
> Group By SalesPersonID, SalesDate
> ORDER BY SalesDate, CumulativeSales DESC
> Finally, when I used the term "rank", I really meant that I was hoping to
> have an integer rank for each SalesPersonID per SalesDate so that I get
> the
> results:
> SalesPersonID SalesDate CumulativeSales Rank
> 5 01-Jan-05 $2,044.00 1
> 4 01-Jan-05 $1,246.00 2
> 1 01-Jan-05 $0.00 3
> 2 01-Jan-05 $0.00 4
> 3 01-Jan-05 $0.00 5
> 5 02-Jan-05 $2,044.00 1
> 4 02-Jan-05 $1,685.00 2
> 1 02-Jan-05 $181.00 3
> 2 02-Jan-05 $0.00 4
> 3 02-Jan-05 $0.00 5
> 4 03-Jan-05 $3,944.00 1
> 5 03-Jan-05 $2,044.00 2
> 3 03-Jan-05 $736.00 3
> 2 03-Jan-05 $490.00 4
> 1 03-Jan-05 $474.00 5
> 4 04-Jan-05 $4,669.00 1
> 5 04-Jan-05 $2,708.00 2
> 3 04-Jan-05 $736.00 3
> 1 04-Jan-05 $722.00 4
> 2 04-Jan-05 $576.00 5
> 4 05-Jan-05 $5,282.00 1
> 5 05-Jan-05 $2,708.00 2
> 1 05-Jan-05 $1,107.00 3
> 3 05-Jan-05 $736.00 4
> 2 05-Jan-05 $576.00 5
> 4 06-Jan-05 $5,282.00 1
> 5 06-Jan-05 $3,372.00 2
> 1 06-Jan-05 $1,107.00 3
> 2 06-Jan-05 $780.00 4
> 3 06-Jan-05 $736.00 5
> 4 07-Jan-05 $5,282.00 1
> 5 07-Jan-05 $4,266.00 2
> 2 07-Jan-05 $1,291.00 3
> 1 07-Jan-05 $1,107.00 4
> 3 07-Jan-05 $1,058.00 5
> 4 08-Jan-05 $7,163.00 1
> 5 08-Jan-05 $4,266.00 2
> 3 08-Jan-05 $1,410.00 3
> 2 08-Jan-05 $1,372.00 4
> 1 08-Jan-05 $1,107.00 5
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%238G1rK91FHA.3780@.TK2MSFTNGP12.phx.gbl...
>