Recent Events - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Recent Events

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-12-18 : 07:15:24

I have a table of dates when a patient has been seen in clinic e.g.

pk_ClinicID, fk_PatientID, ClinicDate,...
1 1 20031201
2 1 20030712
3 2 20021105
4 1 20030109

Any patient can be seen more than once in a clinic. What I need is a query that tells me if the two most recent clinics for each patient were within 6 months of each other

thanks

steve

Steve no function beer well without

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-12-18 : 08:00:04

A quick one here :


SELECT	DISTINCT A.patientID AS 'patients who have had two visits within the last 6 months'
FROM	APP AS A
INNER JOIN (
SELECT patientID, MAX(clinicDate) AS THEDATE
FROM APP
GROUP BY patientID
HAVING COUNT(*) >= 2) AS B
ON A.patientID = B.patientID
WHERE	A.clinicDate <> B.THEDATE
AND	DATEDIFF(MM, A.clinicDate, B.THEDATE) <= 6
GO

________________
Make love not war!

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-18 : 08:16:49

quote:
SELECT A.clinicID, A.patientID, A.clinicDate
FROM APP AS A
INNER JOIN (
SELECT patientID, MAX(clinicDate) AS THEDATE
FROM APP
GROUP BY patientID
HAVING COUNT(*) >= 2) AS B
ON A.patientID = B.patientID
WHERE A.clinicDate <> B.THEDATE
AND DATEDIFF(MM, A.clinicDate, B.THEDATE) <= 6

Won't that return all records for a patient id within the last 6 months?

When I reread the requirement, Steve is not saying what data he wants, only "if the two most recent clinics for each patient were within 6 months".

Returning each PatientID that met that criteria would fill the bill.

Steve, could you tell us a little more about what recordset you would like to have?

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-12-18 : 08:30:09

Sam, you're totally right, the query does return duplicate patientIDs if the patient happens to have say 3 or 4 clinic visits within 6 months of each other. You could place a DISTINSCT with the SELECT which will still return the correct results.

Oh, and he does want the 2 most recent and not just any two.

________________
Make love not war!

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-12-19 : 04:55:27

Thanks to all who replied so farOK. I'll try to explain a little more in the hope that it will help.

I need to look at the two most recent clinics for each patient (some will have 1 or 0 clinics so they can be excluded). For these two clinics I need to know if they happened within 6 months of each other or not.

So the answer that I ultimately need is something like

PatientID, MostRecentClinicDate, 2ndMostRecentClinicDate, Within6Months?
1 20031201, 20030712, Yes
3 20030501, 20031102, No (1 day over I think)
etc...

Hopefully this is a little clearer now

thanks

steve

Steve no function beer well without

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-19 : 08:58:02

This is long, but it returns the last 2 clinics and dates for each patient in a single row.


SELECT A.PatientID, A.LastDate, Y.ClinicID as LastClinicID, A.NextToLastDate, Z.ClinicID as NextToLastClinicID
FROM (
  SELECT A.PatientID, A.LastDate, MAX(B.ClinicDate) as NextToLastDate
  FROM ( -- This recordset has the most recent date
    SELECT patientID, MAX(clinicDate) AS LastDate
    FROM dbo.MyTable
    GROUP BY PatientID
  ) A
  INNER JOIN dbo.MyTable B -- Join with all records in the last 6 months to find 2nd most recent
    ON B.PatientID = A.PatientID 
    AND B.ClinicDate <> A.LastDate
    AND DATEDIFF(MM, B.ClinicDate, A.LastDate) <= 6
  GROUP BY A.PatientID, A.LastDate
) X
INNER JOIN dbo.MyTable Y  -- Join with all data for Last date
  ON Y.PatientID = X.PatientID
  AND Y.ClinicDate = X.LastDate
INNER JOIN dbo.MyTable Z
  ON Z.PatientID = X.PatientID
  AND Z.ClinicDate = X.NextToLastDate

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-12-22 : 09:25:23

I'll take a look at that, thanks

steve

Steve no function beer well without

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-22 : 14:43:31

Here is my shot:

CREATE TABLE clinic (pk_ClinicID INT, fk_PatientID INT, ClinicDate DATETIME )

INSERT INTO clinic
SELECT 1,1,'20031201'
UNION ALL SELECT 2, 1, '20030712'
UNION ALL SELECT 3, 2, '20021105'
UNION ALL SELECT 4, 1, '20030109'
UNION ALL SELECT 3, 2, '20031201'
GO

SELECT 	c.fk_patientid PatientID,
	MostRecentClinicDate, 
	MAX(clinicdate) '2ndMostRecentClinicDate',
	CASE WHEN DATEDIFF(mm,MAX(clinicdate),MostRecentClinicDate ) <=6 THEN 'Yes' ELSE 'No' END Within6Months
FROM clinic c
JOIN
(
	SELECT fk_patientid, MAX(clinicdate) MostRecentClinicDate
	FROM clinic a
	GROUP BY fk_patientid
) a
ON c.fk_patientid = a.fk_patientid and c.clinicdate < a.MostRecentClinicDate
GROUP BY c.fk_patientid, a.MostRecentClinicDate

DROP TABLE clinic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources