CODEWITHSUNDEEP
sqlpostgresql
piofusco
piofusco

Reputation: 239

Postgresql - retrieve rows within criteria within 30 day span

I have the following tables

AdmittedPatients(pid, workerid, admitted, discharged)
Patients(pid, firstname, lastname, admitted, discharged)
DiagnosticHistory(diagnosisID, workerid, pid, timeofdiagnosis)
Diagnosis(diagnosisID, description)

Here is an SQL Fiddle: http://sqlfiddle.com/#!15/e7403

Things to note:

Here is my task: list patients who were admitted to the hospital within 30 days of their last discharge date. For each patient list their patient identification number, name, diagnosis, and admitting doctor.

This is what I've cooked up so far:

select pid, firstname, lastname, admittedpatients.workerid, patients.admitted, admittedpatients.discharged
from patients 
join admittedpatients using (pid)
group by pid, firstname, lastname, patients.admitted, admittedpatients.workerid, admittedpatients.discharged
having patients.admitted <= admittedpatients.discharged;

This returns pid's from 0, 1, and 4 when it should 0, 1, 2, and 4.

Upvotes: 0

Views: 190

Answers (3)

xQbert
xQbert

Reputation: 35333

Not sure why out need group by or having here... no aggregate...

SELECT A.pid, firstname, lastname, A.workerid, P.admitted, A.discharged
FROM  patients P
INNER JOIN  admittedpatients A 
  on P.pID = A.pID
WHERE date_add(a.discharged, interval 30 day)>=p.admitted
and p.admitted >=a.discharged

updated fiddle: http://sqlfiddle.com/#!2/dc33c/30/0

Didn't get into returning all your needed fields but as this gets the desired result set I imagine it's just a series of joins from here...

Updated to postgresql:

SELECT A.pid, firstname, lastname, A.workerid, P.admitted, A.discharged
FROM  patients P
INNER JOIN  admittedpatients A 
  on P.pID = A.pID
WHERE a.discharged+ interval '30 day' >=p.admitted
and p.admitted >=a.discharged

http://sqlfiddle.com/#!15/e7403/1/0

Upvotes: 1

Andreas
Andreas

Reputation: 5103

I didn't see any diagnostic info in the fiddle, so I didn't return any.

select pid
,p.lastname,p.firstname 
,ad.lastname,ad.firstname 
from AdmittedPatients as a
join AdmittedPatients as d using (pid)
join Patients as p using (pid)
join AdminDoctors as ad on ad.workerid=a.workerid
where d.discharged between a.admitted-30 and a.admitted

Upvotes: 1

Scott
Scott

Reputation: 3732

You have a rather basic WHERE clause error here:

Admitted cannot be both before discharged AND after discharged+30

Also you have an extra semicolon before your whole query is ended, probably throwing out the last line altogether.

I think you're looking for admitted=discharged

Upvotes: 0

Related Questions