Help with Query - 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 Query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-03-20 : 00:59:10

I have a table called REFEREMAIL

with the following columns

EMAILID (Identity)
REFERUSERID
EMAILADDRESS
REFERDATE

Each time a user refers a user an email is sent to that user, and the email address it is sent to is logged, as well as users ID, and time of refering. If the user is not registered I insert a 0.

What I need to do is get a list of the top referers. When a new person signs up for an account with an emailaddress that is in the REFEREMAIL table with a date before the time of signup a succesful refer has been made. The REFEREMAIL table is going to have alot of rows that are not counted.

I need to get an ordered count of each users number of sucessful referrals. My table that contains all the accounts is called USERDETAILS. The columns in this table taht come into play are "Emailaddress", "dateJoined", and "nameOnline".

This query is a bit over my head.. I appreciate any help.. Hopefully that makes sense, it was a bit confusing to explain.

I am also open to any suggestions on how to do it better, I think this is a pretty good solution as far as I can see.

Thanks alot

Mike123

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-03-20 : 01:36:22

The count of each referral mail address ordered by the count. Is this you are trying for?
USE TEMPDB
GO
CREATE TABLE REFEREMAIL (EMAILID INT IDENTITY(1,1), REFERERUSERID NUMERIC, EMAILADDRESS VARCHAR(20), REFERDATE DATETIME)
GO
SELECT EMAILADDRESS, COUNT(*) AS NO_OF_REFEREDUSERID
FROM REFEREMAIL
GROUP BY EMAILADDRESS
ORDER BY NO_OF_REFEREDUSERID
GO

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

Edited by - samsekar on 03/20/2003 01:47:13

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-03-20 : 02:24:21

Thanks.. thats almost what i need but not quite..

I dont want just the count, but I want the count of emails in the table - REFEREMAILS that exist in USERDETAILS where the date is earlier in REFEREMAILS than it is in USERDETAILS. Sorry its confusing i hope i explained ok ..

cheers
mike123

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-03-20 : 02:51:07

HTH..
CREATE TABLE USERDETAILS (EMAILADDRESS VARCHAR(20), USERDATE DATETIME)
GO
SELECT A.EMAILADDRESS, COUNT(A.EMAILADDRESS) AS NO_OF_REFEREDUSERID
FROM REFEREMAIL AS A
INNER JOIN USERDETAILS AS B
ON A.EMAILADDRESS = B.EMAILADDRESS
AND A.REFERDATE<B.USERDATE
GROUP BY A.EMAILADDRESS
ORDER BY NO_OF_REFEREDUSERID
GO

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-03-20 : 14:53:38

Thanks again.. I think my directions are maybe not the clearest.. let me provide some sample data

REFEREMAILS

REFERID / REFERUSERID / EMAILADDRESS / REFERDATE

62 100 bob@hotmail.com 2000-01-01
63 100 bill@hotmail.com 2000-01-01
64 100 jack@hotmail.com 2000-01-01
65 100 john@hotmail.com 2000-01-01

USERDETAILS

USERID / EMAILADDRESS / DATE / NAMEONLINE

100 sean@hotmail.com 2001-09-13 03:34:06.00 Sean
409 bob@hotmail.com 2001-09-13 03:34:06.000 Bob
410 bill@hotmail.com 2001-09-13 03:41:43.000 Bill
411 jack@hotmail.com 2001-09-17 01:28:02.000 Jack
412 john@hotmail.com 2001-09-13 04:34:38.000 John

In this situation "SEAN" has referred 4 people, all of which who have signed up after they got referred. I want the recordset returned to be like this

SEAN 4

Thanks alot
MIke123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-20 : 15:03:18

How about this?

SELECT nameonline, COUNT(referuserid) AS ReferredCount
FROM USERDETAILS
INNER JOIN REFEREMAILS ON USERDETAILS.userid = REFEREMAILS.referuserid
GROUP BY nameonline, referuserid

Tara

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-03-20 : 15:12:00

perfect!, thanks alot!

mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources