how to join these two queries results

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)

how to join these two queries results

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-11-13 : 09:06:01

Hi,

I have these two queries as posted below, and they are both returning the proper data, but I would like to combine them.
I'm not sure exactly how to do the join because I am JOIN'ing onto a modified column. As I want to join on the varchar version of the date.

The final combined query should just bring back the varchar date column, as well as "inbound_clicks_total" and "outbound_clicks_total" . 3 columns total.

Please see queries below

Any help is much appreciated!

thanks again!
mike123

------------------
--START OF FIRST QUERY
------------------

DECLARE @monthsAgo int
SELECT @monthsAgo = 0

SELECT CONVERT(varchar(10),IBC.clickDate,112) as clickDate, COUNT(IBC.clickID) AS inbound_clicks_total

FROM [tblClickLog_InBound] IBC

WHERE

clickDate >= dateadd(month,datediff(month,@monthsAgo ,getdate())-@monthsAgo ,0) and
clickDate < dateadd(month,datediff(month,@monthsAgo ,getdate())-@monthsAgo +1,0)

GROUP BY CONVERT(varchar(10),IBC.clickDate,112)

ORDER BY clickDate DESC

------------------
--END OF FIRST QUERY, START #2
------------------

DECLARE @monthsAgo int
SELECT @monthsAgo = 0

SELECT CONVERT(varchar(10),OBC.clickDate,112) as clickDate, COUNT(OBC.clickID) AS outbound_clicks_total

FROM [tblClickLog_OutBound] OBC

JOIN [tblLinkSwap_OutBoundLinks] OBL on OBL.linkID = OBC.linkID

WHERE OBL.partnerID = 1 AND

clickDate >= dateadd(month,datediff(month,@monthsAgo ,getdate())-@monthsAgo ,0) and
clickDate < dateadd(month,datediff(month,@monthsAgo ,getdate())-@monthsAgo +1,0)

GROUP BY CONVERT(varchar(10),OBC.clickDate,112)

ORDER BY clickDate DESC

------------------
--END OF SECOND QUERY
------------------

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-11-13 : 09:27:59

quote:
Originally posted by mike123

I'm not sure exactly how to do the join because I am JOIN'ing onto a modified column. As I want to join on the varchar version of the date.

No you don't. You want to join on the whole date value. Don't convert dates to strings.


DECLARE @monthsAgo int
SELECT @monthsAgo = 0

SELECT	coalesce(Inbound.clickdate, Outbound.clickdate) as clickdate,
	coalesce(Inbound.inbound_clicks_total, 0) as inbound_clicks_total,
	coalesce(Outbound.outbound_clicks_total, 0) as outbound_clicks_total
from	--Inbound
		(SELECT	dateadd(day, datediff(day, 0, clickDate), 0) as clickDate,
				COUNT(clickID) AS inbound_clicks_total
		FROM	tblClickLog_InBound
		WHERE	clickDate >= dateadd(month,datediff(month,@monthsAgo ,getdate())-@monthsAgo ,0)
				AND clickDate < dateadd(month,datediff(month,@monthsAgo ,getdate())-@monthsAgo +1,0)
		GROUP BY dateadd(day, datediff(day, 0, clickDate), 0)) Inbound
	full outer join --Outbound
		(SELECT	dateadd(day, datediff(day, 0, tblClickLog_OutBound.clickDate), 0) as clickDate,
				COUNT(tblClickLog_OutBound.clickID) AS outbound_clicks_total
		FROM	tblClickLog_OutBound
				JOIN [tblLinkSwap_OutBoundLinks] on tblLinkSwap_OutBoundLinks.linkID = tblClickLog_OutBound.linkID
		WHERE	tblLinkSwap_OutBoundLinks.partnerID = 1
				AND tblClickLog_OutBound.clickDate >= dateadd(month,datediff(month,@monthsAgo ,getdate())-@monthsAgo ,0)
				AND tblClickLog_OutBound.clickDate < dateadd(month,datediff(month,@monthsAgo ,getdate())-@monthsAgo +1,0)
		GROUP BY dateadd(day, datediff(day, 0, tblClickLog_OutBound..clickDate), 0)) Outbound
		on Inbound.clickdate = Outbound.clickdate

If it is not practically useful, then it is practically useless.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-11-13 : 09:54:00

Hi Blindman,

This seems to be working perfectly. I'm going thru it all so I can understand it now.

Thanks very much for your help!

cheers,
mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources