Cursors vs. Queries: fields

Showing posts with label fields. Show all posts

Thursday, March 29, 2012

cursor to compare/report on the same fields in 2 tables

I only want to see results if they are 1 for the same address field

this is what I have so far...

declare @.address1 char(61),@.city char(61)

declare address_cursor CURSOR FOR
SELECT address1,city FROM test.dbo.testadd

OPEN address_cursor

fetch next from address_cursor into @.address1,@.city
while @.@.fetch_status = 0
BEGIN
select * from testadd where @.address1 like '%' + address1 + '%' and
@.city = city
Fetch next from address_cursor into @.address1,@.city
Print
END
CLOSE address_cursor
DEallocate address_cursorhttp://sqlserver-puzzles.blogspot.c...albes-real.html
--------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||SQLNewbie wrote:

Quote:

Originally Posted by

[snip]

You refer to yourself as a "newbie". If you are new to SQL or to SQL
Server then do not even attempt to write cursors.

There are nearly always better alternatives to cursors. It's only when
you have a lot of experience that you can make an informed decision
about when a cursor makes sense. Meantime, if you can't think of
another way to do something it would be better to ask for help rather
than try to write a cursor. You'll learn good practices a LOT quicker
that way.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||SQLNewbie (tahnee.puckett@.bancsourceinc.com) writes:

Quote:

Originally Posted by

If it's a copy, isn't it the same data then?

I was trying to understand what you really want to do, but I'm afraid I
don't.

I would suggest that you post:

o CREATE TABLE statements for your tables, preferably to show the
pertinent points.
o INSERT statements with sample data.
o The desired output given the sample.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||SQLNewbie wrote:

Quote:

Originally Posted by

I have the following cursor that I am comparing 2 tables, the
production table and a copy of the production table, I want results of
all address's that are like the address1 field...the problem is...my
results are giving me every field and if there is more than one, it is
putting it in a grid...
>
I only want to see results if they are 1 for the same address field
>
this is what I have so far...
>
>
declare @.address1 char(61),@.city char(61)
>
declare address_cursor CURSOR FOR
SELECT address1,city FROM test.dbo.testadd
>
OPEN address_cursor
>
fetch next from address_cursor into @.address1,@.city
while @.@.fetch_status = 0
BEGIN
select * from testadd where @.address1 like '%' + address1 + '%' and
@.city = city
Fetch next from address_cursor into @.address1,@.city
Print
END
CLOSE address_cursor
DEallocate address_cursor

See if this points you in the right direction:

select t.address1, t.city, count(*)
from test.dbo.testadd t
join production.dbo.testadd p on t.city = p.city
where p.address1 like '%' + t.address1 + '%'
group by t.address1, t.city
having count(*) 1

Tuesday, March 27, 2012

cursor or query

> Dear all,
> i had a table named saletable i had fields like
> itemgroupid,itemgroupname,saleqty,saleam
ount and saledate.
> i want to sum the saleqty and saleamount by every month and year.by every itemgoup
name
suppose that every year has january month and 31 days in january so i want
that for every year and every month i should sum the qty and sale amount for
Y/M but by Itemgroupname
do not want to give criteria
> for eaxmple
> itemgroupid itemgroupname saleqty saleamount date
> ----
--
> 23101102 HERO LINER 10.00 50467.29 2004-01
-07
> 23101102 HERO LINER 20.00 10924.58 2005-01
-07
> 23101102 HERO LINER 10.00 50467.29 2004-02
-07
> 23101102 HERO LINER 20.00 10924.58 2005-02
-07
> 23101102 HERO LINER 10.00 50467.29 2004-01
-07
> 23101102 HERO LINER 20.00 10924.58 2005-01
-07
> 23204101 Trading Mudgard 10.00 6000.00 2005-04-21
> 23204101 Trading Mudgard 10.00 50467.29 2004-01-07
> 23204101 Trading Mudgard 20.00 10924.58 2005-01-07
> 23204101 Trading Mudgard 20.00 10924.58 2005-02-07
> 23204101 Trading Mudgard 10.00 50467.29 2004-01-07
> 23204101 Trading Mudgard 20.00 10924.58 2005-01-07
> I WANT like this
> for every month and year
> 23204101 Trading Mudgard 20.00 100934.58 2004-01
> 23204101 Trading Mudgard 60.00 2326000.00 2005-01
> 23204101 Trading Mudgard 10.00 6000.00 2005-07
> thanx
--
waiting for solution
from
SufianSELECT itemgroupid, itemgroupname, MIN(date),
SUM(saleqty), SUM(saleamount)
FROM SaleTable
GROUP BY YEAR(date), MONTH(date)
If that's not what you want then please post DDL, sample data, required
results. See:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Thanx David it worked and once again thanx
--
waiting for solution
from
Sufian
"David Portas" wrote:

> SELECT itemgroupid, itemgroupname, MIN(date),
> SUM(saleqty), SUM(saleamount)
> FROM SaleTable
> GROUP BY YEAR(date), MONTH(date)
> If that's not what you want then please post DDL, sample data, required
> results. See:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>
>

Sunday, March 25, 2012

Cursor Column name

Is there any way in sql to know the column name in cursor. like in record set (rsName.Fields(1).Name gives us the coulumn name)
Thanks in AdvanceI think Mr Chigrik can help you - syscursors etc -

http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocTbl.htmsql

Thursday, March 22, 2012

CurrentMember failed with (Multiple Items) in the 'filter fields'

hello

CurrentMember failed with (Multiple Items) in the 'filter fields' because ofcourse CurrentMember can NOT be associated with a set !!
Is there a simple way to make this Multiple-Item-safe (in the filter) ?

for example this MDX-formula:

