Puzzler: Three consecutive rows
-
I have puzzled over this one for a while, and can't seem to get it.
I have a table of patients, doctor's appointments, and doctor's names.
DECLARE @Appt TABLE (
PATIENT decimal(12,0),
SER_DT datetime,
PROV varchar(30))
INSERT INTO @Appt VALUES (1, '2007-03-01', 'Smith')
INSERT INTO @Appt VALUES (1, '2007-03-02', 'Smith')
INSERT INTO @Appt VALUES (1, '2007-03-03', 'Smith')
INSERT INTO @Appt VALUES (1, '2007-03-04', 'Jones')
INSERT INTO @Appt VALUES (2, '2007-03-01', 'Doe')
INSERT INTO @Appt VALUES (2, '2007-03-04', 'Doe')
INSERT INTO @Appt VALUES (2, '2007-03-08', 'Jones')
INSERT INTO @Appt VALUES (2, '2007-03-10', 'Jones')
INSERT INTO @Appt VALUES (2, '2007-03-11', 'Jones')
INSERT INTO @Appt VALUES (2, '2007-03-12', 'Doe')
INSERT INTO @Appt VALUES (2, '2007-03-13', 'Doe')
INSERT INTO @Appt VALUES (2, '2007-03-14', 'Doe')
INSERT INTO @Appt VALUES (3, '2007-03-01', 'Jones')
INSERT INTO @Appt VALUES (3, '2007-03-07', 'Doe')
INSERT INTO @Appt VALUES (3, '2007-03-14', 'Jones')
I want to select the first occurance of patient and doctor for each patient who has seen the same doctor three times in a row. In my sample data above, I should select patient #1 and Dr. Smith, patient #2 and Dr. Jones (but not Dr. Doe since #2 saw Jones three consecutive times first), and no one for patient #3.
I was able to implement a solution using a cursor, but I know there's a set-based approach to accomplish this. Any ideas? Thanks.
Update: Sorry for the syntax errors, I had to cut down the production code quite a bit to make it fit here, and I did it poorly. I've fixed it now (I think).
There is no "i" in team, but idiot has two. -
-- Prepare sample data
DECLARE @Appt TABLE (Patient INT, Ser_DT DATETIME, Prov VARCHAR(30))
INSERT @Appt
SELECT 1, '2007-03-01', 'Smith' UNION ALL
SELECT 1, '2007-03-02', 'Smith' UNION ALL
SELECT 1, '2007-03-03', 'Smith' UNION ALL
SELECT 1, '2007-03-04', 'Jones' UNION ALL
SELECT 7, '2007-03-01', 'Doe' UNION ALL
SELECT 7, '2007-03-04', 'Doe' UNION ALL
SELECT 7, '2007-03-08', 'Jones' UNION ALL
SELECT 7, '2007-03-10', 'Jones' UNION ALL
SELECT 7, '2007-03-11', 'Jones' UNION ALL
SELECT 7, '2007-03-12', 'Doe' UNION ALL
SELECT 7, '2007-03-13', 'Doe' UNION ALL
SELECT 7, '2007-03-14', 'Doe' UNION ALL
SELECT 3, '2007-03-01', 'Jones' UNION ALL
SELECT 3, '2007-03-07', 'Doe' UNION ALL
SELECT 3, '2007-03-14', 'Jones'
-- Show the expected output
SELECT y.Patient,
COUNT(*) AS Occurancies
FROM (
SELECT x.Patient,
x.First_DT,
x.Second_DT,
MIN(a.Ser_DT) AS Third_DT
FROM (
SELECT a1.Patient,
a1.Ser_DT AS First_DT,
MIN(a2.Ser_DT) AS Second_DT
FROM @Appt AS a1
INNER JOIN @Appt AS a2 ON a2.Patient = a1.Patient AND a2.Ser_DT > a1.Ser_DT
GROUP BY a1.Patient,
a1.Ser_DT
) AS x
INNER JOIN @Appt AS a ON a.Patient = x.Patient AND a.Ser_DT > x.Second_DT
GROUP BY x.Patient,
x.First_DT,
x.Second_DT
) AS y
INNER JOIN @Appt AS z1 ON z1.Patient = y.Patient AND z1.Ser_DT = y.First_DT
INNER JOIN @Appt AS z2 ON z2.Patient = y.Patient AND z2.Ser_DT = y.Second_DT
INNER JOIN @Appt AS z3 ON z3.Patient = y.Patient AND z3.Ser_DT = y.Third_DT
WHERE z1.Prov = z2.Prov
AND z2.Prov = z3.Prov
GROUP BY y.Patient
ORDER BY y.Patient
N 56°04'39.16"
E 12°55'05.25" -
-- Prepare sample data
DECLARE @Appt TABLE (Patient INT, Ser_DT DATETIME, Prov VARCHAR(30))
INSERT @Appt
SELECT 1, '2007-03-01', 'Smith' UNION ALL
SELECT 1, '2007-03-02', 'Smith' UNION ALL
SELECT 1, '2007-03-03', 'Smith' UNION ALL
SELECT 1, '2007-03-04', 'Jones' UNION ALL
SELECT 7, '2007-03-01', 'Doe' UNION ALL
SELECT 7, '2007-03-04', 'Doe' UNION ALL
SELECT 7, '2007-03-08', 'Jones' UNION ALL
SELECT 7, '2007-03-10', 'Jones' UNION ALL
SELECT 7, '2007-03-11', 'Jones' UNION ALL
SELECT 7, '2007-03-12', 'Doe' UNION ALL
SELECT 7, '2007-03-13', 'Doe' UNION ALL
SELECT 7, '2007-03-14', 'Doe' UNION ALL
SELECT 3, '2007-03-01', 'Jones' UNION ALL
SELECT 3, '2007-03-07', 'Doe' UNION ALL
SELECT 3, '2007-03-14', 'Jones'
-- Stage the data
DECLARE @Stage TABLE (RecID INT IDENTITY(1, 1), Patient INT, Prov VARCHAR(30))
INSERT @Stage
SELECT y.Patient,
z1.Prov
FROM (
SELECT x.Patient,
x.First_DT,
x.Second_DT,
MIN(a.Ser_DT) AS Third_DT
FROM (
SELECT a1.Patient,
a1.Ser_DT AS First_DT,
MIN(a2.Ser_DT) AS Second_DT
FROM @Appt AS a1
INNER JOIN @Appt AS a2 ON a2.Patient = a1.Patient AND a2.Ser_DT > a1.Ser_DT
GROUP BY a1.Patient,
a1.Ser_DT
) AS x
INNER JOIN @Appt AS a ON a.Patient = x.Patient AND a.Ser_DT > x.Second_DT
GROUP BY x.Patient,
x.First_DT,
x.Second_DT
) AS y
INNER JOIN @Appt AS z1 ON z1.Patient = y.Patient AND z1.Ser_DT = y.First_DT
INNER JOIN @Appt AS z2 ON z2.Patient = y.Patient AND z2.Ser_DT = y.Second_DT
INNER JOIN @Appt AS z3 ON z3.Patient = y.Patient AND z3.Ser_DT = y.Third_DT
WHERE z1.Prov = z2.Prov
AND z2.Prov = z3.Prov
ORDER BY y.Patient
-- Show the expected output
SELECT s.Patient,
s.Prov
FROM @Stage AS s
INNER JOIN (
SELECT Patient,
MIN(RecID) AS RecID
FROM @Stage
GROUP BY Patient
) AS x ON x.RecID = s.RecID
N 56°04'39.16"
E 12°55'05.25" -
Thanks, Peter.
There is no "i" in team, but idiot has two. -
Just another option
SELECT
A.PATIENT,
A.PROV
FROM
@Appt A
INNER JOIN
(
SELECT
PATIENT,
MIN(SER_DT) SER_DT
FROM
@Appt oX
WHERE
(SELECT COUNT(DISTINCT PROV) FROM (SELECT TOP 3 PROV, SER_DT FROM @appt iX WHERE iX.PATIENT = oX.PATIENT AND iX.SER_DT >= oX.SER_DT ORDER BY iX.PATIENT ASC, iX.SER_DT ASC) A GROUP BY PROV HAVING COUNT(SER_DT) = 3) = 1
GROUP BY
PATIENT
) B
ON
A.PATIENT = B.PATIENT AND
A.SER_DT = B.SER_DT
-
Hi ,
Try this one out.....
select * from @Appt a
join
(select patient,prov from @Appt
group by patient,prov
having count(*) =3) b
on a.patient=b.patient and
a.prov=b.prov
Thanks & Regards
Amit Gupta
-
Amit, add this test data to the existing one.
SELECT
7, '2007-03-09', 'Doe' UNION ALL
N 56°04'39.16"
E 12°55'05.25"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply