How Can I match tables on "similar dates" in SQL

Hi All,

I have the query below. It is a filthy piece of code, but the general premis is that amongst other things, I amd trying to join 2 tables based on a date. Unfortunatly the dates do not match exactly, so I have to match on the closest date I can find, be it earlier or later, but with a maximum window of +/- 10 mins, and if it does not exists, create it.

This is throwing an error saying Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.


Can anybody help? It's driving me mad!

SELECT 
       ISNULL(C.[DATE], DR.[DATE]) [DATE]
      ,ISNULL(C.[CLIENT_ID], DR.[CLIENT_ID]) [CLIENT_ID]
      ,ISNULL(C.[CAMPAIGN_ID], DR.[CAMPAIGN_ID]) [CAMPAIGN_ID]
      ,ISNULL(C.[SITE_ID], DR.[SITE_ID]) [SITE_ID]
      ,ISNULL(C.[PLACEMENT_ID], DR.[PLACEMENT_ID]) [PLACEMENT_ID]
      ,ISNULL(C.[CREATIVE_ID], DR.[CREATIVE_ID]) [CREATIVE_ID]
      ,ISNULL(C.[RULE_ID], DR.[RULE_ID]) [RULE_ID]
      ,ISNULL(C.[MEDIAPLEX_COOKIE_ID], DR.[MEDIAPLEX_COOKIE_ID]) [MEDIAPLEX_ID]
      ,ISNULL(C.[PARAMETER_STRING], DR.[PARAMETER_STRING]) [PARAMETER_STRING]
      ,ISNULL(C.[PERFORMANCE_TRACKER_1], DR.[PERFORMANCE_TRACKER_1]) [PERFORMANCE_TRACKER_1]
      ,ISNULL(C.[PERFORMANCE_TRACKER_2], DR.[PERFORMANCE_TRACKER_2]) [PERFORMANCE_TRACKER_2]
      ,ISNULL(C.[PERFORMANCE_TRACKER_3], DR.[PERFORMANCE_TRACKER_3]) [PERFORMANCE_TRACKER_3]
      ,C.[Country_ID]
      ,C.[State/Province]
      ,C.[DMA]
      ,C.[UserAgentString]
      ,ISNULL(C.[Date], DR.[Event_Date]) [EventDate]
      ,DR.[TimeOfDayConversion]
FROM 
		[dbo].[Click] C
RIGHT OUTER JOIN [dbo].[DerivedROI] DR
	ON 
		ABS(DATEDIFF(second, C.Date, DR.Event_Date)) 
		= 
		( 
		SELECT MIN(ABS(DATEDIFF(second, C.[Date], DR2.[Event_Date]))) 
			FROM [dbo].[DerivedROI] AS DR2 
		)
	AND C.[MEDIAPLEX_COOKIE_ID] = DR.[MEDIAPLEX_COOKIE_ID]
	AND C.[CLIENT_ID] = DR.[CLIENT_ID]
WHERE	
	DR.[Date] > CAST(FLOOR(CAST(GETDATE() - 5 AS FLOAT)) AS DATETIME)

Select allOpen in new window