Member Avatar for saadi06

Hi,
I have a problem in making a query.I have two tables patient and encounter. Patient has one to many relation with the encounter table. The encounter table has a field encounter_date. I want to make a search query in which I enter the date ranges and the query fetches the encounter
between the date range and check whether the encounter is first encounter of the patient. For example a patient has two encounters on 2012-02-01 and 2012-06-07 and I search between 2012-01-29 to 2012-02-05.The search result should be displayed as the patients first encounter is on 2012-02-01 and it should not come in the date range for 2012-06-01 and 2012-06-10.I have made a query but it is having problem. Here is my query

SELECT  patient.origid          AS 'Patient Id',
          patient.firstname       AS 'First Name',
          patient.lastname        AS 'Last Name',
          patient.middlename      AS 'Middle Name',
          DATE_FORMAT(encounter.encounter_date,'%Y-%m-%d')        AS 'Encounter Date',
          FROM patient
          INNER JOIN encounter ON  encounter.origid = patient.origid 
          WHERE NOT  encounter.encounter_status='cancelled'
          AND encounter.encounter_date BETWEEN '2012-02-01' AND '2012-02-10'
          GROUP BY encounter.origid ORDER BY encounter.encounter_date ASC
          LIMIT 15

Now the problem with this query is that it does not take in account that is this first encounter of the user or not.I think I have to implement an inner query for this but cant think of it.
Can anyone help.
Thanks in advance.

Edited by saadi06
  • 2 Contributors
  • 3 Replies
  • 171 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by diafol
Member Avatar for diafol

SO you're not searching for a particular patient - you want ALL first encounters between 2 dates. That right?

If so, you may need to do a subquery instead of using ther encounter table as is.
THat may be something like:

...INNER JOIN (SELECT origid, MIN(encounter_date) FROM encounter WHERE encounter_date BETWEEN '...' AND '...' GROUP BY oridig) AS enc ON enc.origid ...
Edited by diafol
Member Avatar for saadi06

Yes exactly.

Member Avatar for diafol

As I don't have your table setups, I'm just typing out loud here. Maybe this will work?

SELECT  patient.origid          AS 'Patient Id',
      patient.firstname       AS 'First Name',
      patient.lastname        AS 'Last Name',
      patient.middlename      AS 'Middle Name',
      DATE_FORMAT(enc.ed,'%Y-%m-%d')   AS encdate,
      FROM patient
      INNER JOIN (SELECT origid, MIN(encounter_date) AS ed FROM encounter WHERE NOT  encounter_status='cancelled' AND encounter_date BETWEEN '2012-02-01' AND '2012-02-10' GROUP BY oridig) AS enc ON  enc.origid = patient.origid 
     ORDER BY enc.encounter_date ASC
      LIMIT 15
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.