Professional Web Applications Themes

Consecutive events - Microsoft SQL / MS SQL Server

I run a system that stores information on patients and when they are seen by a doctor in clinic. So for a given clinic I may have pk_ClinicID fk_PatientID ClinicDate PatientAttended I need to write a query to determine if a patient didn't attend for two consecutive clinic's. At the moment I have something that tells me that a patient has not attended two (or more clinic's) but doesn't tell me that these are consecutive i.e. they could have attended clinic's in between. Does anyone have any clues or pointers please thanks in advance 'M'...

  1. #1

    Default Consecutive events

    I run a system that stores information on patients and when they are
    seen by a doctor in clinic. So for a given clinic I may have

    pk_ClinicID fk_PatientID ClinicDate PatientAttended

    I need to write a query to determine if a patient didn't attend for
    two consecutive clinic's. At the moment I have something that tells me
    that a patient has not attended two (or more clinic's) but doesn't
    tell me that these are consecutive i.e. they could have attended
    clinic's in between.

    Does anyone have any clues or pointers please

    thanks in advance

    'M'
    Codename Guest

  2. #2

    Default Re: Consecutive events

    usually you do a join like
    select * from clinics join patients on clinics.attendee_id =
    patients.patient_id

    fool around with
    select * from clinics join patients on clinics.attendee_id =
    patients.patient_id+1 or clinics.attendee_id = patients.patient_id-1





    "Codename 'M'" <com> wrote in message
    news:google.com... 


    Tammy Guest

  3. #3

    Default Re: Consecutive events

    SELECT m1.fk_PatientID FROM myTable m1
    WHERE m1PatientAttended = 'Did Not Attend'
    AND (SELECT TOP 1 m2.PatientAttended FROM myTable m2
    WHERE m1.fk_PatientID = m2.fk_PatientID
    AND m2.ClinicDate > m1.ClinicDate
    ORDER BY m2.ClinicDate ASC) = 'Did Not Attend'


    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Codename 'M'" <com> wrote in message
    news:google.com... 


    Jacco Guest

  4. #4

    Default Re: Consecutive events

    Can you please post DDL (CREATE TABLE), sample data (INSERTs) and desired results?

    --
    BG, SQL Server MVP
    Solid Quality Learning
    www.solidqualitylearning.com


    "Codename 'M'" <com> wrote in message news:google.com... 
    Itzik Guest

Similar Threads

  1. Replies: 2
    Last Post: February 17th, 01:17 AM
  2. CF needs to honor consecutive delimeters
    By deane_barker in forum Macromedia ColdFusion
    Replies: 4
    Last Post: March 30th, 02:12 PM
  3. InDEsign Newbie - Consecutive numbering of figures in a doent. How?
    By Adam_Hodge@adobeforums.com in forum Adobe Indesign Windows
    Replies: 3
    Last Post: August 16th, 04:52 PM
  4. Replies: 2
    Last Post: June 28th, 12:20 PM
  5. how to construct 2 consecutive if conditions?
    By Danield in forum PERL Beginners
    Replies: 1
    Last Post: January 25th, 05:43 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139