Joining 3 tables on multiple fields - SQL Server

0

Please login or register to vote for this query.

(click on this box to dismiss)

Computational Science

Q&A for scientists using computers to solve scientific problems

CREATE TABLE #User_Table
(
	[Lead] INT,
	[ID] INT
)

INSERT INTO #User_Table
(
	[Lead],
	[ID]
)
SELECT 1, 5 UNION
SELECT 1, 8 UNION
SELECT 1, 12 UNION
SELECT 2, 13 UNION
SELECT 2, 14 UNION
SELECT 2, 15 UNION
SELECT 2, 18 UNION
SELECT 2, 24

CREATE TABLE #Interactions
(
	[Int_Date] SMALLDATETIME,
	[ID] INT,
	[Interaction] VARCHAR(15)
)

INSERT INTO #Interactions
(
	[Int_Date],
	[ID],
	[Interaction]
)
SELECT '2012-03-01', 5, 'Hang Up' UNION
SELECT '2012-03-01', 13, 'Reset' UNION
SELECT '2012-03-01', 8, 'Basic' UNION
SELECT '2012-03-01', 13, 'Basic' UNION
SELECT '2012-03-02', 14, 'Reset' UNION
SELECT '2012-03-02', 12, 'Advanced' UNION
SELECT '2012-03-02', 12, 'Hang Up' UNION
SELECT '2012-03-02', 24, 'Reset' UNION
SELECT '2012-03-02', 5, 'Basic'

CREATE TABLE #Repeats
(
	[Repeat_d] SMALLDATETIME,
	[ID] INT,
	[R_Interaction] VARCHAR(15)
)

INSERT INTO #Repeats
(
	[Repeat_d],
	[ID],
	[R_Interaction]
)
SELECT '2012-03-01', 13, 'Reset' UNION
SELECT '2012-03-01', 8, 'Basic' UNION
SELECT '2012-03-02', 14, 'Reset' UNION
SELECT '2012-03-02', 12, 'Advanced' UNION
SELECT '2012-03-02', 24, 'Reset'

DECLARE
	@StartDate SMALLDATETIME,
	@EndDate SMALLDATETIME,
	@TL_ID SMALLINT

SELECT
	@StartDate = '2012-03-01',
	@EndDate = '2012-03-31',
	@TL_ID = '2'

SELECT
	DATEADD(DD, DATEDIFF(DD, 0, [Int_Date]), 0) AS [Int_Date],
	u.[Lead],
	i.[ID],
	COUNT(1) + COUNT(r.[ID]) AS [Total_Interactions],
	COUNT(r.[ID]) AS [Total_Repeats],
	i.[Interaction]
FROM
	#Interactions AS i WITH (NOLOCK)
JOIN
	#User_Table AS u WITH (NOLOCK)
ON
	u.[ID] = i.[ID]
LEFT OUTER JOIN
	#Repeats AS r WITH (NOLOCK)
ON
	r.[ID] = i.[ID]
AND	r.[Repeat_d] = i.[Int_Date]
AND	r.[R_Interaction] = i.[Interaction]
WHERE
	i.[Int_Date] BETWEEN @StartDate AND @EndDate
--AND	u.[Lead] = @TL_ID
GROUP BY
	DATEADD(DD, DATEDIFF(DD, 0, [Int_Date]), 0),
	u.[Lead],
	i.[ID],
	i.[Interaction]
ORDER BY
	[Int_Date],
	u.[Lead],
	i.[ID],
	[Total_Repeats] DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results