help with SP - SQL Server Forums

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)

help with SP

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-05-22 : 04:29:43

I'm trying to write a stored procedure that is a little over my head. I have the following but it is incorrect.

I have a table that full of email addresses that have been entered by users with the corresponding USERID of the user who entered the email. When a user refers a friends email, and they sign up with that email I want that record to register in the record count.

For instance say I am userID 5000 and I recommend bob@hotmail.com, and bill@hotmail.com. If these 2 addresses sign up in the TBLUSERDETAILS table after I recommend them, I want my USERID - 5000 to bring back "2". I am also attempting to sort them by the users with the most referals.

Its pretty late, thats the best I could explain it right now :)

If its confusing please let me know

Thanks for all the help, once again!!!

Mike123

------------Code Below

CREATE procedure select_referersEmail

AS SET NOCOUNT ON

SELECT nameonline, COUNT(referuserid) AS ReferredCount

FROM TBLUSERDETAILS

INNER JOIN TBLREFEREMAILS ON TBLUSERDETAILS.userid = TBLREFEREMAILS.referuserid

WHERE TBLUSERDETAILS.DATE > TBLREFEREMAILS.REFERDATE

GROUP BY nameonline, referuserid
GO

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-05-22 : 18:18:47

Mike, on first glance, this looks okay. Is it not working? To do the sorting, just add an ORDER BY 2 DESC after the GROUP BY clause.

------------------------------------------------------
The more you know, the more you know you don't know.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-22 : 18:46:09

CREATE procedure select_referersEmail

AS SET NOCOUNT ON
SELECT nameonline, COUNT(*) AS ReferredCount
FROM TBLUSERDETAILS
INNER JOIN TBLREFEREMAILS ON TBLUSERDETAILS.userid = TBLREFEREMAILS.referuserid
WHERE TBLUSERDETAILS.DATE > TBLREFEREMAILS.REFERDATE
GROUP BY nameonline
order by COUNT(*) desc

if you can have duplicate referrals then

SELECT nameonline, COUNT(distinct referuserid) AS ReferredCount
FROM TBLUSERDETAILS
INNER JOIN TBLREFEREMAILS ON TBLUSERDETAILS.userid = TBLREFEREMAILS.referuserid
WHERE TBLUSERDETAILS.DATE > TBLREFEREMAILS.REFERDATE
GROUP BY nameonline
order by COUNT(distinct referuserid) desc

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 05/22/2003 18:48:35

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-05-22 : 19:56:39

Thanks for the help

Neither of these seem to be working, perhaps my original SP is so off that it is misleading.

I need a recordset as such brought back

UserBob 45
UserRob 50
UserJim 44

etc..

Currently it only brings back 1 record and has a count of 1 referal. The one record is possibly correct, but the count of referals is definately not correct.

Thanks for all the help once again!

Mike123

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-22 : 20:29:23

How about this
select tReferrer.nameonline, count(*)
FROM TBLUSERDETAILS tReferred
INNER JOIN TBLREFEREMAILS ON tReferred.emailaddress = TBLREFEREMAILS.emailaddress
INNER JOIN TBLUSERDETAILS tReferrer ON tReferrer.userid = TBLREFEREMAILS.referuserid
WHERE tReferred.DATE > TBLREFEREMAILS.REFERDATE
group by tReferrer.nameonline
order by count(*) desc

It uses a copy of TBLUSERDETAILS to get the date of signup compared to the refferal and another copy to get the nameonline of the refferrer.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-05-23 : 10:20:27

worked perfectly!

thank you so much!!

mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources