help 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 2000 Forums

Transact-SQL (2000)

help modifying query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-11-09 : 05:18:13

Hi,

I have the following query below that I need to add on to. I now have a table with the following design. I need to add one more column returned to this query that is a COUNT(*) of the tblAffiliateClicks that have the corresponding affilLinkID to the affilID and are in the proper date range. Can anyone lend me a hand on this one ?

Thanks - much appreciated

mike123

tblAffiliateClicks

clickID
affilLinkID
clickDate



CREATE PROCEDURE dbo.select_AffiliateReport_Tier1_Total
	(
	 @affilID int,
	 @dateStart smalldatetime,
	 @dateEnd smalldatetime
	)

AS SET NOCOUNT ON

SELECT 

	Count(*) as Signups,	
	ISNULL( SUM ( CASE WHEN mt.membershipTypeID = 1 THEN 1 ELSE 0 END ), 0) as Basic,
	ISNULL( SUM ( CASE WHEN mt.membershipTypeID = 2 THEN 1 ELSE 0 END ), 0)  as basic2

FROM 
	tblUserDetails ud
INNER JOIN tblMemberships mem ON mem.purchasedFor = ud.UserID
INNER JOIN tblMembershipTypes mt ON mt.membershipTypeId = mem.membershipTypeId
INNER JOIN tblAffiliateLinks afl ON afl.affilLinkID = ud.affilLinkID
INNER JOIN tblAffiliateDetails ad ON ad.affilID = afl.affilID


INNER JOIN
      (select clickID, affilLinkID, count(*) as NumOfClicks
       from   tblAffiliateClicks
       where  clickDate netween @dateStart AND @dateEnd
       group by clickID, affilLinkID) t1 ON t1.clickID = ud.clickID AND t1.affilLinkID=ud.affilLinkID





WHERE
	ad.affilID = isNull(@affilID, ad.affilID) and
	mem.[dateStart] BETWEEN @dateStart AND @dateEnd

GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-09 : 06:01:45

something like this?


...
INNER JOIN
      (select clickID, affilLinkID, count(*) as NumOfClicks
       from   tblAffiliateClicks
       where  clickDate netween @dateStart AND @dateEnd
       group by clickID, affilLinkID) t1 ON t1.clickID = ud.clickID AND t1.affilLinkID=ud.affilLinkID

Go with the flow & have fun! Else fight the flow

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-11-09 : 20:27:21

Hi Spirit1

I get the following error when making modifications to the SPROC as you suggested. (the sproc above has been edited with changes)

Server: Msg 207, Level 16, State 3, Procedure select_AffiliateReport_Tier1_Total, Line 10
Invalid column name 'clickID'.

I'm trying to follow whats happening in the query and I'm unsure of this part right here.

t1 ON t1.clickID = ud.clickID

There is only one column named "clickID" in all the tables. This column appears only in tblAffiliateClicks and is the identity column. I only need the count of total clicks so I'm unsure if I really need to select more than that?

Thanks once again for your help!

Mike123

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-10 : 01:01:27

quote:
Originally posted by mike123

Hi Spirit1

I get the following error when making modifications to the SPROC as you suggested. (the sproc above has been edited with changes)

Server: Msg 207, Level 16, State 3, Procedure select_AffiliateReport_Tier1_Total, Line 10
Invalid column name 'clickID'.

I'm trying to follow whats happening in the query and I'm unsure of this part right here.

t1 ON t1.clickID = ud.clickID

There is only one column named "clickID" in all the tables. This column appears only in tblAffiliateClicks and is the identity column. I only need the count of total clicks so I'm unsure if I really need to select more than that?

Thanks once again for your help!

Mike123

as you just mentioned, ud.clickid does not exist (tblUserDetails)

--------------------
keeping it simple...

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-11-10 : 05:02:50

yes I know it doesnt exist ?? I really don't know what your trying to get at

are you saying just remove the join -

t1 ON t1.clickID = ud.clickID ???

I'm also wondering why there is select clickID, affilLinkID, count(*) as NumOfClicks as I think I would just need count(clickID) as I am only going after the count?

Im basically confused as to whether what I needed wasnt clear and the query was written with other goals in mind as it seems to select more columns then necessary and join onto a column that doesnt exist.

also why a group by if i just need 1 column with 1 value ?

Thanks
mike123

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-11-10 : 05:33:01

Hi Mike

What about adding a subquery ?



SELECT 

	Count(*) as Signups,	
	ISNULL( SUM ( CASE WHEN mt.membershipTypeID = 1 THEN 1 ELSE 0 END ), 0) as Basic,
	ISNULL( SUM ( CASE WHEN mt.membershipTypeID = 2 THEN 1 ELSE 0 END ), 0)  as basic2,
	(
	SELECT Count(*) 
	FROM
		tblAffiliateClicks tac
	WHERE
		clickDate netween @dateStart AND @dateEnd and
		tac.affiliLinkID = afl.affiliLinkID ) as NumOfClicks
		

FROM 
	tblUserDetails ud
INNER JOIN tblMemberships mem ON mem.purchasedFor = ud.UserID
INNER JOIN tblMembershipTypes mt ON mt.membershipTypeId = mem.membershipTypeId
INNER JOIN tblAffiliateLinks afl ON afl.affilLinkID = ud.affilLinkID
INNER JOIN tblAffiliateDetails ad ON ad.affilID = afl.affilID


INNER JOIN
      (select clickID, affilLinkID, count(*) as NumOfClicks
       from   tblAffiliateClicks
       where  clickDate netween @dateStart AND @dateEnd
       group by clickID, affilLinkID) t1 ON t1.clickID = ud.clickID AND t1.affilLinkID=ud.affilLinkID

WHERE
	ad.affilID = isNull(@affilID, ad.affilID) and
	mem.[dateStart] BETWEEN @dateStart AND @dateEnd

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources