looping thru a table and updating the contents

Hello, Its hard trying to explain this.

I have 3 tables

Table 1 is where the users are stored, each user has a username and a userrank

Table 2 is where the points that decides the userrank are stored

Table 3 contains the available userranks like this

Table 1 (user_list) looks briefly like this:
username nvarchar(20),
userrank int, -- Reference to Table3 id
... alot more fields

Table 2 (settings_profile) looks like this:
username nvarchar(20),
total_active_points int,
... some more fields

Table 3 (data_ranks) looks like this:
id int primary key auto inc,
rankname nvarchar(20),
min_pts int,
max_pts int

Points get added to table 2 whenever they do something that generates points on the site. Points also get withdrawn every 7 days, so a user can only collect points for 7 days, on the 8th day, all points he earned on the 1st day is reduced from the current points with this code:

WHILE(SELECT @.username= username, @.id= id, @.temp1= ap_sentmails, @.temp2= ap_createdthreads, @.temp3= ap_createdanswers, @.temp4= ap_signguestbook, @.temp5= ap_blogcomment, @.temp6= ap_createblogentry, @.temp7= ap_profilefirsttime, @.temp8= ap_profilephoto, @.temp9= ap_activateguestbook, @.temp10= ap_addnewfriend, @.temp11= ap_superguruvote, @.temp12= ap_forumtopicvote, @.temp13= ap_labervote, @.temp14= ap_funstuffitemvote, @.temp15= ap_movievote, @.temp16= ap_actorvote, @.temp17= ap_money_newWHERE(created<Dateadd(dd,-7, @.todaysdate))
BEGIN
SET @.sum= 0
SET @.sum= @.temp1+ @.temp2+ @.temp3+ @.temp4+ @.temp5+ @.temp6+ @.temp7+ @.temp8+ @.temp9+ @.temp10+ @.temp11+ @.temp12+ @.temp13+ @.temp14+ @.temp15+ @.temp16+ @.temp17
UPDATE settings_profileSET total_active_points= total_active_points- @.sumWHERE(username= @.username)
DELETEFROM konto_daylistWHERE(id= @.id)
END

Now my question is this, i want to loop thru the table A, collect all usernames inside of it, then run it against table b and table c to determine the current rank of the user.
Something like this...

DECLARE @.username nvarchar(20)
DECLARE @.pts int, @.rank int

...something that starts a loop thru table A (user_list) and get the username into @.username...

SELECT @.pts =total_active_points FROM settings_profile WHERE (username = @.username)
-- Determine the rank here, by compairing the points the user have against the pointstabel in table data_ranks
SELECT @.rank = id FROM data_ranks WHERE (pts_min => @.pts AND pts_max < @.pts)
UPDATE user_list SET rank = @.rank WHERE (username = @.username)

...next persion in the loop...

This SP runs once a day and will first reduce the points from 8days ago, then it will run thru all the users and determine their new rank...

But how do i loop thru all the users? with a cursor?

I don't have time to write this for you, but here's the line of attack to use:

Create and test a query that computes the the correct score for each user.

then, issue an update in this format:

update settings_profile

set total_active_points = ( the query from above where settings_profile.username = other_table.username)

|||

Hi, i dont truly understand what you meant. I can compute the score for each user If i have their username, thats why i need to create a loop that loops thru every post in table A, so that i can update every single post with the rank they earned from the points. I need to find someway to loop thru all posts.

|||

No, you do not need to write a loop thru the users.

This is because the SQL Update statement provides an implied loop for you.

Example:

createtable xxx(idint, textvaluevarchar(20))

insertinto xxxvalues(1,'Nikki')

insertinto xxxvalues(2,'John')

update xxxset textvalue='Ginger'

select*from xxx

You will note that every xxx record now has Ginger in the textvalue column.

The update statement IS a loop!

And, while it is processing any given xxx record, it knows the value in each of that record's columns.

I do not have to hard-code the value that will be used to set textvalue with.

I can issue a query instead.

So, let's create a new table called nametable.

createtable nametable(idint,namevarchar(20))

insertinto nametablevalues(1,'Amber')

insertinto nametablevalues(2,'Tracy')

update xxx set textvalue = (select name from nametable where nametable.id = xxx.id)

select * from xxx

Note that the name for record 1 is now Amber and the name for record 2 is now Tracy

Hope that helps clarify things.

|||

Hi,

Its working like a clock now, thanks for the explanation.

I build this line with the example code you sent, and its brilliant.

UPDATE profile_publicinfoSET userrank=(SELECT data_userrank.idFROM settings_profileINNERJOIN data_userrankON(data_userrank.pointsmin<= settings_profile.total_active_pointsAND data_userrank.pointsmax> settings_profile.total_active_points)WHERE(settings_profile.username= profile_publicinfo.username))

|||

Great!

Now all you have to do is mark the thread as answered, so other folks don't spend their time trying to help you only to find out you don't need help anymore! :)

The key to being ultra-productive with SQL is to think in terms of "sets of data" instead of looping thru records of data one at a time.

|||

Then i just have to ask, is it possible to update several records with a IF statement?

Like this,

UPDATE clubsSET isactive=(IF(clubs_account.money-clubs_account.cost< 0SELECT 1) ELSE (SELECT 0)FROM clubs_account)

Thats money - expenses, if its less then 0, return a 1 otherwise return a 0

|||

The SQL equivalent of an IF statement is a CASE statement.

But don't try to write it the way your if statement was coded.

Try this instead:

update clubs
set isactive = (select case clubs_account.money - clubs_account.cost < 0
when true then 1
else 0
end case
from clubs_account
where clubs_account.? = clubs.?
)

|||

Looks very good, but i just keeps getting an error when i tried it (i also tried to put () around the math calculations, but i still get the same result),

The clubs.inactive is a bit field if that is of any importance.

UPDATE clubsSET isactive=(SELECTCASE club_konto.money- club_konto.cost< 0WHEN trueTHEN 1ELSE 0ENDCASEFROM club_kontoWHERE club_konto.clubid= clubs.id)

Incorrect syntax near '<'.

|||

bit fields are 1s and 0s, not true and false.

Also, I work in too many different languages. :( It's END, not END CASE.

createtable clubs(idint, isactivebit)

createtable club_konto( clubidint,moneyint, costint)

insertinto clubsvalues(1,1)

insertinto clubsvalues(2,1)

insertinto clubsvalues(3,0)

insertinto clubsvalues(4,0)

insertinto club_kontovalues(1,50,10)

insertinto club_kontovalues(2,10,50)

insertinto club_kontovalues(3,50,10)

insertinto club_kontovalues(4,10,50)

select*from clubs

select*from club_konto

UPDATE clubs

SET isactive=(SELECTCASEWHEN club_konto.money- club_konto.cost< 0THEN 1ELSE 0END

FROM club_kontoWHERE club_konto.clubid= clubs.id)

select*from clubs

I have to caution you about this - the select statement used to populate the isactive column must NOT return more than one row of data.

If it can return more than one row, you need to change it so it only returns one row. So, if there can be two or more club_konto records per club, the query above will not work. Instead you would need it to be something like this:

UPDATE clubs

SET isactive=(SELECTCASEWHEN sum(club_konto.money)- sum(club_konto.cost)< 0THEN 1ELSE 0END

FROM club_kontoWHERE club_konto.clubid= clubs.id)

|||

I see that i need to learn alot more about how data is handeled inside MS SQL 2005, its working perfect with your example,

Thanks alot

(I have afew more SPs with While loops that i have to go thru now and see if i can upgrade them according to the new update way.)

Loop through a recordset to populate columns in a temp table

I want to take the contents from a table of appointments and insert the
appointments for any given month into a temp table where all the
appointments for each day are inserted into a single row with a column
for each day of the month.
Is there a simple way to do this?

I have a recordset that looks like:

SELECT
a.Date,
a.Client --contents: Joe, Frank, Fred, Pete, Oscar
FROM
dbo.tblAppointments a
WHERE
a.date between ...(first and last day of the selected month)

What I want to do is to create a temp table that has 31 columns
to hold appointments and insert into each column any appointments for
the date...

CREATE TABLE #Appointments (id int identity, Day1 nvarchar(500), Day2
nvarchar(500), Day3 nvarchar(500), etc...)

Then loop through the recordset above to insert into Day1, Day 2, Day3,
etc. all the appointments for that day, with multiple appointments
separated by a comma.

INSERT INTO
#Appointments(Day1)
SELECT
a.Client
FROM
dbo.tblAppointments a
WHERE
a.date = (...first day of the month)

(LOOP to Day31)

The results would look like
Day1 Day2 Day3 ...
Row1 Joe, Pete
Frank,
Fred

Maybe there's an even better way to handle this sort of situation?
Thanks,
lqYou can use a query to do reports like this without looping or temp tables.
As specified it seems a little strange to list the dates horizontally since
there is apparently no data on the vertical axis but the rest is really just
a matter of formatting once you have the basic query. Formatting is best
done client-side rather than in the database.

DECLARE @.dt DATETIME
/* First date of the month */
SET @.dt = '20050501'

SELECT
MAX(CASE WHEN DATEDIFF(DAY,@.dt,date)=0 THEN client END),
MAX(CASE WHEN DATEDIFF(DAY,@.dt,date)=1 THEN client END),
MAX(CASE WHEN DATEDIFF(DAY,@.dt,date)=2 THEN client END),
...
MAX(CASE WHEN DATEDIFF(DAY,@.dt,date)=30 THEN client END)
FROM tblAppointments
WHERE date >= @.dt
AND date < DATEADD(MONTH,1,@.dt)
GROUP BY client

--
David Portas
SQL Server MVP
--|||laurenq uantrell wrote:
> I want to take the contents from a table of appointments and insert the
> appointments for any given month into a temp table where all the
> appointments for each day are inserted into a single row with a column
> for each day of the month.
> Is there a simple way to do this?
> I have a recordset that looks like:
> SELECT
> a.Date,
> a.Client --contents: Joe, Frank, Fred, Pete, Oscar
> FROM
> dbo.tblAppointments a
> WHERE
> a.date between ...(first and last day of the selected month)
> What I want to do is to create a temp table that has 31 columns
> to hold appointments and insert into each column any appointments for
> the date...
> CREATE TABLE #Appointments (id int identity, Day1 nvarchar(500), Day2
> nvarchar(500), Day3 nvarchar(500), etc...)
> Then loop through the recordset above to insert into Day1, Day 2, Day3,
> etc. all the appointments for that day, with multiple appointments
> separated by a comma.
> INSERT INTO
> #Appointments(Day1)
> SELECT
> a.Client
> FROM
> dbo.tblAppointments a
> WHERE
> a.date = (...first day of the month)
> (LOOP to Day31)
>
> The results would look like
> Day1 Day2 Day3 ...
> Row1 Joe, Pete
> Frank,
> Fred
> Maybe there's an even better way to handle this sort of situation?
> Thanks,
> lq

You're talking about crosstab queries. Here's a page of links that may
be of use:

http://www.google.com/custom?q=cros...ID%3A1%3B&hl=en

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> I want to take the contents from a table of appointments and insert the
> appointments for any given month into a temp table where all the
> appointments for each day are inserted into a single row with a column
> for each day of the month.
> Is there a simple way to do this?
>...
> Then loop through the recordset above to insert into Day1, Day 2, Day3,
> etc. all the appointments for that day, with multiple appointments
> separated by a comma.

I'm a afraid that loop is what you will have to do. And write 31
UPDATE statements, one for each day of the month. There are ways to
build comma-separated lists with set-based statements, but the
methods used are unsupported and undefined, and cannot be trusted.
So the only way to build a CSV is to run a cursor.

OK, you don't really need 31 UPDATE statements. You could aggregate
data into a table with one per date, and then at then end run a
31-way self cross-join to produce the final result.

Certainly, a client program is much better apt to do this sort
of thing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||David, thanks for that. I thought it must be pretty straignt forward.
The reason I want it all in one row and the days data in columns is
that I want to populate a sincle record on a form that resembles a
monthly calendar but cwhich in fact is simply 31 text boxes. Previously
I had a form that populated 31 different subforms by looping through
the dates on the client side and it is too slow.THanks,
lq|||David, thanks for that. I thought it must be pretty straight forward.
The reason I want it all in one row and the days data in columns is
that I want to populate a sincle record on a form that resembles a
monthly calendar but cwhich in fact is simply 31 text boxes. Previously
I had a form that populated 31 different subforms by looping through
the dates on the client side and it is too slow.THanks,
lq|||Erland,
My solution is to run a UDFwithin a View that creates the comma
separated list for each date of appointments. That part works fine on a
single date, and now I'm just figuring out how to loop through the
dates: first day of the month + 31 days.
Thanks.
lq|||Erland,
You got me in the right direction and the solution works very fast:

The stored procedure:

@.ClienID int,
@.dt datetime /* first day of the selected month */

AS

DECLARE @.dtEnd datetime
SET @.dtEnd = DATEADD(DAY,-1,DATEADD(MONTH,1,@.dt)) /* last day of the
selected month */

SELECT
dbo.fn_ClientSked(@.dt, @.ClientID) AS D1,
dbo.fn_ClientSked(DATEADD(DAY,1,@.dt), @.ClientID) AS D2,
dbo.fn_ClientSked(DATEADD(DAY,2,@.dt), @.ClientID) AS D3,
etc...(for D4-D28)
CASE WHEN DATEADD(DAY,28,@.dt) <= @.dtEnd THEN
dbo.fn_ClientSked(DATEADD(DAY,28,@.dt), @.ClientID) END AS D29,
CASE WHEN DATEADD(DAY,29,@.dt) <= @.dtEnd THEN
dbo.fn_ClientSked(DATEADD(DAY,29,@.dt), @.ClientID) END AS D30,
CASE WHEN DATEADD(DAY,30,@.dt) <= @.dtEnd THEN
dbo.fn_ClientSked(DATEADD(DAY,30,@.dt), @.ClientID) END AS D31

The UDF:

CREATE function dbo.fn_ClientSked(@.dtX as DateTime, @.ClientID as int)
returns
nvarchar(500)
AS
begin
declare @.ret_value nvarchar(500)
SET @.ret_value=''
Select @.ret_value=@.ret_value + '; ' + AppointmentTitle
FROM dbo.tblAppointments
WHERE
tblAppointments.ClientID = @.ClientID
AND
@.dtX Between tblAppointments.StartDate AND tblAppointments.EndDate
RETURN CASE WHEN LEN(@.ret_value)>0 THEN
RIGHT(@.ret_value,LEN(@.ret_value)-2) ELSE '' END
end

Note: This particular UDF returns all appointments by day of the month
for the same client to populate a monthly calendar. It can be easily
modified to show appointments by employee, etc.|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> You got me in the right direction and the solution works very fast:
>...
> declare @.ret_value nvarchar(500)
> SET @.ret_value=''
> Select @.ret_value=@.ret_value + '; ' + AppointmentTitle
> FROM dbo.tblAppointments
> WHERE
> tblAppointments.ClientID = @.ClientID
> AND
> @.dtX Between tblAppointments.StartDate AND tblAppointments.EndDate
> RETURN CASE WHEN LEN(@.ret_value)>0 THEN
> RIGHT(@.ret_value,LEN(@.ret_value)-2) ELSE '' END
> end

While it may work and be fast, it relies on undefined behaviour. See
http://support.microsoft.com/default.aspx?scid=287515.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
Now you have me worried. Because I am using a UDF within the SELECT
statement? Isn't that what they're for?
I chose this solution because the other solutions I conceived required
31 server calls to populate 31 subforms (one for each day of the week.)
With the method above the data is all shoved down the pipe as one row.
Isn't that a preferable solution where a simple string output is all
that's required for each date?
lq|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Now you have me worried. Because I am using a UDF within the SELECT
> statement? Isn't that what they're for?

No, because of

Select @.ret_value=@.ret_value + '; ' + AppointmentTitle
FROM dbo.tblAppointments

This can give you want you want, or just one appointment. Or something
else. The result of this construct "concatentate aggregation" is not
defined.

If you want to play safe, write a cursor instead. (But you can still
do this in a UDF.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||OK, thanks for the info. I hate messing with cursors...
lqsql