Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 I dont know SQL - using cursors :)
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Tim
Starting Member

Australia
392 Posts

Posted - 06/18/2001 :  22:20:57  Show Profile  Reply with Quote
This (simplified) database stores results of tennis matches in 3 tables:

tblMatch
ID
Date
Venue

tblMatchPlayer
ID
MatchID
PlayerID
Result

tblPlayer
ID
Name

For every match there are 2 records written to tblMatchPlayer 1 for each player involved.
The result column stores 'W' for a win and 'L' for a loss.

Requirement is to provide a summary of all matches played since a selected date, with one player per row and the following columns:

- PlayerName
- NumberMatchesPlayed
- PercentWins
- PercentLosses
- Form

'Form' is a string that shows the results of each player's games in the same period, in chronological order with most recent first, eg: 'WLLWL'


I did make a single query that could do this but it was way to ineffiecient to run over a table with many thousands of rows.

So I resorted to a cursor which does everything but the 'form' in a select with group by. For each row returned by the cursor I do a second query which calculates the form.

Any cursor crushers out there can do it in single sql statement that is feasible with large nbr records?




Edited by - tim on 06/18/2001 22:23:16

colinm
Yak Posting Veteran

United Kingdom
62 Posts

Posted - 06/19/2001 :  05:53:54  Show Profile  Reply with Quote
You might be better splitting it into two queries.
Something roughly like this

SELECT p.ID,p.Name,count(mp.ID),
SUM(CASE mp.Result WHEN 'L' THEN 1 ELSE 0) /
count(mp.ID) * 100.0 AS PercentLoss INTO #temp

Then join it with your form query


Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/19/2001 :  06:14:34  Show Profile  Visit nr's Homepage  Reply with Quote
something like


SELECT pid = mp.PlayerID,matches = count(*),
wins = SUM(CASE mp.Result WHEN 'W' THEN 1 ELSE 0) ,
form = '' ,
LastID = 0
INTO #temp
from tblMatchPlayer mp join tblMatch m on m.id = mp.matchid
where m.date between ...
group by mp.PlayerID

declare @i int, @maxi int

select @i = 0, @maxi = max(matches) from #temp

while @i < @maxi
begin
select @i = @i + 1
update #temp
set LastID = (select min(id) from tblMatchPlayer mp where mp.PlayerID = #temp.pid and m.id > #temp.LastID)
where #temp.matches >= @i

update #temp set form = form + result
from tblMatchPlayer mp
where mp.PlayerID = #temp.pid
and #temp.matches >= @i
end

then select from #temp with a few calculations


==========================================
Cursors are useful if you don't know sql.
Go to Top of Page

davidpardoe
Constraint Violating Yak Guru

United Kingdom
324 Posts

Posted - 06/19/2001 :  06:42:31  Show Profile  Reply with Quote
A different way...

It assumes that the matchID is in order (ie. the higher the ID the later the date).

You should able to incorporate this with the query you have already written and the other suggestions for the other fields...

 
create table tblForm (PlayerID int,Form varchar(1000))
insert into tblForm(PlayerID) select distinct playerid from tblMatchPlayer
-- Table to hold form field

create table temp (ID int identity(1,1),MatchID int,PlayerID int, Result varchar(1))
insert into temp (MatchID,PlayerID,Result) select MatchID,PlayerID,Result from tblMatchPlayer order by PlayerID,MatchID
-- Reorders tblMatchPlayer - assumption is that matchID is higher the later the match date

SET CONCAT_NULL_YIELDS_NULL off
SET NOCOUNT ON
declare @id int,@maxid int,@currentplayerid int,@currentform varchar(1),@form varchar(1000)
select @id=1,@maxid=max(id),@currentplayerid=min(playerid) from temp -- Initialise @currentplayerid
while @id<=@maxid -- Loops through
begin
select @form=case when playerid=@currentplayerid then @form else '' end from temp where id=@id
-- Resets the form field to NULL if new player
select @currentplayerid=playerid from temp where @id=id
-- Updates @currentplayerid to current player
select @form=@form+result from temp where @id=id
-- Update form field
update tblForm set Form=@Form where playerid=@currentplayerid
-- Update results table
select @id = @id+1
-- Increments id by one
end


Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/19/2001 :  08:56:40  Show Profile  Visit nr's Homepage  Reply with Quote
That looks like it would be a bit slow.
you could do from my previous option


select @i = 0, @maxi = max(PlayerID) from #temp
declare @form varchar(1000)
while @i < @maxi
begin
select @i = min(PlayerID) from #Temp where PlayerId > @i

select @form = coalesce(@form + ',','') + result
from tblMatchPlayer mp join tblMatch m on m.id = mp.matchid
where mp.PlayerID = @i
and m.date between .....

update #temp set form = @form where PlayerID = @i
end

this might be quicker - depends on the data.


==========================================
Cursors are useful if you don't know sql.
Go to Top of Page

davidpardoe
Constraint Violating Yak Guru

United Kingdom
324 Posts

Posted - 06/19/2001 :  09:16:23  Show Profile  Reply with Quote
Ah hah!

That funky coalesce function - I really must learn more!

Go to Top of Page

dtong004
Constraint Violating Yak Guru

USA
281 Posts

Posted - 06/19/2001 :  12:36:18  Show Profile  Visit dtong004's Homepage  Reply with Quote
NR. I think your query have an assumption that id growing sequential with dates. The earler, the id value smaller. This assumption is questionable, I think. Correrct me if I am wrong

With Tim's purpose, I think a query with a cursor will make scripts a lot more cleaner. What do you think?

Daniel

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/19/2001 :  13:00:06  Show Profile  Visit nr's Homepage  Reply with Quote
You're right but you could do the same thing with a temp table to hold the IDs used if the assumption doesn't hold. Depends on how the data is obtained.

Guess what I think!

==========================================
Cursors are useful if you don't know sql.
Go to Top of Page

Tim
Starting Member

Australia
392 Posts

Posted - 06/20/2001 :  10:53:57  Show Profile  Reply with Quote
Some good suggestions here guys I am going to change my query a bit based on these. Thanks.

for interest sake, people enter the results at different times, perhaps a couple days after the match, in which other people's records may have been added for more recent dates. So you can't rely on the ID at all.

I have another doozy query from the same application if any one wants to look at it... Who thought a simple tenni sapp could get tricky eh.




Go to Top of Page

dtong004
Constraint Violating Yak Guru

USA
281 Posts

Posted - 06/20/2001 :  14:32:45  Show Profile  Visit dtong004's Homepage  Reply with Quote
That is exactly my concern. Well, you can get around with another temp table, as NR said.

Personaly, I think Cursor is the best way to go. Once your records below 50,000 row/cursor. It should be fine with SQL server. It is more robust with cursor. If you write this query with two cursors, one cursor handly the players, one cusro handle the games, none of them over 10,000 I would think.

Good luck

Daniel

Go to Top of Page

JustinBigelow
SQL Gigolo

USA
1157 Posts

Posted - 06/20/2001 :  16:12:44  Show Profile  Reply with Quote
quote:

It is more robust with cursor.



I can't agree with you that a cursor is a more robust solution in SQL Server. SQL is a set based environment. With very few exceptions a cursor based solution should lose out to a set based solution everytime.

Justin

Go to Top of Page

dtong004
Constraint Violating Yak Guru

USA
281 Posts

Posted - 06/20/2001 :  18:47:29  Show Profile  Visit dtong004's Homepage  Reply with Quote
quote:

quote:

It is more robust with cursor.



I can't agree with you that a cursor is a more robust solution in SQL Server. SQL is a set based environment. With very few exceptions a cursor based solution should lose out to a set based solution everytime.

Justin





Justin. We should avoid to use cursor. Cursor program is robust but expensive. Please look the problem I posted on June 12 about merge two tables together. Appreantly, we run out of strategy without cursor.

Personly, I don't like to create temp table. It make the code reading so hard to follow. Besides, creating temp table is expensive too.

Daniel

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/20/2001 :  21:10:07  Show Profile  Visit nr's Homepage  Reply with Quote
>> creating temp table is expensive too
Not very - if this takes up too much resources then you probably need to create redundant structures to support the query.

The benefit of temp table over cursors is that you can choose batches of records to process together even if you can't process everything at once - you are not limiting yourself to processing one row at a time.
A bigger benefit though is that you are learning how to use sql to it's full potential.
This is my main gripe about cursors - they indicate that either the developer isn't very good at sql or the database isn't designed to support the requirements. This doesn't mean that sometimes a pseudo cursor isn't the best solution as you can't always redesign a system (I would never implement a cursor, nor would anyone working for me, but sometimes do emulate the processing).

==========================================
Cursors are useful if you don't know sql.
Go to Top of Page

JustinBigelow
SQL Gigolo

USA
1157 Posts

Posted - 06/20/2001 :  21:59:15  Show Profile  Reply with Quote
quote:

(I would never implement a cursor, nor would anyone working for me,



nr rules over his development team with an iron fist!!!

Justin

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/20/2001 :  23:03:04  Show Profile  Visit nr's Homepage  Reply with Quote
I would never work for me .

==========================================
Cursors are useful if you don't know sql.
Go to Top of Page

dtong004
Constraint Violating Yak Guru

USA
281 Posts

Posted - 06/21/2001 :  10:51:56  Show Profile  Visit dtong004's Homepage  Reply with Quote
NR and Justin.

If you look at NR's solution for Tim's problem. It does go row by rows even with a temp table. This is implict cursor!

The rest of discussion is what is difference between implict cursor vs. explict cursor.

Explict cursor has "declare cursor",
Implict cursor doesn't

Explict cursor maybe a little slower than implict cursor. However, the code is cleaner and the program is more flexible. Implict cursor can do smalle job not complicated work.

I did not say you cannot use the implict cursor like NR's solution. But if we get a little bit further complex of Tim's problem, you will find implicit cursor cannot do the work.

Good day

Daniel



Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/21/2001 :  11:06:00  Show Profile  Visit nr's Homepage  Reply with Quote
I call it a pseudo-cursor.
Agree that it's not much different.

The advantage is that it allows you to do more flexible processing as you have access to the whole table not just the current row.

And it doesn't use the keyword cursor .

==========================================
Cursors are useful if you don't know sql.
Go to Top of Page

davidpardoe
Constraint Violating Yak Guru

United Kingdom
324 Posts

Posted - 06/21/2001 :  11:08:41  Show Profile  Reply with Quote
I must be taking sides with nr but I am convinced that no matter how complex the problem the implicit or pseudo cursor can do the same as the explicit one.

My background has been SQL/relational databases based rather than programming, hence working directly with the tables, rather than setting up a cursor is more logical to me.

I also like the idea of being to access all the data, not just in the table the cursor has been opened on but others as well.

Go to Top of Page

Tim
Starting Member

Australia
392 Posts

Posted - 08/10/2001 :  02:04:36  Show Profile  Reply with Quote
Well I tried everything posted here but couldn't get it working.

So here is some data and expected result, can anyone do it without cursor, and a solution which must work on thousands of rows?

EXPECTED RESULT


Player MatchesPlayed Win% Loss% Form
------------------------------------
Jim 2 50% 50% WL
Kim 4 75% 25% LWWW
Tim 4 25% 75% WLLL


DATA


drop table Player
create Table Player(
Player_ID int IDENTITY,
PlayerName VarChar(50) NOT NULL
)

INSERT INTO Player VALUES ('Tim')
INSERT INTO Player VALUES ('Kim')
INSERT INTO Player VALUES ('Jim')

drop table Match
create Table Match(
Match_ID int IDENTITY,
MatchDate datetime NOT NULL
)

INSERT INTO Match VALUES ('4-JAN-01')
INSERT INTO Match VALUES ('1-JAN-01')
INSERT INTO Match VALUES ('2-JAN-01')
INSERT INTO Match VALUES ('3-JAN-01')
INSERT INTO Match VALUES ('6-JAN-01')

drop table Result
create Table Result(
Result_ID int IDENTITY,
Match_ID int NOT NULL,
Player_ID int NOT NULL,
Result char(1) NOT NULL
)

INSERT INTO Result VALUES (1,1,'L')
INSERT INTO Result VALUES (1,2,'W')
INSERT INTO Result VALUES (2,2,'W')
INSERT INTO Result VALUES (2,3,'L')
INSERT INTO Result VALUES (3,1,'L')
INSERT INTO Result VALUES (3,3,'W')
INSERT INTO Result VALUES (4,2,'W')
INSERT INTO Result VALUES (4,1,'L')
INSERT INTO Result VALUES (5,1,'W')
INSERT INTO Result VALUES (5,2,'L')



Go to Top of Page

davidpardoe
Constraint Violating Yak Guru

United Kingdom
324 Posts

Posted - 08/10/2001 :  06:18:58  Show Profile  Reply with Quote
OK - this will give you the results you need. I'm not sure on performance on a large dataset though.

create table Form (PlayerName varchar(50),Player_ID int,Form varchar(1000))
insert into Form(PlayerName, Player_ID)
select distinct PlayerName,player_id from Player -- Table to hold form field

create table temp (ID int identity(1,1),Match_ID int,Player_ID int, Result varchar(1))
insert into temp (Match_ID,Player_ID,Result)
select result.Match_ID,Player_ID,Result from Result
inner join match on match.match_id=result.match_id
order by Player_ID,MatchDate
-- Reorders tblMatchPlayer and adds an identity column for looping purposes

SET CONCAT_NULL_YIELDS_NULL off

declare @id int,@maxid int,@currentplayerid int,@currentform varchar(1),@form varchar(1000)

select @id=1,@maxid=max(id),@currentplayerid=min(player_id) from temp
-- Initialise @currentplayerid
while @id<=@maxid -- Loops through
begin
select @form=case when player_id=@currentplayerid then @form else '' end from temp where id=@id
-- Resets the form field to NULL if new player
select @currentplayerid=player_id from temp where @id=id
-- Updates @currentplayerid to current player
select @form=@form+result from temp where @id=id
-- Update form field
update Form set Form=reverse(@Form) where player_id=@currentplayerid
-- Update results table
select @id = @id+1
-- Increments id by one
end

select PlayerName,
MatchesPlayed=count(*),
win=cast(sum(case when Result='W' then 1 else 0 end) as float)/count(*),
loss=cast(sum(case when Result='L' then 1 else 0 end) as float)/count(*),
Form
from temp
inner join form on temp.player_id=form.player_id
group by form.player_id,form.PlayerName,form.Form
order by form.PlayerName




Edited by - davidpardoe on 08/10/2001 06:19:44
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000