CODEWITHSUNDEEP
sqlpostgresql
Kuke
Kuke

Reputation: 37

postgresql Select date from min value

I want to select list of people, who have FIRST time 'E' diagnosis from 2016-01-01.

my code looks like

SELECT DISTINCT ON(form25.visit.patient_id )
        form25.visit.patient_id,
        CONCAT(demographics.party.firstname,' ', demographics.party.lastname) AS pacientas, 
        form25.visit.disease_code,
        demographics.party.code,
        form25.visit.date,
        form25.diagnose.disease_type
    FROM form25.visit
    JOIN demographics.party
        ON form25.visit.patient_id = demographics.party.id
    JOIN form25.diagnose
        ON form25.visit.patient_id = form25.diagnose.card_id
    WHERE form25.visit.disease_code LIKE 'E%'
        AND form25.diagnose.disease_type = '+'
        AND form25.visit.date >= '2016-01-01'
    ORDER BY form25.visit.patient_id, form25.visit.date ASC

result i get:

patients E.. 2013,2014,2016, 2017

(contains E.. with 2016 so I see them)

All I want is to eliminate those who have date(s) with E.. before 2016, and see only those who had it first time from 2016.

Upvotes: 0

Views: 333

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

First, your query would be much easier to read (and write) if you used table aliases.

Second, you can get what you want by doing an aggregation and then looking at the minimum date:

SELECT v.patient_id, CONCAT(p.firstname, ' ', p.lastname) AS pacientas, 
       MIN(v.date) as first_date
FROM form25.visit v JOIN
     demographics.party p
     ON v.patient_id = p.id JOIN
     form25.diagnose d
     ON v.patient_id = d.card_id
WHERE v.disease_code LIKE 'E%' AND d.disease_type = '+'
GROUP BY patient_id, pacientas
HAVING MIN(v.date) >= '2016-01-01';

Note that this gives you the patients that meet the conditions. If you need visit details, you can join back or use window functions.

Upvotes: 1

Related Questions