March 9, 2017 at 7:48 am
Hello, I have 2 tables of data to work with that I need to join and I am slightly stumped!
Table 1 is an exported list of people and the dates they have visited a certain site, this could be a variable number of visits:
PIDVisitDate VisitName
1001 01.01.17 Visit1
1001 01.02.17 Visit2
1001 01.03.17 Visit3
1002 01.02.17 Visit1
1002 01.03.17 Visit2
Table 2 is an exported list of questionnaire answers from these people and the dates they were completed:
PID DateCompleted Ans1 Ans2 Ans3
1001 02.01.17 1 2 3
1001 09.01.17 1 2 3
1001 16.01.17 1 2 3
1001 23.01.17 1 2 3
1001 30.01.17 1 2 3
1001 02.02.17 2 2 2
1001 09.02.17 2 2 2
1001 16.02.17 2 2 2
1001 23.02.17 2 2 2
1001 02.03.17 1 1 1
1001 09.03.17 1 1 1
1002 02.02.17 9 9 9
1002 09.02.17 9 9 9
1002 16.02.17 9 9 9
1002 23.02.17 9 9 9
1002 02.03.17 7 7 7
1002 09.03.17 7 7 7
What I am looking to do is join these tables together and group the questionnaires based
on the date they were completed and the dates of the visits they attended, as below:
PID VisitDate VisitName DateCompleted Ans1 Ans2 Ans3
1001 01.01.17 Visit1 02.01.17 1 2 3
1001 01.01.17 Visit1 09.01.17 1 2 3
1001 01.01.17 Visit1 16.01.17 1 2 3
1001 01.01.17 Visit1 23.01.17 1 2 3
1001 01.01.17 Visit1 30.01.17 1 2 3
1001 01.02.17 Visit2 02.02.17 2 2 2
1001 01.02.17 Visit2 09.02.17 2 2 2
1001 01.02.17 Visit2 16.02.17 2 2 2
1001 01.02.17 Visit2 23.02.17 2 2 2
1001 01.03.17 Visit3 02.03.17 1 1 1
1001 01.03.17 Visit3 09.03.17 1 1 1
1002 01.02.17 Visit1 02.02.17 9 9 9
1002 01.02.17 Visit1 09.02.17 9 9 9
1002 01.02.17 Visit1 16.02.17 9 9 9
1002 01.02.17 Visit1 23.02.17 9 9 9
1002 01.03.17 Visit2 02.03.17 7 7 7
1002 01.03.17 Visit2 09.03.17 7 7 7
I just cannot see how I can pull this together, I may be missing something stupid but I just cannot see it.
Seriously hoping someone can help me out and make me feel a bit lees stupid!!
Please find below the scripts to create the base data:
CREATE TABLE #person
(
PID INT,
VisitDate DATETIME,
VisitName VARCHAR(6)
);
CREATE TABLE #answers
(
PID INT,
DateCompleted DATETIME,
Ans1 INT,
Ans2 INT,
Ans3 INT
);
INSERT INTO #person
(
PID,
VisitDate,
Visitname
)
VALUES
(1001, '01-Jan-2017', 'Visit1'),
(1001, '01-Feb-2017', 'Visit2'),
(1001, '01-Mar-2017', 'Visit3'),
(1002, '01-Feb-2017', 'Visit1'),
(1002, '01-Mar-2017', 'Visit2');
INSERT INTO #answers
(
PID,
DateCompleted,
Ans1,
Ans2,
Ans3
)
VALUES
(1001, '02-Jan-2017', 1, 2, 3),
(1001, '09-Jan-2017', 1, 2, 3),
(1001, '16-Jan-2017', 1, 2, 3),
(1001, '23-Jan-2017', 1, 2, 3),
(1001, '30-Jan-2017', 1, 2, 3),
(1001, '02-Feb-2017', 2, 2, 2),
(1001, '09-Feb-2017', 2, 2, 2),
(1001, '16-Feb-2017', 2, 2, 2),
(1001, '23-Feb-2017', 2, 2, 2),
(1001, '02-Mar-2017', 1, 1, 1),
(1001, '02-Mar-2017', 1, 1, 1),
(1002, '02-Feb-2017', 9, 9, 9),
(1002, '09-Feb-2017', 9, 9, 9),
(1002, '16-Feb-2017', 9, 9, 9),
(1002, '23-Feb-2017', 9, 9, 9),
(1002, '02-Mar-2017', 7, 7, 7),
(1002, '02-Mar-2017', 7, 7, 7);
March 9, 2017 at 8:10 am
This looks right:WITH Visits AS (
SELECT
PID
, VisitDate
, VisitName
, LEAD(VisitDate,1,NULL) OVER (PARTITION BY PID ORDER BY VisitDate) AS NextVisitDate
FROM #person
)
SELECT
v.PID
, v.VisitDate
, v.VisitName
, a.DateCompleted
, a.Ans1
, a.Ans2
, a.Ans3
FROM Visits v
JOIN #answers a ON v.PID = a.PID
AND a.DateCompleted >= v.VisitDate AND a.DateCompleted < COALESCE(v.NextVisitDate,'9999-12-31');
March 9, 2017 at 8:14 am
WITH ctePerson AS (
SELECT *
, nextVisitDate = LEAD(p.VisitDate, 1, '2199-12-31') OVER (PARTITION BY PID ORDER BY p.VisitDate)
FROM #person AS p
)
SELECT
p.PID
, p.VisitDate
, p.VisitName
, a.DateCompleted
, a.Ans1
, a.Ans2
, a.Ans3
FROM ctePerson AS p
INNER JOIN #answers AS a
ON p.PID = a.PID
WHERE a.DateCompleted >= p.VisitDate
AND a.DateCompleted < p.nextVisitDate;
March 9, 2017 at 8:22 am
What about this?
WITH TEMP_CTE AS(
SELECT *, LEAD(VisitDate, 1, '29990101') OVER(PARTITION BY PID ORDER BY VisitDate ASC) AS NextVisit FROM #Person
)
SELECT TEMP_CTE.PID, TEMP_CTE.VisitDate, TEMP_CTE.VisitName, #answers.DateCompleted, #answers.Ans1, #answers.Ans2, #answers.Ans3 FROM TEMP_CTE JOIN #answers ON TEMP_CTE.PID = #answers.PID
WHERE
TEMP_CTE.VisitDate <= #answers.DateCompleted AND TEMP_CTE.NextVisit > #answers.DateCompleted
ORDER BY TEMP_CTE.PID, VisitDate, DateCompleted
March 10, 2017 at 3:28 am
Never knew about the LEAD function- thought I might have come across to the requirement to use this at least once before!!
Thank you so much, I know I can rely on the members here every time my lack of T-SQL skills comes to bite me!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply