i need create few queries database following schema:
patient(**pid**,pname,address,phone) ward(**wid**, wname) // wid=ward id, bed(**wid,bid**) // bid=bed id appointment(**apid**,date,result,pid,cid) // cid=consultant id, pid=patient id consultant(**cid**,cname,clinid,phone) // clinid=clinic id allocation(**apid**,pid,wid,bid,date,ex_leave,act_leave) //ex=expected, act=actual the queries are:
- find how many unoccupied beds in each ward.
- find ward of allocation made on every day during march 2013.
- return consultant details of performed appointments led allocation in orthopedic ward.
i tried create first 1 using views this:
create view hospital.occupied_beds select a.wid,count(*) o_beds hospital.allocation a,hospital.bed b a.wid=b.wid , a.bid=b.bid , a.act_leave null group a.wid; create view hospital.all_beds select c.wid,count(*) all_beds hospital.bed c group c.wid; select distinct a.wid,all_beds-o_beds uo_beds hospital.occupied_beds a, hospital.all_beds b but way doesn't return wards in beds unoccupied.
please me :)
here 3 possible solutions questions. keep in mind not going efficiency. there ways optimize these queries bit. wanted give ideas , going in right direction.
for unoccipied beds per ward:
select w.wname, bc.total - ifnull(ob.occupied,0) unoccupied ward w, (select wid, count(bid) total bed group wid) bc left join (select wid, count(wid) occupied allocation act_leave null group wid) ob on bc.wid = ob.wid w.wid = bc.wid for wards allocations every day in march 2013
select w.wid, w.wname, count(distinct(a.date)) acount ward w, allocation a.date >= '2013-03-01' , a.date <= '2013-03-31' , w.wid = a.wid group w.wid having acount = 31 the list of consultants ortho appointments in descending order (most allocations on top)
select c.cid, c.cname, count(a.apid) apptcount consultant c, appointment p, allocation a, ward w c.cid = p.cid , p.apid = a.apid , a.wid = w.wid , w.wname = 'orthopedic' group c.cid order apptcount desc 
Comments
Post a Comment