sum(
generate
(
ascendants([Posting Period].currentmember),
iif(
[Posting Period].currentmember IS
[Posting Period].firstsibling,
{},
[Posting Period].firstsibling:
[Posting Period].prevmember
)
) + {[Posting Period].currentmember}
,
[Measures].[VRD Mutation]
)Mosha posted a good discussion of this problem on his blog quite recently:
http://www.sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx|||Interested Big Smile

In short: use EXISTING-operator or exists-function OR remove CurrentMember statement !
But.... How about rewriting the ascendants-function (instead of descendants) into a multiselect-friendly-mdx ? (See my example above)|||

This problem raises the question of what you want to see when you select multiple members on Posting Period here - what do you want to do the cumulative sum on? I'm going to assume that you want to do the cumulative sum on the most recent selected Posting Period, and if this is correct then the following MDX should work:

sum(
generate
(
ascendants({tail(existing [Posting Period].[Day].Members,1) as myset}.item(0)),
iif(
[Posting Period].currentmember IS
[Posting Period].firstsibling,
{},
[Posting Period].firstsibling:
[Posting Period].prevmember
)
) + myset,
[Measures].[VRD Mutation]
)

Incidentally while looking at this problem, I've realised that the algorithm you're using here from Richard's site is not actually as efficient as it should be: in the scenario where you've selected a member which is the last member in its set of siblings it sums up the complete set of siblings instead of replacing the set with its parent. This effect is magnified when you have to deal with sets in the where clause in the way I've outlined, because I'm forcing everything down to the Month level - see this sample AdventureWorks query as an illustration:

WITH MEMBER MEASURES.TEST AS

GENERATE(

ASCENDANTS(

{TAIL(EXISTING [Date].[Calendar].[Date].MEMBERS,1) AS MYSET}.ITEM(0)

)

,IIF( [Date].[Calendar].CURRENTMEMBER IS [Date].[Calendar].CURRENTMEMBER.FIRSTSIBLING,

{},

[Date].[Calendar].CURRENTMEMBER.FIRSTSIBLING : [Date].[Calendar].CURRENTMEMBER.PREVMEMBER

)

) + MYSET

, [Date].[Calendar].CURRENTMEMBER.NAME, "; ")

SELECT MEASURES.TEST ON 0,

[Date].[Calendar].MEMBERS

ON 1

FROM [Adventure Works]

I don't think the other (admittedly more complex) approaches on my blog suffer from this problem, so you may want to use them instead.

HTH,

Chris

sql

Sunday, March 11, 2012

Currency Exporting to Excel

Hi,
In one of my reports I have some fields that are currency fields displayed
with the currency format string (C). However, when I export these to Excel
(2003) I get the little yellow warning that says Number stored as text. And
if I try to do a sum by dragging across the cells and clicking the sigma
button at the top, it causes it to put an odd formula in the top row.
Is there an easy fix so that #'s exported as currency remain tied to excel
as currency?
Thanks,
ChrisI tried this on a matrix in RS2005 and it worked OK.
The format was c0. I do remember it being an issue in 2000. I don't
think there was a way round it in the report definition.
The only thing you could try is, in Excel, highlight the column and
format the cell to currency. If this works you will be able to do a SUM.
Cheers
Chris
Chris Taylor wrote:
> Hi,
> In one of my reports I have some fields that are currency fields
> displayed with the currency format string (C). However, when I
> export these to Excel (2003) I get the little yellow warning that
> says Number stored as text. And if I try to do a sum by dragging
> across the cells and clicking the sigma button at the top, it causes
> it to put an odd formula in the top row.
> Is there an easy fix so that #'s exported as currency remain tied to
> excel as currency?
> Thanks,
> Chris|||Tried formatting C0, stlil getting the text formatted as #.
MS any insight on this?
"Chris" <mr.mcgoo@.gmail.com> wrote in message
news:uPH9wVvQGHA.2704@.TK2MSFTNGP15.phx.gbl...
>I tried this on a matrix in RS2005 and it worked OK.
> The format was c0. I do remember it being an issue in 2000. I don't
> think there was a way round it in the report definition.
> The only thing you could try is, in Excel, highlight the column and
> format the cell to currency. If this works you will be able to do a SUM.
> Cheers
> Chris
>
> Chris Taylor wrote:
>> Hi,
>> In one of my reports I have some fields that are currency fields
>> displayed with the currency format string (C). However, when I
>> export these to Excel (2003) I get the little yellow warning that
>> says Number stored as text. And if I try to do a sum by dragging
>> across the cells and clicking the sigma button at the top, it causes
>> it to put an odd formula in the top row.
>> Is there an easy fix so that #'s exported as currency remain tied to
>> excel as currency?
>> Thanks,
>> Chris
>
> --
>|||Hi Chris,
Please modify the column width in Excel. I have tested in my side on RS
2005, it works fine.
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Nope. I just tried this; both FormatCurrency and Format c0 send a
number as text, causing Excel to just put a Smart Tag on it and ignore
it in functions. I know that converting numbers to text is the entire
purpose of those two functions, but making them the sole means of
formatting numbers makes the Excel export function about as useless as
nipples on the Batsuit.
Wei Lu: I think Chris Taylor means "text formatted as number" and not
"the column's too narrow, so Excel puts in pound signs to tell you to
resize the column."

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 to compare/report on the same fields in 2 tables

Tuesday, March 27, 2012

cursor or query

Sunday, March 25, 2012

Cursor Column name

Thursday, March 22, 2012

CurrentMember failed with (Multiple Items) in the 'filter fields'

Sunday, March 11, 2012

Currency Exporting to Excel

Thursday, March 8, 2012

Cumulative Aggregate Query

Cumulative Aggregate Query

Cumulative Aggregate Query

Cursors vs. Queries

Blog Archive

About Me