Been stumped on this for 3 months :D

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Been stumped on this for 3 months :D

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-06-10 : 23:41:55

I have a table called TBLTOPSTATES, this table is here to store the following

STATEPROVID
VOTES
POINTS
GENDERID

There will be 128 rows of data. 64 for each state and canadian province X 2 for each gender. I will need to update this just once a day and I am planning on doing it via a sql server job. This table is going to be viewed thousands of times per day so speed is very important as our server is already underpowered. The table that contains the data from which I would like to populate TBLTOPSTATES is called TBLVOTE. It has the following fields

voteForID
points

What I need to do is update TBLTOPSTATES with an aggregate function run against TBLVOTE. The columns that need updating are VOTES, and POINTS. I can join TBLUSERS on VoteForID and grab the corresponding GENDERID, so that I can update appropriatley for the two genders.

I hope that makes sense I did my best trying to explain it.
Thanks alot, I've almost given up after 3 months but I thought I should try again fresh.

Mike

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2002-06-11 : 03:34:02

So VoteForID = GENDERID ?

If so then(shot in the dark here

):-

Update TBLTOPSTATES Set POINTS = (SELECT V.points FROM TBLVOTE V WHERE GENDERID = V.VoteForID)

and you could update votes in the same update, but I was wondering how you get the figures here....

If this doesn't help, could you give some more information? e.g, some data detailing the links...

Peace

Rick

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-06-11 : 03:53:53

thanks here I will explain a little better

with VOTEFORID we can get the GENDERID below, altho I think this would be done in a join somehow in the final Stored Proc.

select GenderID FROM tblUsers WHERE voteForID ='500'

I need to update according to STATEPROVID and according to GENDERID

For instance if there was 5 votes in tblVote as such

voteForID = 500 / Points = 50
voteForID = 510 / Points = 60
voteForID = 520 / Points = 70
voteForID = 530 / Points = 80
voteForID = 540 / Points = 90

I need to update TBLTOPSTATES as such. VOTEFORID is a user in TBLUSERS, I need to find out what STATEPROVID they are from (SELECT STATEPROVID FROM TBLUSERS WHERE VOTEFORID ='500') and find out their gender (select GenderID FROM tblUsers WHERE voteForID ='500') Then I need to update the appropriate row in TBLTOPSTATES. So if they where from stateprovid "20" and where GenderID "2" then taht is the row I need to update with 50pts and 1 vote.. However I think I need to do it with a aggregate function since I have about 1 million rows of votes.

Please let me know what other info would help.

Thanks again.

Mike

dataphile
Yak Posting Veteran

71 Posts

Posted - 2002-06-11 : 04:05:35

create table TBLTOPSTATES (
STATEPROVID int,
VOTES int,
POINTS int,
GENDERID char(1))
go
create table TBLVOTE(
voteForID int,
points int,
genderid char(1))
go
insert into tblvote select 1,2,'M'
insert into tblvote select 1,3,'M'
insert into tblvote select 1,4,'F'
insert into tblvote select 1,7,'F'
go
delete from tbltopstates
insert into tbltopstates(stateprovid,votes,points,genderid) values (1,0,0,'M')
insert into tbltopstates(stateprovid,votes,points,genderid) values (1,0,0,'F')
go
update tts
set tts.votes = tts.votes+tv.votes, tts.points = tts.points+tv.points
from tbltopstates tts inner join
(select tv2.voteforid, tv2.genderid, count(*) votes, sum(tv2.points) points
from tblvote tv2
group by tv2.voteforid, tv2.genderid) tv on tv.voteforid = tts.stateprovid and tv.genderid = tts.genderid

delete from tblvote
go
select * from tbltopstates

--It's a pleasure.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-06-11 : 14:15:07

That is great I think its almost there

The thing is I need to have 128 rows of data.... 64 for each state and province X 2 for each genderID. Currently it only has 2 rows. How can I modify this?

A small note, also I can't delete the tblvotes after updating tbltopstates but I am assuming I can just take that line out

Thanks so much!

Mike

dataphile
Yak Posting Veteran

71 Posts

Posted - 2002-06-12 : 03:10:26

To accomodate the other states you need to insert them into the tbltopstates table:

insert into tbltopstates(stateprovid,votes,points,genderid) values (2,0,0,'M')
insert into tbltopstates(stateprovid,votes,points,genderid) values (2,0,0,'F')
...
insert into tbltopstates(stateprovid,votes,points,genderid) values (64,0,0,'M')
insert into tbltopstates(stateprovid,votes,points,genderid) values (64,0,0,'F')

If you don't delete the tblvote table after each update, you need to exclude the records you already inserted, by using a datetime-stamp in the tblvote table and use a "where datetime_stamp > lastupdated_date_time" (credit to Page47) in the update query.

--Always a pleasure.

Edited by - dataphile on 06/12/2002 03:12:49

damcalcan
Starting Member

11 Posts

Posted - 2002-06-12 : 16:04:38

in case you have a states table named lets say States, you can populate tbltopstates table using following script :

INSERT INTO tbltopstates(stateprovid,votes,points,genderid)
SELECT S.stateprovid , 0,0 , G.Type
FROM States
CROSS JOIN (SELECT 'M' as Type
UNION ALL
SELECT 'F' ) G

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources