Cursors vs. Queries: total

Showing posts with label total. Show all posts

Thursday, March 29, 2012

Cursor question

I have a table with the following columns Parent_Part (PP), Child_part (CP),
Need_Date (ND), Reqd_qty (RQ), QTY_On_Hand (QOH). QOH is total qty in stock
that has been reserved for the parent part and is to be allocated to the
child based on the need date per PP/CP group. The sample table looks like
this after it's sorted based on allocation criteria. Your help will be
greated appreciated. TIA.
NOTE: I have not been using my hotmail account and I may have to activate
it so for now please post your reply here.
PP CP ND RQ QOH ALLOC
A A1 3/1/05 5 7 ?
A A1 4/1/05 5 7 ?
A A4 3/10/03 1 0 ?
B B3 3/1/05 10 6 ?
B B3 5/15/05 2 6 ?
B B3 6/1/05 2 6 ?
Result should look like this
PP CP ND RQ QOH ALLOC
A A1 3/1/05 5 7 5
A A1 4/1/05 5 7 2
A A4 3/10/03 1 0 0
B B3 3/1/05 10 6 6
B B3 5/15/05 2 6 0
B B3 6/1/05 2 6 0Hi
See http://www.aspfaq.com/etiquett__e.asp?id=5006 on how to make more
useful posts.
This assume that all PP, CP and ND combinations are unique!
SELECT q.PP, q.CP, q.ND, q.RQ, q.QOH,
CASE WHEN q.QOH - r.rq > 0 THEN
CASE WHEN q.QOH - r.rq - q.rq < 0 THEN q.QOH - r.rq
ELSE q.rq
END
ELSE 0
END AS ALLOC
from #qry q
JOIN (
SELECT b.pp, b.cp, b.nd, ISNULL(SUM(a.RQ),0) AS RQ
FROM #qry a
RIGHT JOIN #qry b ON b.pp = a.pp and b.cp = a.cp and a.nd < b.nd
GROUP BY b.pp, b.cp, b.nd
) r ON q.pp = r.pp and q.cp = r.cp and q.nd = r.nd
ORDER BY q.pp, q.cp, q.nd
John
"danlin" wrote:

> I have a table with the following columns Parent_Part (PP), Child_part (CP
),
> Need_Date (ND), Reqd_qty (RQ), QTY_On_Hand (QOH). QOH is total qty in sto
ck
> that has been reserved for the parent part and is to be allocated to the
> child based on the need date per PP/CP group. The sample table looks like
> this after it's sorted based on allocation criteria. Your help will be
> greated appreciated. TIA.
> NOTE: I have not been using my hotmail account and I may have to activate
> it so for now please post your reply here.
> PP CP ND RQ QOH ALLOC
> A A1 3/1/05 5 7 ?
> A A1 4/1/05 5 7 ?
> A A4 3/10/03 1 0 ?
> B B3 3/1/05 10 6 ?
> B B3 5/15/05 2 6 ?
> B B3 6/1/05 2 6 ?
> Result should look like this
> PP CP ND RQ QOH ALLOC
> A A1 3/1/05 5 7 5
> A A1 4/1/05 5 7 2
> A A4 3/10/03 1 0 0
> B B3 3/1/05 10 6 6
> B B3 5/15/05 2 6 0
> B B3 6/1/05 2 6 0
>

Sunday, March 25, 2012

Cursor in an SP

I am doing a compare last history query that I'm seeing only a cursor as a
way to do.
Data to return: custID, CustRegion, # orders, Total volume,
#monthsExpectedUse
1) Look at all orders by customer within a date range. ( I get col 1)
Greates cursor
2) Establish prior order per customer outside of the dates, and give time
diff for their ( I get cols 3,4,5)
How do I combine inital data @.CustAccount, @.qty, @.TotVolume, @.MonthsUse into
a final return set?It might be that a cursor is the best way, but that is rarely the case, and
even more rare that it is the only way. Could you provide detailed specs,
sample data, and desired results? This way, we can probably come up with an
alternative to a cursor which will be much more efficient and easier to
maintain. Please see http://www.aspfaq.com/5006
"Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
news:uQf6lajrFHA.3264@.TK2MSFTNGP12.phx.gbl...
>I am doing a compare last history query that I'm seeing only a cursor as a
>way to do.
> Data to return: custID, CustRegion, # orders, Total volume,
> #monthsExpectedUse
> 1) Look at all orders by customer within a date range. ( I get col 1)
> Greates cursor
> 2) Establish prior order per customer outside of the dates, and give time
> diff for their ( I get cols 3,4,5)
> How do I combine inital data @.CustAccount, @.qty, @.TotVolume, @.MonthsUse
> into a final return set?
>|||
*** Sent via Developersdex http://www.examnotes.net ***|||> *** Sent via Developersdex http://www.examnotes.net ***
Nice one. You might try a newsreader, they're a bit more reliable.|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OvknW5jrFHA.2588@.tk2msftngp13.phx.gbl...
> Nice one. You might try a newsreader, they're a bit more reliable.
Crud!|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OvknW5jrFHA.2588@.tk2msftngp13.phx.gbl...
> Nice one. You might try a newsreader, they're a bit more reliable.
Can a select with in a select return 2 columns?|||> Can a select with in a select return 2 columns?
Once again, you will need to be more specific. If you post table structure,
sample data, and what you are trying to do, it will be much easier than
answering word problems...|||"Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
news:OWokKDkrFHA.3080@.TK2MSFTNGP15.phx.gbl...
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OvknW5jrFHA.2588@.tk2msftngp13.phx.gbl...
> Can a select with in a select return 2 columns?
I want : returns 2 columns here not 1
Select a.col1, a.col2, (select b.col3, b.col4 from table orders b where b.id
= a.col1 and b.id2 = a.col2)
From orders a
Left join ....
Where ...
Group by ...
Order by ...|||I do not understand what "returns 2 columns here not 1" means.
***PLEASE*** GO READ http://www.aspfaq.com/5006
"Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
news:Owz2IIkrFHA.2552@.TK2MSFTNGP10.phx.gbl...
> "Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
> news:OWokKDkrFHA.3080@.TK2MSFTNGP15.phx.gbl...
> I want : returns 2 columns here not 1
> Select a.col1, a.col2, (select b.col3, b.col4 from table orders b where
> b.id = a.col1 and b.id2 = a.col2)
> From orders a
> Left join ....
> Where ...
> Group by ...
> Order by ...
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Sunday, March 11, 2012

Currency Format

The $ must appears only on certain total levels and has to be in the same position in the column regardless of the value for financial reporting. In other words, I don't want the $ to float next the most significant digit in the value. See the example below from Excel. Using an IIF statement I can concatenate the $ to the string when it is a certain group level. However, I have tried many various formatting strings but can't find one that results in a fixed number of characters so that the $ will always appears in the same position in the column.

Anyone have a way to do this?

General Government

222

3.45%

$ 405,590,654

1.98%

Public Buildings

194

3.01%

375,453,314

1.83%

Other Facilities

0.34%

21,324,140

0.1%

Property Acquisition

0.09%

8,813,200

0.04%

Grand Total

6,439

100.0%

$ 20,507,977,298

100.0%

I did get this to work by adding a column to the grouping. That somehow causes the heading size to increase (?) and takes much more time than coding a format code in an expression (I have many reports to do).

Can you try something like this in your field's expression:

="$" & Space(30 - len(Fields!Total.Value)) & Fields!Total.Value

The 30 in my example would have to be higher than the maximum number of digits in your Total field. This will put spaces between the $ and the actual number you are showing, but only enough to make the $ symbols align.

Hope this helps.

Jarret

|||

This is close, but the proportional font keeps the $ from lining up.

I am not a VB programmer so I have a little trouble finding the appropriate string and format functions.

Thanks for your reply.

|||

Could you change to a fixed-width font so that it would line up?

Or, you could add a column to the left of the field that has the $ symbol in it, but only for the group header and footer. This way, it would always line up.

Jarret

|||

I did the add a column and it causes the group headers to grow and be ugly. I am hoping for a solution where I can code something in the expression. If not, I may have someone code me a little VB routine to use.

Thanks.

Currency

Hello I am a newbe to sql and i am shure my problem is simple but hope somone can help.
My Total comes out like $20.00 and i want it to come out like 20.00
Simple but frustraiting
Any help apprieciated
ColinThis is a formatting issue, and thus does not fall under the scope of the SQL Engine. What application/interface are you using to display the value, and what datatype are you using?|||Thanks for your reply

The data type is set to money and its a web site built with Dreamweaver and it outputs in explorer if thats what you mean.

Thanks again

Colin|||The SQL Money data type just defines how the data is stored in SQL Server. Four significant digits held to the right of the decimal point. It has nothing to do with how it is formatted.

You need to check Dream Weaver's help file for assistance, though Dream Weaver may just check your system defaults.|||Thanks again I will do that, but I think I have been here befor.

Best regards,

Colin

Cumulative total in matrix

Hi,
For columns in a matrix, I need to add the row value to the row value in
previous column. Is there a way to do this?
Here is what I want the matrix results to look like:
year1 year2 year3 year4
Amount 1 3 4 7
Total 1 4 8 15
Year is the column group value and amount is the row group value. Total is
the field I am looking for how to calculate.
Thanks!Initially I thought you were after a running total, but looking at your
example that doesn't appear to be the case.
You will have to do this in the underlying query by including the two
consecutive year's values on the same row; i.e.
Select A.Year, Sum(A.Value), (Select Sum(B.Value) From MyTable B Where
B.Year = A.Year-1)
From MyTable A
Group By A.Year
(I'm not sure this is syntactically correct, but you get my drift)
Having said that, mathematically what you're showing would appear
incorrect, effectively the year2 total is included in the year4 total
twice. What are you trying to show?
Chris
Me wrote:
> Hi,
> For columns in a matrix, I need to add the row value to the row value
> in previous column. Is there a way to do this?
> Here is what I want the matrix results to look like:
> year1 year2 year3 year4
> Amount 1 3 4 7
> Total 1 4 8 15
> Year is the column group value and amount is the row group value.
> Total is the field I am looking for how to calculate.
> Thanks!|||Hi Chris,
Thanks for replying.
What I wrote is a little unclear but my example is correct. They want to
add the current year to the running total of the previous years. Does that
make sense?
Thanks,
Melissa
"Chris McGuigan" wrote:
> Initially I thought you were after a running total, but looking at your
> example that doesn't appear to be the case.
> You will have to do this in the underlying query by including the two
> consecutive year's values on the same row; i.e.
> Select A.Year, Sum(A.Value), (Select Sum(B.Value) From MyTable B Where
> B.Year = A.Year-1)
> From MyTable A
> Group By A.Year
> (I'm not sure this is syntactically correct, but you get my drift)
> Having said that, mathematically what you're showing would appear
> incorrect, effectively the year2 total is included in the year4 total
> twice. What are you trying to show?
> Chris
>
> Me wrote:
> > Hi,
> >
> > For columns in a matrix, I need to add the row value to the row value
> > in previous column. Is there a way to do this?
> >
> > Here is what I want the matrix results to look like:
> >
> > year1 year2 year3 year4
> > Amount 1 3 4 7
> > Total 1 4 8 15
> >
> > Year is the column group value and amount is the row group value.
> > Total is the field I am looking for how to calculate.
> >
> > Thanks!
>|||Melissa, Yes that makes sense (logically not statistically), but I
think you may have mis-interpreted it.
In the example you have given, year 3 is neither a running total nor a
grand total. It is year1 + year2 + year1 + year3, that's the effect of
what you are doing.
So they want the current year added to each past year? I still can't
see a reason for that, but hey! the customers always right!
You are better off doing this in the query. Matrix controls like fairly
simply structured data, so hide the complexity in the query, on the
lines of my original post.
The more I look at this, the more I think you probably just need a
running total. If that is the case use =RunningValue( ... ) in the cell.
Chris
Me wrote:
> Hi Chris,
> Thanks for replying.
> What I wrote is a little unclear but my example is correct. They
> want to add the current year to the running total of the previous
> years. Does that make sense?
> Thanks,
> Melissa
>
> "Chris McGuigan" wrote:
> > Initially I thought you were after a running total, but looking at
> > your example that doesn't appear to be the case.
> >
> > You will have to do this in the underlying query by including the
> > two consecutive year's values on the same row; i.e.
> > Select A.Year, Sum(A.Value), (Select Sum(B.Value) From MyTable B
> > Where B.Year = A.Year-1)
> > From MyTable A
> > Group By A.Year
> > (I'm not sure this is syntactically correct, but you get my drift)
> >
> > Having said that, mathematically what you're showing would appear
> > incorrect, effectively the year2 total is included in the year4
> > total twice. What are you trying to show?
> >
> > Chris
> >
> >
> >
> > Me wrote:
> >
> > > Hi,
> > >
> > > For columns in a matrix, I need to add the row value to the row
> > > value in previous column. Is there a way to do this?
> > >
> > > Here is what I want the matrix results to look like:
> > >
> > > year1 year2 year3 year4
> > > Amount 1 3 4 7
> > > Total 1 4 8 15
> > >
> > > Year is the column group value and amount is the row group value.
> > > Total is the field I am looking for how to calculate.
> > >
> > > Thanks!
> >
> >

Cumulative Total

I try to sum a value in the TABLE FOOTER if this record falls into a
particular condition. But the result is zero.
The formular is as follow:
=Sum(IIf(Fields!EQP_LEN.Value = 20 And Fields!EQP_TYPE.Value = "GP",
Fields!CN_WGT.Value, 0))
If I the formular like this:
=Sum(IIf(Fields!EQP_LEN.Value = 20 And Fields!EQP_TYPE.Value = "GP", 1, 0))
It works !!!
What's wrong ?What's the datatype of Fields!CN_WGT.Value? I would assume it is not a
System.Int32, but 0 is a System.Int32.
You might want to try 0.0 instead of 0:
=Sum(IIf(Fields!EQP_LEN.Value = 20 And Fields!EQP_TYPE.Value = "GP",
Fields!CN_WGT.Value, 0.0))
Alternatively:
=Sum(IIf(Fields!EQP_LEN.Value = 20 And Fields!EQP_TYPE.Value = "GP",
CDbl(Fields!CN_WGT.Value), 0.0))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
news:26102E03-4F38-4F38-872E-633F5E02B78B@.microsoft.com...
> I try to sum a value in the TABLE FOOTER if this record falls into a
> particular condition. But the result is zero.
> The formular is as follow:
> =Sum(IIf(Fields!EQP_LEN.Value = 20 And Fields!EQP_TYPE.Value = "GP",
> Fields!CN_WGT.Value, 0))
> If I the formular like this:
> =Sum(IIf(Fields!EQP_LEN.Value = 20 And Fields!EQP_TYPE.Value = "GP", 1,
0))
> It works !!!
> What's wrong ?|||Thanks, it works now !!!
"Robert Bruckner [MSFT]" wrote:
> What's the datatype of Fields!CN_WGT.Value? I would assume it is not a
> System.Int32, but 0 is a System.Int32.
> You might want to try 0.0 instead of 0:
> =Sum(IIf(Fields!EQP_LEN.Value = 20 And Fields!EQP_TYPE.Value = "GP",
> Fields!CN_WGT.Value, 0.0))
> Alternatively:
> =Sum(IIf(Fields!EQP_LEN.Value = 20 And Fields!EQP_TYPE.Value = "GP",
> CDbl(Fields!CN_WGT.Value), 0.0))
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> news:26102E03-4F38-4F38-872E-633F5E02B78B@.microsoft.com...
> > I try to sum a value in the TABLE FOOTER if this record falls into a
> > particular condition. But the result is zero.
> > The formular is as follow:
> > =Sum(IIf(Fields!EQP_LEN.Value = 20 And Fields!EQP_TYPE.Value = "GP",
> > Fields!CN_WGT.Value, 0))
> >
> > If I the formular like this:
> > =Sum(IIf(Fields!EQP_LEN.Value = 20 And Fields!EQP_TYPE.Value = "GP", 1,
> 0))
> > It works !!!
> >
> > What's wrong ?
>
>

Thursday, March 8, 2012

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

Thursday, March 29, 2012

Cursor question

Sunday, March 25, 2012

Cursor in an SP

Sunday, March 11, 2012

Currency Format

Currency

Cumulative total in matrix

Cumulative Total

Thursday, March 8, 2012

Cumulative Aggregate Query

Cumulative Aggregate Query

Cumulative Aggregate Query

Cursors vs. Queries

Blog Archive

About Me