successive events - 2 problems

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

SQL Server Development (2000)

successive events - 2 problems

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-05-28 : 10:10:36

I have a table called CLINIC for which each row contains one of three things

a) details of a patients referral
b) details of a patients position on a waiting list
c) details of the appointments that were made for a patient

In this table there are the following fields (and more)

ClinicNum - Primary Key for the clinic table
PatientNum - Secondary key from the PATIENT table
ClinicDate - Date of a appointment (not used for options 1 or 2 above)
Attended - Indicates if the patient attended the clinic or not (not used for options 1 or 2 above)

Although the logical sequence is Referral -> Waiting list -> Clinic. This is not always the case and cannot be guaranteed

My problems are these:-

1) Given the above how do I find out if a patient DID NOT ATTEND for two successive clinic's.?

2) How do I identify the patients first attendance? I tried using min(clinicdate) and grouping on PatientNum but couldn't get the whole row without using a temporary table. Is it possible to do this in a view rather than a stored procedure?

thanks in advance

steve

JCamburn
Starting Member

31 Posts

Posted - 2003-05-30 : 19:09:13

quote:
2) How do I identify the patients first attendance? I tried using min(clinicdate) and grouping on PatientNum but couldn't get the whole row without using a temporary table. Is it possible to do this in a view rather than a stored procedure?

Are the combination of PatientNum and ClinicDate unique for each row in the table? If so, you could use the following:


SELECT
    c.*
FROM
    CLINIC c
    INNER JOIN
    (
        SELECT
            PatientNum,
            MIN(ClinicDate) AS FirstAttendanceDate
        FROM
            CLINIC
        GROUP BY
            PatientNum
    ) AS q
    ON c.PatientNum = q.PatientNum 
    AND c.ClinicDate = q.FirstAttendanceDate

quote:
1) Given the above how do I find out if a patient DID NOT ATTEND for two successive clinic's.?

I will have to get back to you on this one, I've got somewhere to go.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-30 : 21:07:11

See Jsmith8858's article on detecting streaks.
http://www.sqlteam.com/item.asp?ItemID=12654

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-06-02 : 08:18:59

The combination of patientnum and clinicdate is unique so I'll try that thanks. Any help or insight with the second problem would be greatly appreciated

I did take a look at the stuff on streaks but had great trouble getting it to do what I thought it was intended to on my data

steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources