help with modifying 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 2005 Forums

Transact-SQL (2005)

help with modifying query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-05-29 : 15:39:56

Hi,

I have a query that I am working on that involves 2 tables. The query below is working correctly and bringing back the desired results, except I want to add 1 more column of data, and I'm not exactly sure how to write it.

What I want to add is the following data.

For each row that is brought back we want to have the COUNT(*) of users who joined the website (tbluserdetails) where their tbluserdteails.date is > the tblreferemails.referDate

Effectively we are attempting to track how well the "tell a friend" via email feature works, and converts to other joined members.

Any assistance is much appreciated!!

thanks once again

mike123

SELECT CONVERT(varchar(10),referDate,112) AS referDate,

SUM ( CASE WHEN emailSendCount = 0 THEN 1 ELSE 0 END ) as '0',
SUM ( CASE WHEN emailSendCount = 1 THEN 1 ELSE 0 END ) as '1',
SUM ( CASE WHEN emailSendCount = 2 THEN 1 ELSE 0 END ) as '2',
SUM ( CASE WHEN emailSendCount = 3 THEN 1 ELSE 0 END ) as '3',
SUM ( CASE WHEN emailSendCount > 3 THEN 1 ELSE 0 END ) as '> 3',
SUM ( CASE WHEN emailSendCount > 0 THEN 1 ELSE 0 END ) as 'totalSent',

count(*) as totalRefers,
count(distinct(referUserID)) as totalUsers,

SUM ( CASE WHEN emailAddress like '%hotmail%' THEN 1 ELSE 0 END ) as 'hotmail',
SUM ( CASE WHEN emailAddress like '%hotmail.co.uk%' THEN 1 ELSE 0 END ) as 'hotmail.co.uk',
SUM ( CASE WHEN emailAddress like '%yahoo.ca%' THEN 1 ELSE 0 END ) as 'yahoo.ca',
SUM ( CASE WHEN emailAddress like '%yahoo.co.uk%' THEN 1 ELSE 0 END ) as 'yahoo.co.uk',
SUM ( CASE WHEN emailAddress like '%gmail%' THEN 1 ELSE 0 END ) as 'gmail',
SUM ( CASE WHEN emailAddress like '%aol%' THEN 1 ELSE 0 END ) as 'aol',
SUM ( CASE WHEN emailAddress like '%yahoo%' THEN 1 ELSE 0 END ) as 'yahoo',

SUM ( CASE WHEN referalTypeID = 1 THEN 1 ELSE 0 END ) as 'manual',
SUM ( CASE WHEN referalTypeID = 2 THEN 1 ELSE 0 END ) as 'auto'

FROM tblreferemails R

WHERE DateDiff(dd, referDate, GetDate()) < 5

GROUP BY CONVERT(varchar(10),referDate,112)

ORDER BY referDate DESC

CREATE TABLE [dbo].[tblUserDetails]
(
[UserID] [int] IDENTITY(1,1) NOT NULL,
[NameOnline] [varchar](15) NULL,
[EmailAddress] [varchar](50) NULL,
[Date] [datetime] NULL,
[Active] [tinyint] NULL
)

CREATE TABLE [dbo].[tblReferEmails](
[emailID] [int] IDENTITY(1,1) NOT NULL,
[referUserID] [int] NOT NULL,
[destinationName] [varchar](50) NULL,
[emailaddress] [varchar](50) NOT NULL,
[referDate] [datetime] NOT NULL,
[referalTypeID] [int] NULL,
[deleted] [int] NULL,
[emailSendCount] [int] NULL,
[lastDateSent] [smalldatetime] NULL
) ON [PRIMARY]

GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 16:49:35

1) WHERE DateDiff(dd, referDate, GetDate()) < 5
2) LIKE '%aol%'

-----------
1) WHERE referDate >= DATEADD(DAY, DATEDIFF(DAYS, '19000105', GETDATE()), '19000101')
to use the index over ReferDate

2) put a @ in the emailaddress, otherwhise sylve.maoli@xyz.com is regarded as aol user.
LIKE '%@aol.%'

The very same applies to other email counters

SUM ( CASE WHEN emailAddress like '%@hotmail.%' THEN 1 ELSE 0 END ) as 'hotmail',
SUM ( CASE WHEN emailAddress like '%@hotmail.co.uk' THEN 1 ELSE 0 END ) as 'hotmail.co.uk',
SUM ( CASE WHEN emailAddress like '%@yahoo.ca' THEN 1 ELSE 0 END ) as 'yahoo.ca',
SUM ( CASE WHEN emailAddress like '%@yahoo.co.uk' THEN 1 ELSE 0 END ) as 'yahoo.co.uk',
SUM ( CASE WHEN emailAddress like '%@gmail.%' THEN 1 ELSE 0 END ) as 'gmail',
SUM ( CASE WHEN emailAddress like '%@aol.%' THEN 1 ELSE 0 END ) as 'aol',
SUM ( CASE WHEN emailAddress like '%@yahoo.%' THEN 1 ELSE 0 END ) as 'yahoo',

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 17:24:26

Post some sample data. It will be easier to understand.

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-05-29 : 19:37:59

Hi Peso,

good point on the '@" tip. I've added it now, and am happy to know my results will be more accurate :)

Regarding sample data sure, I have included some below.

The data illustrates that userID =1 (bob) sent out some referral emails on 2 dates (01/01/2008 and 02/01/2008)

He referred 5 people, 2 of them signed up for an account after he sent them an email. Because their joinDate was after the date he sent an email to them, we will assume that the user signed up because of that email.

Hope this makes sense, if not please let me know what else I can do.

Thanks again Peso!!

mike123

tblUserDetails
userID/nameOnline/emailAddress/date

1 / bob / bob@hotmail.com / 01/01/2008
2 / bill / bill@hotmail.com / 02/01/2008
3 / jack / jack@hotmail.com / 03/01/2008
4 / tom / tom@hotmail.com / 04/01/2008

tblReferEmails

referUserID / emailAddress /referDate

1 / tom@hotmail.com / 01/01/2008
1 / bill@hotmail.com / 02/01/2008
1 / notme@hotmail.com / 02/01/2008
1 / notmeeither@hotmail.com / 02/01/2008
1 / defnotme@hotmail.com / 02/01/2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources