CODEWITHSUNDEEP
sqlpostgresql
AscaL
AscaL

Reputation: 183

Number of free beds in a set date in a set department

my problem is this: I have this tables:

Patient (fiscalcode, name, surname)
Department (id, name)
Bed (id, department) : department references Department(id)
Recover (patient, bed, start_date, end_date*) : patient ref Patient(fc) and bed ref Bed(id)

I need to make a query returning the number of occupied beds in a given date and in a given department d.

I was thinking of something like this, but i'm not sure: 

SELECT count(bed.id)
FROM bed b
    ,department dep
    ,recover r
WHERE dep.id = d.id
    AND d.id = b.id
    AND r.bed = b.id
    AND r.start_date < given_date
    AND r.end_date < given_date

Upvotes: 1

Views: 57

Answers (1)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20499

I think this should work for you

SELECT COUNT(B.ID)
FROM BED B
    INNER JOIN DEPARTMENT D ON B.department = D.id
    INNER JOIN RECOVER R ON R.Bed = B.id
WHERE
    R.end_date < given_date
    AND R.start_date < given_date
    AND (D.name = 'department_name' 
         OR 
         D.id = --departmentID--
         )

You actually had an extra condition in your WHERE clause that required the ID in DEPARTMENT talbe to be the same as the ID in BED table.

Upvotes: 1

Related Questions