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, SimonPleas 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...
>>I hope I've understood the requirements....
>|||That's quite the requirement! ;-) Try this and let me know if it does it
for you:
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
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
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)
go
create view MyView1
as
SELECT
sp.SalesPersonID
, d.SalesDate
, isnull (SUM(s1.DollarValue), 0.0) AS DailySales
FROM
dbo.SalesPersons sp
cross join dbo.Dates d
left join dbo.Sales s1 on s1.SalesPersonID = sp.SalesPersonID
and s1.SalesDate = d.SalesDate
group by
d.SalesDate
, sp.SalesPersonID
go
create view MyView2
as
select
MyView1.SalesPersonID
, MyView1.SalesDate
, MyView1.DailySales
, (select sum (v2.DailySales) from MyView1 as v2
where v2.SalesPersonID = MyView1.SalesPersonID
and v2.SalesDate <= MyView1.SalesDate) as CumulativeSales
from
MyView1
go
select
v1.SalesPersonID
, v1.SalesDate
, v1.CumulativeSales
, (select count (*) from MyView2 as v2
where v2.SalesDate = v1.SalesDate
and (v2.CumulativeSales > v1.CumulativeSales
or (v2.CumulativeSales = v1.CumulativeSales
and v2.SalesPersonID <= v1.SalesPersonID))
) as Rank
from
MyView2 v1
order by
v1.SalesDate
, Rank
go
drop view MyView1, MyView2
drop table SalesPersons, Sales, Dates
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:%23dpzC8K2FHA.3568@.TK2MSFTNGP15.phx.gbl...
> 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...
>>I hope I've understood the requirements....
>>
>|||Tom, thanks for all your time to crack this one !
When I tested your solution on a production-scale table with 10,000 records,
the third query timed out (not surprisingly) so I switched to stored
procedures and temporary tables based on your SQL syntax (see code below).
I guess this begs the question whether SELECT statements, while elegant, may
not be the most efficient solution. Perhaps I should be doing the
cumulative aggregating and ranking with CURSORS or in VB/C# etc. In
reality, because the final result set changes daily, I could just schedule
it on a nightly basis. The alternative would be to create a user table and
append to it daily. So many choices!
Thanks again - I learnt several good tricks from you.
Simon
ALTER PROCEDURE spSales
AS
IF NOT EXISTS(SELECT name FROM tempdb..sysobjects where name = '##tblSales')
CREATE TABLE ##tblSales (SalesPersonID int, SalesDate datetime, DailySales
money)
DELETE FROM ##tblSales
INSERT INTO ##tblSales (SalesPersonID, SalesDate, DailySales)
SELECT sp.SalesPersonID, d.SalesDate, ISNULL(SUM(s1.DollarValue), 0.0) AS
DailySales
FROM dbo.SalesPersons sp CROSS JOIN
dbo.Dates d LEFT OUTER JOIN
dbo.Sales s1 ON s1.SalesPersonID = sp.SalesPersonID
AND s1.SalesDate = d.SalesDate
GROUP BY d.SalesDate, sp.SalesPersonID
ORDER BY d.SalesDate, sp.SalesPersonID
IF NOT EXISTS(SELECT name FROM tempdb..sysobjects where name ='##tblSales2') CREATE TABLE ##tblSales2 (SalesPersonID int, SalesDate
datetime, DailySales money, CumulativeSales money)
DELETE FROM ##tblSales2
INSERT INTO ##tblSales2 (SalesPersonID, SalesDate, DailySales,
CumulativeSales)
SELECT SalesPersonID, SalesDate, DailySales,
(SELECT SUM(v2.DailySales)
FROM ##tblSales AS v2
WHERE v2.SalesPersonID =##tblSales.SalesPersonID AND v2.SalesDate <= ##tblSales.SalesDate) AS
CumulativeSales
FROM dbo.##tblSales
followed by...
ALTER PROCEDURE dbo.spSalesFinal
AS SELECT TOP 100 PERCENT SalesPersonID, SalesDate, CumulativeSales,
(SELECT COUNT(*)
FROM ##tblSales2 AS v2
WHERE v2.SalesDate = v1.SalesDate AND
(v2.CumulativeSales > v1.CumulativeSales OR
(v2.CumulativeSales =v1.CumulativeSales AND v2.SalesPersonID <= v1.SalesPersonID))) AS Rank
FROM dbo.[##tblSales2] v1
ORDER BY SalesDate, Rank|||Tom,
Just realized - what if CumulativeSales are identical for a date? How does
your solution treat equal rankings ie if there are two people tied for 4th,
the ranking should go 1,2,3,4,4,6,7,8,9,.....
Simon|||Although my view-based solution does give you what you want, I did expect
poor performance because of having to do the running totals essentially
twice. You could consider doing an indexed view for the basic daily
aggregations - thus speeding up MyView1. (Your idea of doing daily
aggregations would work here, too.) Also, given that MyView2 essentially
joins onto itself, a case could be made to populate a temp table once with
the contents of MyView2, index it appropriately and then run the final
query.
You definitely do NOT want a cursor. Also, Don't use SELECT TOP 100
PERCENT; it gives you nothing. BTW, why are you using global temp tables.
--
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:eHUTl6R2FHA.1188@.TK2MSFTNGP12.phx.gbl...
Tom, thanks for all your time to crack this one !
When I tested your solution on a production-scale table with 10,000 records,
the third query timed out (not surprisingly) so I switched to stored
procedures and temporary tables based on your SQL syntax (see code below).
I guess this begs the question whether SELECT statements, while elegant, may
not be the most efficient solution. Perhaps I should be doing the
cumulative aggregating and ranking with CURSORS or in VB/C# etc. In
reality, because the final result set changes daily, I could just schedule
it on a nightly basis. The alternative would be to create a user table and
append to it daily. So many choices!
Thanks again - I learnt several good tricks from you.
Simon
ALTER PROCEDURE spSales
AS
IF NOT EXISTS(SELECT name FROM tempdb..sysobjects where name = '##tblSales')
CREATE TABLE ##tblSales (SalesPersonID int, SalesDate datetime, DailySales
money)
DELETE FROM ##tblSales
INSERT INTO ##tblSales (SalesPersonID, SalesDate, DailySales)
SELECT sp.SalesPersonID, d.SalesDate, ISNULL(SUM(s1.DollarValue), 0.0) AS
DailySales
FROM dbo.SalesPersons sp CROSS JOIN
dbo.Dates d LEFT OUTER JOIN
dbo.Sales s1 ON s1.SalesPersonID = sp.SalesPersonID
AND s1.SalesDate = d.SalesDate
GROUP BY d.SalesDate, sp.SalesPersonID
ORDER BY d.SalesDate, sp.SalesPersonID
IF NOT EXISTS(SELECT name FROM tempdb..sysobjects where name ='##tblSales2') CREATE TABLE ##tblSales2 (SalesPersonID int, SalesDate
datetime, DailySales money, CumulativeSales money)
DELETE FROM ##tblSales2
INSERT INTO ##tblSales2 (SalesPersonID, SalesDate, DailySales,
CumulativeSales)
SELECT SalesPersonID, SalesDate, DailySales,
(SELECT SUM(v2.DailySales)
FROM ##tblSales AS v2
WHERE v2.SalesPersonID =##tblSales.SalesPersonID AND v2.SalesDate <= ##tblSales.SalesDate) AS
CumulativeSales
FROM dbo.##tblSales
followed by...
ALTER PROCEDURE dbo.spSalesFinal
AS SELECT TOP 100 PERCENT SalesPersonID, SalesDate, CumulativeSales,
(SELECT COUNT(*)
FROM ##tblSales2 AS v2
WHERE v2.SalesDate = v1.SalesDate AND
(v2.CumulativeSales > v1.CumulativeSales OR
(v2.CumulativeSales =v1.CumulativeSales AND v2.SalesPersonID <= v1.SalesPersonID))) AS Rank
FROM dbo.[##tblSales2] v1
ORDER BY SalesDate, Rank|||I went with your expected results, even though I thought it was odd to be
ranking people differently when they were tied. You can give people equal
ranks for equal cumulative totals with:
select
v1.SalesPersonID
, v1.SalesDate
, v1.CumulativeSales
, 1 + (select count (*) from MyView2 as v2
where v2.SalesDate = v1.SalesDate
and (v2.CumulativeSales > v1.CumulativeSales
)
) as Rank
from
MyView2 v1
order by
v1.SalesDate
, Rank
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:%233YkYhT2FHA.2816@.tk2msftngp13.phx.gbl...
Tom,
Just realized - what if CumulativeSales are identical for a date? How does
your solution treat equal rankings ie if there are two people tied for 4th,
the ranking should go 1,2,3,4,4,6,7,8,9,.....
Simon|||Fantastic Tom - Thank you for sharing!
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:e3S%23oWV2FHA.2444@.TK2MSFTNGP10.phx.gbl...
>I went with your expected results, even though I thought it was odd to be
> ranking people differently when they were tied. You can give people equal
> ranks for equal cumulative totals with:
> select
> v1.SalesPersonID
> , v1.SalesDate
> , v1.CumulativeSales
> , 1 + (select count (*) from MyView2 as v2
> where v2.SalesDate = v1.SalesDate
> and (v2.CumulativeSales > v1.CumulativeSales
> )
> ) as Rank
> from
> MyView2 v1
> order by
> v1.SalesDate
> , Rank
>
> --
> 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:%233YkYhT2FHA.2816@.tk2msftngp13.phx.gbl...
> Tom,
> Just realized - what if CumulativeSales are identical for a date? How
> does
> your solution treat equal rankings ie if there are two people tied for
> 4th,
> the ranking should go 1,2,3,4,4,6,7,8,9,.....
> Simon
>|||Tom,
I tried the following using your syntax for MyView1 and some BOL code but
got the error "Server: Msg 1936, Level 16, State 1, Line 1
Cannot index the view 'salesSQL.dbo.MyView1'. It contains one or more
disallowed constructs." Am I doing what you suggested properly? [I'll use
local temp tables.]
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF
GO
SET
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
ON
GO
create view MyView1 WITH SCHEMABINDING
as
SELECT
sp.SalesPersonID
, d.SalesDate
, isnull (SUM(s1.DollarValue), 0.0) AS DailySales
FROM
dbo.SalesPersons sp
cross join dbo.Dates d
left join dbo.Sales s1 on s1.SalesPersonID = sp.SalesPersonID
and s1.SalesDate = d.SalesDate
group by
d.SalesDate
, sp.SalesPersonID
go
--Create index on the view.
CREATE UNIQUE CLUSTERED INDEX IV1 ON MyView1(SalesDate, SalesPersonID)
GO
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OML4MUV2FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Although my view-based solution does give you what you want, I did expect
> poor performance because of having to do the running totals essentially
> twice. You could consider doing an indexed view for the basic daily
> aggregations - thus speeding up MyView1. (Your idea of doing daily
> aggregations would work here, too.) Also, given that MyView2 essentially
> joins onto itself, a case could be made to populate a temp table once with
> the contents of MyView2, index it appropriately and then run the final
> query.
> You definitely do NOT want a cursor. Also, Don't use SELECT TOP 100
> PERCENT; it gives you nothing. BTW, why are you using global temp tables.
> --
> 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:eHUTl6R2FHA.1188@.TK2MSFTNGP12.phx.gbl...
> Tom, thanks for all your time to crack this one !
> When I tested your solution on a production-scale table with 10,000
> records,
> the third query timed out (not surprisingly) so I switched to stored
> procedures and temporary tables based on your SQL syntax (see code below).
> I guess this begs the question whether SELECT statements, while elegant,
> may
> not be the most efficient solution. Perhaps I should be doing the
> cumulative aggregating and ranking with CURSORS or in VB/C# etc. In
> reality, because the final result set changes daily, I could just schedule
> it on a nightly basis. The alternative would be to create a user table
> and
> append to it daily. So many choices!
> Thanks again - I learnt several good tricks from you.
> Simon
>
> ALTER PROCEDURE spSales
> AS
> IF NOT EXISTS(SELECT name FROM tempdb..sysobjects where name => '##tblSales')
> CREATE TABLE ##tblSales (SalesPersonID int, SalesDate datetime,
> DailySales
> money)
> DELETE FROM ##tblSales
> INSERT INTO ##tblSales (SalesPersonID, SalesDate, DailySales)
> SELECT sp.SalesPersonID, d.SalesDate, ISNULL(SUM(s1.DollarValue), 0.0) AS
> DailySales
> FROM dbo.SalesPersons sp CROSS JOIN
> dbo.Dates d LEFT OUTER JOIN
> dbo.Sales s1 ON s1.SalesPersonID = sp.SalesPersonID
> AND s1.SalesDate = d.SalesDate
> GROUP BY d.SalesDate, sp.SalesPersonID
> ORDER BY d.SalesDate, sp.SalesPersonID
> IF NOT EXISTS(SELECT name FROM tempdb..sysobjects where name => '##tblSales2') CREATE TABLE ##tblSales2 (SalesPersonID int, SalesDate
> datetime, DailySales money, CumulativeSales money)
> DELETE FROM ##tblSales2
> INSERT INTO ##tblSales2 (SalesPersonID, SalesDate, DailySales,
> CumulativeSales)
> SELECT SalesPersonID, SalesDate, DailySales,
> (SELECT SUM(v2.DailySales)
> FROM ##tblSales AS v2
> WHERE v2.SalesPersonID => ##tblSales.SalesPersonID AND v2.SalesDate <= ##tblSales.SalesDate) AS
> CumulativeSales
> FROM dbo.##tblSales
> followed by...
> ALTER PROCEDURE dbo.spSalesFinal
> AS SELECT TOP 100 PERCENT SalesPersonID, SalesDate, CumulativeSales,
> (SELECT COUNT(*)
> FROM ##tblSales2 AS v2
> WHERE v2.SalesDate = v1.SalesDate AND
> (v2.CumulativeSales > v1.CumulativeSales OR
> (v2.CumulativeSales => v1.CumulativeSales AND v2.SalesPersonID <= v1.SalesPersonID))) AS Rank
> FROM dbo.[##tblSales2] v1
> ORDER BY SalesDate, Rank
>
>
>|||Oh, I just realized something. You can't use the LEFT JOIN in an indexed
view. Looks like you'll have to go the temp table route with that one, too.
:-( You can still index the temp table, though.
--
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:e$Bm6he2FHA.1576@.TK2MSFTNGP15.phx.gbl...
Tom,
I tried the following using your syntax for MyView1 and some BOL code but
got the error "Server: Msg 1936, Level 16, State 1, Line 1
Cannot index the view 'salesSQL.dbo.MyView1'. It contains one or more
disallowed constructs." Am I doing what you suggested properly? [I'll use
local temp tables.]
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF
GO
SET
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
ON
GO
create view MyView1 WITH SCHEMABINDING
as
SELECT
sp.SalesPersonID
, d.SalesDate
, isnull (SUM(s1.DollarValue), 0.0) AS DailySales
FROM
dbo.SalesPersons sp
cross join dbo.Dates d
left join dbo.Sales s1 on s1.SalesPersonID = sp.SalesPersonID
and s1.SalesDate = d.SalesDate
group by
d.SalesDate
, sp.SalesPersonID
go
--Create index on the view.
CREATE UNIQUE CLUSTERED INDEX IV1 ON MyView1(SalesDate, SalesPersonID)
GO
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OML4MUV2FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Although my view-based solution does give you what you want, I did expect
> poor performance because of having to do the running totals essentially
> twice. You could consider doing an indexed view for the basic daily
> aggregations - thus speeding up MyView1. (Your idea of doing daily
> aggregations would work here, too.) Also, given that MyView2 essentially
> joins onto itself, a case could be made to populate a temp table once with
> the contents of MyView2, index it appropriately and then run the final
> query.
> You definitely do NOT want a cursor. Also, Don't use SELECT TOP 100
> PERCENT; it gives you nothing. BTW, why are you using global temp tables.
> --
> 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:eHUTl6R2FHA.1188@.TK2MSFTNGP12.phx.gbl...
> Tom, thanks for all your time to crack this one !
> When I tested your solution on a production-scale table with 10,000
> records,
> the third query timed out (not surprisingly) so I switched to stored
> procedures and temporary tables based on your SQL syntax (see code below).
> I guess this begs the question whether SELECT statements, while elegant,
> may
> not be the most efficient solution. Perhaps I should be doing the
> cumulative aggregating and ranking with CURSORS or in VB/C# etc. In
> reality, because the final result set changes daily, I could just schedule
> it on a nightly basis. The alternative would be to create a user table
> and
> append to it daily. So many choices!
> Thanks again - I learnt several good tricks from you.
> Simon
>
> ALTER PROCEDURE spSales
> AS
> IF NOT EXISTS(SELECT name FROM tempdb..sysobjects where name => '##tblSales')
> CREATE TABLE ##tblSales (SalesPersonID int, SalesDate datetime,
> DailySales
> money)
> DELETE FROM ##tblSales
> INSERT INTO ##tblSales (SalesPersonID, SalesDate, DailySales)
> SELECT sp.SalesPersonID, d.SalesDate, ISNULL(SUM(s1.DollarValue), 0.0) AS
> DailySales
> FROM dbo.SalesPersons sp CROSS JOIN
> dbo.Dates d LEFT OUTER JOIN
> dbo.Sales s1 ON s1.SalesPersonID = sp.SalesPersonID
> AND s1.SalesDate = d.SalesDate
> GROUP BY d.SalesDate, sp.SalesPersonID
> ORDER BY d.SalesDate, sp.SalesPersonID
> IF NOT EXISTS(SELECT name FROM tempdb..sysobjects where name => '##tblSales2') CREATE TABLE ##tblSales2 (SalesPersonID int, SalesDate
> datetime, DailySales money, CumulativeSales money)
> DELETE FROM ##tblSales2
> INSERT INTO ##tblSales2 (SalesPersonID, SalesDate, DailySales,
> CumulativeSales)
> SELECT SalesPersonID, SalesDate, DailySales,
> (SELECT SUM(v2.DailySales)
> FROM ##tblSales AS v2
> WHERE v2.SalesPersonID => ##tblSales.SalesPersonID AND v2.SalesDate <= ##tblSales.SalesDate) AS
> CumulativeSales
> FROM dbo.##tblSales
> followed by...
> ALTER PROCEDURE dbo.spSalesFinal
> AS SELECT TOP 100 PERCENT SalesPersonID, SalesDate, CumulativeSales,
> (SELECT COUNT(*)
> FROM ##tblSales2 AS v2
> WHERE v2.SalesDate = v1.SalesDate AND
> (v2.CumulativeSales > v1.CumulativeSales OR
> (v2.CumulativeSales => v1.CumulativeSales AND v2.SalesPersonID <= v1.SalesPersonID))) AS Rank
> FROM dbo.[##tblSales2] v1
> ORDER BY SalesDate, Rank
>
>
>|||BTW, SQL Server 2005 has a RANK() function, which will obviate the need for
this type of code - and improve performance! :-)
--
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:eTekT1d2FHA.744@.TK2MSFTNGP10.phx.gbl...
Fantastic Tom - Thank you for sharing!
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:e3S%23oWV2FHA.2444@.TK2MSFTNGP10.phx.gbl...
>I went with your expected results, even though I thought it was odd to be
> ranking people differently when they were tied. You can give people equal
> ranks for equal cumulative totals with:
> select
> v1.SalesPersonID
> , v1.SalesDate
> , v1.CumulativeSales
> , 1 + (select count (*) from MyView2 as v2
> where v2.SalesDate = v1.SalesDate
> and (v2.CumulativeSales > v1.CumulativeSales
> )
> ) as Rank
> from
> MyView2 v1
> order by
> v1.SalesDate
> , Rank
>
> --
> 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:%233YkYhT2FHA.2816@.tk2msftngp13.phx.gbl...
> Tom,
> Just realized - what if CumulativeSales are identical for a date? How
> does
> your solution treat equal rankings ie if there are two people tied for
> 4th,
> the ranking should go 1,2,3,4,4,6,7,8,9,.....
> Simon
>|||One more thing. If you can handle the ranking bits in the front end (where
it belongs), then you should at least double the performance if you have to
stick with SQL Server 2000.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uPKHR2h2FHA.2232@.TK2MSFTNGP12.phx.gbl...
BTW, SQL Server 2005 has a RANK() function, which will obviate the need for
this type of code - and improve performance! :-)
--
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:eTekT1d2FHA.744@.TK2MSFTNGP10.phx.gbl...
Fantastic Tom - Thank you for sharing!
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:e3S%23oWV2FHA.2444@.TK2MSFTNGP10.phx.gbl...
>I went with your expected results, even though I thought it was odd to be
> ranking people differently when they were tied. You can give people equal
> ranks for equal cumulative totals with:
> select
> v1.SalesPersonID
> , v1.SalesDate
> , v1.CumulativeSales
> , 1 + (select count (*) from MyView2 as v2
> where v2.SalesDate = v1.SalesDate
> and (v2.CumulativeSales > v1.CumulativeSales
> )
> ) as Rank
> from
> MyView2 v1
> order by
> v1.SalesDate
> , Rank
>
> --
> 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:%233YkYhT2FHA.2816@.tk2msftngp13.phx.gbl...
> Tom,
> Just realized - what if CumulativeSales are identical for a date? How
> does
> your solution treat equal rankings ie if there are two people tied for
> 4th,
> the ranking should go 1,2,3,4,4,6,7,8,9,.....
> Simon
>|||One thing, though. You can still do the daily aggregates for each
salesperson in an indexed view. Cluster it on (SalesDate, SalesPersonID).
THEN, create a non-indexed view with the CROSS JOIN/LEFT JOIN and see how
that goes.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uge$n1h2FHA.700@.TK2MSFTNGP15.phx.gbl...
Oh, I just realized something. You can't use the LEFT JOIN in an indexed
view. Looks like you'll have to go the temp table route with that one, too.
:-( You can still index the temp table, though.
--
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:e$Bm6he2FHA.1576@.TK2MSFTNGP15.phx.gbl...
Tom,
I tried the following using your syntax for MyView1 and some BOL code but
got the error "Server: Msg 1936, Level 16, State 1, Line 1
Cannot index the view 'salesSQL.dbo.MyView1'. It contains one or more
disallowed constructs." Am I doing what you suggested properly? [I'll use
local temp tables.]
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF
GO
SET
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
ON
GO
create view MyView1 WITH SCHEMABINDING
as
SELECT
sp.SalesPersonID
, d.SalesDate
, isnull (SUM(s1.DollarValue), 0.0) AS DailySales
FROM
dbo.SalesPersons sp
cross join dbo.Dates d
left join dbo.Sales s1 on s1.SalesPersonID = sp.SalesPersonID
and s1.SalesDate = d.SalesDate
group by
d.SalesDate
, sp.SalesPersonID
go
--Create index on the view.
CREATE UNIQUE CLUSTERED INDEX IV1 ON MyView1(SalesDate, SalesPersonID)
GO
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OML4MUV2FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Although my view-based solution does give you what you want, I did expect
> poor performance because of having to do the running totals essentially
> twice. You could consider doing an indexed view for the basic daily
> aggregations - thus speeding up MyView1. (Your idea of doing daily
> aggregations would work here, too.) Also, given that MyView2 essentially
> joins onto itself, a case could be made to populate a temp table once with
> the contents of MyView2, index it appropriately and then run the final
> query.
> You definitely do NOT want a cursor. Also, Don't use SELECT TOP 100
> PERCENT; it gives you nothing. BTW, why are you using global temp tables.
> --
> 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:eHUTl6R2FHA.1188@.TK2MSFTNGP12.phx.gbl...
> Tom, thanks for all your time to crack this one !
> When I tested your solution on a production-scale table with 10,000
> records,
> the third query timed out (not surprisingly) so I switched to stored
> procedures and temporary tables based on your SQL syntax (see code below).
> I guess this begs the question whether SELECT statements, while elegant,
> may
> not be the most efficient solution. Perhaps I should be doing the
> cumulative aggregating and ranking with CURSORS or in VB/C# etc. In
> reality, because the final result set changes daily, I could just schedule
> it on a nightly basis. The alternative would be to create a user table
> and
> append to it daily. So many choices!
> Thanks again - I learnt several good tricks from you.
> Simon
>
> ALTER PROCEDURE spSales
> AS
> IF NOT EXISTS(SELECT name FROM tempdb..sysobjects where name => '##tblSales')
> CREATE TABLE ##tblSales (SalesPersonID int, SalesDate datetime,
> DailySales
> money)
> DELETE FROM ##tblSales
> INSERT INTO ##tblSales (SalesPersonID, SalesDate, DailySales)
> SELECT sp.SalesPersonID, d.SalesDate, ISNULL(SUM(s1.DollarValue), 0.0) AS
> DailySales
> FROM dbo.SalesPersons sp CROSS JOIN
> dbo.Dates d LEFT OUTER JOIN
> dbo.Sales s1 ON s1.SalesPersonID = sp.SalesPersonID
> AND s1.SalesDate = d.SalesDate
> GROUP BY d.SalesDate, sp.SalesPersonID
> ORDER BY d.SalesDate, sp.SalesPersonID
> IF NOT EXISTS(SELECT name FROM tempdb..sysobjects where name => '##tblSales2') CREATE TABLE ##tblSales2 (SalesPersonID int, SalesDate
> datetime, DailySales money, CumulativeSales money)
> DELETE FROM ##tblSales2
> INSERT INTO ##tblSales2 (SalesPersonID, SalesDate, DailySales,
> CumulativeSales)
> SELECT SalesPersonID, SalesDate, DailySales,
> (SELECT SUM(v2.DailySales)
> FROM ##tblSales AS v2
> WHERE v2.SalesPersonID => ##tblSales.SalesPersonID AND v2.SalesDate <= ##tblSales.SalesDate) AS
> CumulativeSales
> FROM dbo.##tblSales
> followed by...
> ALTER PROCEDURE dbo.spSalesFinal
> AS SELECT TOP 100 PERCENT SalesPersonID, SalesDate, CumulativeSales,
> (SELECT COUNT(*)
> FROM ##tblSales2 AS v2
> WHERE v2.SalesDate = v1.SalesDate AND
> (v2.CumulativeSales > v1.CumulativeSales OR
> (v2.CumulativeSales => v1.CumulativeSales AND v2.SalesPersonID <= v1.SalesPersonID))) AS Rank
> FROM dbo.[##tblSales2] v1
> ORDER BY SalesDate, Rank
>
>
>|||Yes, that is why I specified 2000 in my original post - I look forward to
the new features
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uPKHR2h2FHA.2232@.TK2MSFTNGP12.phx.gbl...
> BTW, SQL Server 2005 has a RANK() function, which will obviate the need
> for
> this type of code - and improve performance! :-)
> --
> 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:eTekT1d2FHA.744@.TK2MSFTNGP10.phx.gbl...
> Fantastic Tom - Thank you for sharing!
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:e3S%23oWV2FHA.2444@.TK2MSFTNGP10.phx.gbl...
>>I went with your expected results, even though I thought it was odd to be
>> ranking people differently when they were tied. You can give people
>> equal
>> ranks for equal cumulative totals with:
>> select
>> v1.SalesPersonID
>> , v1.SalesDate
>> , v1.CumulativeSales
>> , 1 + (select count (*) from MyView2 as v2
>> where v2.SalesDate = v1.SalesDate
>> and (v2.CumulativeSales > v1.CumulativeSales
>> )
>> ) as Rank
>> from
>> MyView2 v1
>> order by
>> v1.SalesDate
>> , Rank
>>
>> --
>> 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:%233YkYhT2FHA.2816@.tk2msftngp13.phx.gbl...
>> Tom,
>> Just realized - what if CumulativeSales are identical for a date? How
>> does
>> your solution treat equal rankings ie if there are two people tied for
>> 4th,
>> the ranking should go 1,2,3,4,4,6,7,8,9,.....
>> Simon
>>
>|||You mean on the client/application side and not in the database?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23EVYRGi2FHA.2268@.TK2MSFTNGP15.phx.gbl...
> One more thing. If you can handle the ranking bits in the front end
> (where
> it belongs), then you should at least double the performance if you have
> to
> stick with SQL Server 2000.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uPKHR2h2FHA.2232@.TK2MSFTNGP12.phx.gbl...
> BTW, SQL Server 2005 has a RANK() function, which will obviate the need
> for
> this type of code - and improve performance! :-)
> --
> 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:eTekT1d2FHA.744@.TK2MSFTNGP10.phx.gbl...
> Fantastic Tom - Thank you for sharing!
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:e3S%23oWV2FHA.2444@.TK2MSFTNGP10.phx.gbl...
>>I went with your expected results, even though I thought it was odd to be
>> ranking people differently when they were tied. You can give people
>> equal
>> ranks for equal cumulative totals with:
>> select
>> v1.SalesPersonID
>> , v1.SalesDate
>> , v1.CumulativeSales
>> , 1 + (select count (*) from MyView2 as v2
>> where v2.SalesDate = v1.SalesDate
>> and (v2.CumulativeSales > v1.CumulativeSales
>> )
>> ) as Rank
>> from
>> MyView2 v1
>> order by
>> v1.SalesDate
>> , Rank
>>
>> --
>> 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:%233YkYhT2FHA.2816@.tk2msftngp13.phx.gbl...
>> Tom,
>> Just realized - what if CumulativeSales are identical for a date? How
>> does
>> your solution treat equal rankings ie if there are two people tied for
>> 4th,
>> the ranking should go 1,2,3,4,4,6,7,8,9,.....
>> Simon
>>
>|||ok will do - tx
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uMsI4Fi2FHA.2232@.TK2MSFTNGP12.phx.gbl...
> One thing, though. You can still do the daily aggregates for each
> salesperson in an indexed view. Cluster it on (SalesDate, SalesPersonID).
> THEN, create a non-indexed view with the CROSS JOIN/LEFT JOIN and see how
> that goes.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uge$n1h2FHA.700@.TK2MSFTNGP15.phx.gbl...
> Oh, I just realized something. You can't use the LEFT JOIN in an indexed
> view. Looks like you'll have to go the temp table route with that one,
> too.
> :-( You can still index the temp table, though.
> --
> 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:e$Bm6he2FHA.1576@.TK2MSFTNGP15.phx.gbl...
> Tom,
> I tried the following using your syntax for MyView1 and some BOL code but
> got the error "Server: Msg 1936, Level 16, State 1, Line 1
> Cannot index the view 'salesSQL.dbo.MyView1'. It contains one or more
> disallowed constructs." Am I doing what you suggested properly? [I'll
> use
> local temp tables.]
> --Set the options to support indexed views.
> SET NUMERIC_ROUNDABORT OFF
> GO
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
> ON
> GO
> create view MyView1 WITH SCHEMABINDING
> as
> SELECT
> sp.SalesPersonID
> , d.SalesDate
> , isnull (SUM(s1.DollarValue), 0.0) AS DailySales
> FROM
> dbo.SalesPersons sp
> cross join dbo.Dates d
> left join dbo.Sales s1 on s1.SalesPersonID = sp.SalesPersonID
> and s1.SalesDate = d.SalesDate
> group by
> d.SalesDate
> , sp.SalesPersonID
> go
> --Create index on the view.
> CREATE UNIQUE CLUSTERED INDEX IV1 ON MyView1(SalesDate, SalesPersonID)
> GO
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OML4MUV2FHA.1420@.TK2MSFTNGP09.phx.gbl...
>> Although my view-based solution does give you what you want, I did expect
>> poor performance because of having to do the running totals essentially
>> twice. You could consider doing an indexed view for the basic daily
>> aggregations - thus speeding up MyView1. (Your idea of doing daily
>> aggregations would work here, too.) Also, given that MyView2 essentially
>> joins onto itself, a case could be made to populate a temp table once
>> with
>> the contents of MyView2, index it appropriately and then run the final
>> query.
>> You definitely do NOT want a cursor. Also, Don't use SELECT TOP 100
>> PERCENT; it gives you nothing. BTW, why are you using global temp
>> tables.
>> --
>> 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:eHUTl6R2FHA.1188@.TK2MSFTNGP12.phx.gbl...
>> Tom, thanks for all your time to crack this one !
>> When I tested your solution on a production-scale table with 10,000
>> records,
>> the third query timed out (not surprisingly) so I switched to stored
>> procedures and temporary tables based on your SQL syntax (see code
>> below).
>> I guess this begs the question whether SELECT statements, while elegant,
>> may
>> not be the most efficient solution. Perhaps I should be doing the
>> cumulative aggregating and ranking with CURSORS or in VB/C# etc. In
>> reality, because the final result set changes daily, I could just
>> schedule
>> it on a nightly basis. The alternative would be to create a user table
>> and
>> append to it daily. So many choices!
>> Thanks again - I learnt several good tricks from you.
>> Simon
>>
>> ALTER PROCEDURE spSales
>> AS
>> IF NOT EXISTS(SELECT name FROM tempdb..sysobjects where name =>> '##tblSales')
>> CREATE TABLE ##tblSales (SalesPersonID int, SalesDate datetime,
>> DailySales
>> money)
>> DELETE FROM ##tblSales
>> INSERT INTO ##tblSales (SalesPersonID, SalesDate, DailySales)
>> SELECT sp.SalesPersonID, d.SalesDate, ISNULL(SUM(s1.DollarValue), 0.0) AS
>> DailySales
>> FROM dbo.SalesPersons sp CROSS JOIN
>> dbo.Dates d LEFT OUTER JOIN
>> dbo.Sales s1 ON s1.SalesPersonID = sp.SalesPersonID
>> AND s1.SalesDate = d.SalesDate
>> GROUP BY d.SalesDate, sp.SalesPersonID
>> ORDER BY d.SalesDate, sp.SalesPersonID
>> IF NOT EXISTS(SELECT name FROM tempdb..sysobjects where name =>> '##tblSales2') CREATE TABLE ##tblSales2 (SalesPersonID int, SalesDate
>> datetime, DailySales money, CumulativeSales money)
>> DELETE FROM ##tblSales2
>> INSERT INTO ##tblSales2 (SalesPersonID, SalesDate, DailySales,
>> CumulativeSales)
>> SELECT SalesPersonID, SalesDate, DailySales,
>> (SELECT SUM(v2.DailySales)
>> FROM ##tblSales AS v2
>> WHERE v2.SalesPersonID =>> ##tblSales.SalesPersonID AND v2.SalesDate <= ##tblSales.SalesDate) AS
>> CumulativeSales
>> FROM dbo.##tblSales
>> followed by...
>> ALTER PROCEDURE dbo.spSalesFinal
>> AS SELECT TOP 100 PERCENT SalesPersonID, SalesDate, CumulativeSales,
>> (SELECT COUNT(*)
>> FROM ##tblSales2 AS v2
>> WHERE v2.SalesDate = v1.SalesDate AND
>> (v2.CumulativeSales > v1.CumulativeSales OR
>> (v2.CumulativeSales =>> v1.CumulativeSales AND v2.SalesPersonID <= v1.SalesPersonID))) AS Rank
>> FROM dbo.[##tblSales2] v1
>> ORDER BY SalesDate, Rank
>>
>>
>>
>|||Yes.
--
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:%23GkLqoq2FHA.3108@.tk2msftngp13.phx.gbl...
You mean on the client/application side and not in the database?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23EVYRGi2FHA.2268@.TK2MSFTNGP15.phx.gbl...
> One more thing. If you can handle the ranking bits in the front end
> (where
> it belongs), then you should at least double the performance if you have
> to
> stick with SQL Server 2000.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uPKHR2h2FHA.2232@.TK2MSFTNGP12.phx.gbl...
> BTW, SQL Server 2005 has a RANK() function, which will obviate the need
> for
> this type of code - and improve performance! :-)
> --
> 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:eTekT1d2FHA.744@.TK2MSFTNGP10.phx.gbl...
> Fantastic Tom - Thank you for sharing!
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:e3S%23oWV2FHA.2444@.TK2MSFTNGP10.phx.gbl...
>>I went with your expected results, even though I thought it was odd to be
>> ranking people differently when they were tied. You can give people
>> equal
>> ranks for equal cumulative totals with:
>> select
>> v1.SalesPersonID
>> , v1.SalesDate
>> , v1.CumulativeSales
>> , 1 + (select count (*) from MyView2 as v2
>> where v2.SalesDate = v1.SalesDate
>> and (v2.CumulativeSales > v1.CumulativeSales
>> )
>> ) as Rank
>> from
>> MyView2 v1
>> order by
>> v1.SalesDate
>> , Rank
>>
>> --
>> 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:%233YkYhT2FHA.2816@.tk2msftngp13.phx.gbl...
>> Tom,
>> Just realized - what if CumulativeSales are identical for a date? How
>> does
>> your solution treat equal rankings ie if there are two people tied for
>> 4th,
>> the ranking should go 1,2,3,4,4,6,7,8,9,.....
>> Simon
>>
>|||Same here. Nov 7. :-)
--
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:%23o179nq2FHA.3964@.TK2MSFTNGP10.phx.gbl...
Yes, that is why I specified 2000 in my original post - I look forward to
the new features
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uPKHR2h2FHA.2232@.TK2MSFTNGP12.phx.gbl...
> BTW, SQL Server 2005 has a RANK() function, which will obviate the need
> for
> this type of code - and improve performance! :-)
> --
> 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:eTekT1d2FHA.744@.TK2MSFTNGP10.phx.gbl...
> Fantastic Tom - Thank you for sharing!
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:e3S%23oWV2FHA.2444@.TK2MSFTNGP10.phx.gbl...
>>I went with your expected results, even though I thought it was odd to be
>> ranking people differently when they were tied. You can give people
>> equal
>> ranks for equal cumulative totals with:
>> select
>> v1.SalesPersonID
>> , v1.SalesDate
>> , v1.CumulativeSales
>> , 1 + (select count (*) from MyView2 as v2
>> where v2.SalesDate = v1.SalesDate
>> and (v2.CumulativeSales > v1.CumulativeSales
>> )
>> ) as Rank
>> from
>> MyView2 v1
>> order by
>> v1.SalesDate
>> , Rank
>>
>> --
>> 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:%233YkYhT2FHA.2816@.tk2msftngp13.phx.gbl...
>> Tom,
>> Just realized - what if CumulativeSales are identical for a date? How
>> does
>> your solution treat equal rankings ie if there are two people tied for
>> 4th,
>> the ranking should go 1,2,3,4,4,6,7,8,9,.....
>> Simon
>>
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment