2 complicated queries

[Nir N]

I sm sorry for the complex question, but I don't have much experience in SQL.

I have to query an external database (meaning I cannot change it's design to
a more user friendly one).

The table consistst of patients visits to a clinic.
For each patient there are 3 fields: An ID, visit date and the examination
code the patient made in that date, so for every visit there are multiple
(20-30) records per patient, all with the same ID and date, some of them even
with the same exam ID (if the patient had multple X-Rays at the same day, for
example).
Usually patients are followed-up every year.

To make things more ocmplex, if the patient visited the clinic a few times
in a 2-months period, it is still considered one visit (some patients are
recalled to repaet an examination if the lab has technical problems or to
verify an abnormal lab test).
However, their exam dates wil be the actual dates that they visited (so an
exam dated March 15th 2008 and another exam dated May 1st 2008 are considered
as the same visit).

I need to write 2 queries:
1. A query that lists all patients (within a specific date range) that
visited the clinic only once in their lifetime (the query result should be
only one record per patient, regardless of how many exams he had at that
visit).
2. A complimentary query that lists all patients who visited the clinic more
than once (i.e. arrived for follow-up). This query shoudl result in one
record per visit.


Is is possible to code those 2 queries into an SQL or Access query?

Thanks,

Nir

 

[Allen Browne]

So, any treatments for a patient that occur on the same date should always
be considered just one visit, and you want to get:
a) Patients who have only ever visited once;
b) Patients who have visited more than once.

Create this query, and save it as Query1:
SELECT DISTINCT PatientID, VisitDate
FROM Table1;

You can now use this to get (a) and (b):
SELECT PatientID, Count(VisitDate) AS HowManyVisits
FROM Query1
GROUP BY PatientID
HAVING Count(VistiDate) = 1;

and use >1 for the other one.

 

[Bob Barrows [MVP]]

I sm sorry for the complex question, but I don't have much experience
in SQL.

I have to query an external database (meaning I cannot change it's
design to a more user friendly one).

The table consistst of patients visits to a clinic.
For each patient there are 3 fields: An ID, visit date and the
examination code the patient made in that date, so for every visit
there are multiple (20-30) records per patient, all with the same ID
and date, some of them even with the same exam ID (if the patient had
multple X-Rays at the same day, for example).
Usually patients are followed-up every year.

This is very unclear. Does the ID refer to the patient? or the visit? or
the exam (you said "exam ID")?
I think I will go on the assumption that ID refers to the patient.

To make things more ocmplex, if the patient visited the clinic a few
times in a 2-months period, it is still considered one visit (some
patients are recalled to repaet an examination if the lab has
technical problems or to verify an abnormal lab test).
However, their exam dates wil be the actual dates that they visited

I don't see a field for "exam date". Did you mean to say "visit date"?

(so an exam dated March 15th 2008 and another exam dated May 1st 2008
are considered as the same visit).

Huh? So we are supposed to somehow arbitrarily decide that exams that
occurred a certain time period apart are part of the same visit?? What
is the maximum duration of a visit - in days, please? Let's assume 60
days ...

So let's see if I have this straight: if a patient makes a monthly visit
for two years, these 24 visits are supposed to be considered a single
visit?

I need to write 2 queries:
1. A query that lists all patients (within a specific date range) that
visited the clinic only once in their lifetime (the query result
should be only one record per patient, regardless of how many exams
he had at that visit).
2. A complimentary query that lists all patients who visited the
clinic more than once (i.e. arrived for follow-up). This query shoudl
result in one record per visit.


Is is possible to code those 2 queries into an SQL or Access query?

I really don't think so. Not in a single query in any case. In Access a
work table will be needed. SQL2005 has a dandy Row_Number() function
that could be used, but you did not specify what version of SQL Server
you are talking about, so let's assume Access.
What you are going to need to do is create a table with an autonumber
VisitID field:

Table: wrkVisits
RecordID autonumber
PatientID
VisitDate date/Time


Then, you will need to run a query to insert unique patients and visit
dates into this table:
INSERT INTO wrkVisits (PatientID,VisitDate)
SELECT DISTINCT ID, VisitDate FROM RemoteTableName
order by ID, VisitDate

Hmm ...
I've had a play with this, and it is very complicated.. This query gets
the days since the prior visit:

SELECT w.RecordID, w.PatientID, w.VisitDate, w2.VisitDate,
nz(datediff("d",w2.VisitDate,nz(w.VisitDate,w2.VisitDate)),0)
AS DaysSinceLastVisit
FROM wrkVisits AS w LEFT JOIN wrkVisits AS w2
ON (w.PatientID = w2.PatientID)
AND (w.RecordID = w2.RecordID+1);

Create a saved query called qryDaysSincePriorVisit using that sql.

Then create a new saved query called qryVisitsPerPatient with this
following sql to get the visits per patient:

SELECT q.PatientID, q.w.VisitDate
FROM qryDaysSincePriorVisit AS q
WHERE (((CLng([DaysSinceLastVisit]))>60
Or (CLng([DaysSinceLastVisit]))=0));

Then, this query gets the patients with a single visit:
SELECT q.PatientID, Min(q.VisitDate) AS MinOfVisitDate
FROM qryVisitsPerPatient AS q
GROUP BY q.PatientID
HAVING (((Count(*))=1));

and this one gets those with multiple visits:
SELECT q1.PatientId,VisitDate
from qryVisitsPerPatient q1 inner join (
SELECT q.PatientID,Count(*) As CountOfVisits
FROM qryVisitsPerPatient AS q
GROUP BY q.PatientID) as q2
on q1.patientid=q2.patientid
where countOfVisits > 1


I think some human intervention will still be required, but this should
get you going.

 

[Nir N]

Thanks for your reply.
How can I bypass the "2 months problem"? (the fact that 2 months of visits
are counted as one visit).

Nir

 

[Nir N]

Thanks for the informative reply.

As for your question- the 60-days limit is a rare occasion, and no one in
this clinic has a monthly schedule: the min time between each visit is 6
months (i.e. at leat 4 months pass from a previous "2 months" visit to the
next visit). Bot most viists are 1 year apart.

Nir

I sm sorry for the complex question, but I don't have much experience
in SQL.

I have to query an external database (meaning I cannot change it's
design to a more user friendly one).

The table consistst of patients visits to a clinic.
For each patient there are 3 fields: An ID, visit date and the
examination code the patient made in that date, so for every visit
there are multiple (20-30) records per patient, all with the same ID
and date, some of them even with the same exam ID (if the patient had
multple X-Rays at the same day, for example).
Usually patients are followed-up every year.

This is very unclear. Does the ID refer to the patient? or the visit? or
the exam (you said "exam ID")?
I think I will go on the assumption that ID refers to the patient.

To make things more ocmplex, if the patient visited the clinic a few
times in a 2-months period, it is still considered one visit (some
patients are recalled to repaet an examination if the lab has
technical problems or to verify an abnormal lab test).
However, their exam dates wil be the actual dates that they visited

I don't see a field for "exam date". Did you mean to say "visit date"?

(so an exam dated March 15th 2008 and another exam dated May 1st 2008
are considered as the same visit).

Huh? So we are supposed to somehow arbitrarily decide that exams that
occurred a certain time period apart are part of the same visit?? What
is the maximum duration of a visit - in days, please? Let's assume 60
days ...

So let's see if I have this straight: if a patient makes a monthly visit
for two years, these 24 visits are supposed to be considered a single
visit?

I need to write 2 queries:
1. A query that lists all patients (within a specific date range) that
visited the clinic only once in their lifetime (the query result
should be only one record per patient, regardless of how many exams
he had at that visit).
2. A complimentary query that lists all patients who visited the
clinic more than once (i.e. arrived for follow-up). This query shoudl
result in one record per visit.


Is is possible to code those 2 queries into an SQL or Access query?

I really don't think so. Not in a single query in any case. In Access a
work table will be needed. SQL2005 has a dandy Row_Number() function
that could be used, but you did not specify what version of SQL Server
you are talking about, so let's assume Access.
What you are going to need to do is create a table with an autonumber
VisitID field:

Table: wrkVisits
RecordID autonumber
PatientID
VisitDate date/Time


Then, you will need to run a query to insert unique patients and visit
dates into this table:
INSERT INTO wrkVisits (PatientID,VisitDate)
SELECT DISTINCT ID, VisitDate FROM RemoteTableName
order by ID, VisitDate

Hmm ...
I've had a play with this, and it is very complicated.. This query gets
the days since the prior visit:

SELECT w.RecordID, w.PatientID, w.VisitDate, w2.VisitDate,
nz(datediff("d",w2.VisitDate,nz(w.VisitDate,w2.VisitDate)),0)
AS DaysSinceLastVisit
FROM wrkVisits AS w LEFT JOIN wrkVisits AS w2
ON (w.PatientID = w2.PatientID)
AND (w.RecordID = w2.RecordID+1);

Create a saved query called qryDaysSincePriorVisit using that sql.

Then create a new saved query called qryVisitsPerPatient with this
following sql to get the visits per patient:

SELECT q.PatientID, q.w.VisitDate
FROM qryDaysSincePriorVisit AS q
WHERE (((CLng([DaysSinceLastVisit]))>60
Or (CLng([DaysSinceLastVisit]))=0));

Then, this query gets the patients with a single visit:
SELECT q.PatientID, Min(q.VisitDate) AS MinOfVisitDate
FROM qryVisitsPerPatient AS q
GROUP BY q.PatientID
HAVING (((Count(*))=1));

and this one gets those with multiple visits:
SELECT q1.PatientId,VisitDate
from qryVisitsPerPatient q1 inner join (
SELECT q.PatientID,Count(*) As CountOfVisits
FROM qryVisitsPerPatient AS q
GROUP BY q.PatientID) as q2
on q1.patientid=q2.patientid
where countOfVisits > 1


I think some human intervention will still be required, but this should
get you going.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Allen Browne]

You may need a subquery to see if there is another record in the table more
than 60 days away for the same patient.

This kind of thing:

SELECT PatientID
FROM Table1
WHERE EXISTS
(SELECT PatientID
FROM Table1 AS Dupe
WHERE Dupe.PatientID = Table1.PatientID
AND ((Dupe.VisitDate > Table1.VisitDate + 60)
OR (Dupe.VisitDate < Table1.VisitDate - 60)));

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html