How to calculate percentage in sql 1

[Dado111]

Hi,

How can i produce a report that gives for each ward its name, code, and the percentage occupied in the wardof a hospital.
knowing that we have the table of "Ward" wich hold a wardCode, wardName, BedCount.
And another table for Admission wich has AdmissionID, patient_Id, AdmissionDate, Ward code Discharge_Date

Thanks for all your help
Dado

 

[bperry]

Here's a solution that seems to work in MS SQL. A subquery is used to calculate the %Occ. (It assumes that a bed is 'occupied' if there is an admission record with no discharge date).
The CAST function is used to convert the numerator to a numeric, otherwise the decimals would not show.
If this is not exactly what you need, perhaps you can use it as a guide?
-----------------------
select
w.WardCode,
w.WardName,

CAST(
(Select count(WardCode) from Admission a2
Where a2.WardCode = w.WardCode
and a2.Discharge_date IS NULL
)
as numeric(3,2))
/ bedCount *100 as PerOcc

from Ward w INNER JOIN Admission a1
ON w.WardCode = a1.WardCode
Group By w.WardCode, w.wardname, w.Bedcount

 

[bperry]

Here is a different approach that uses a derived table to calculate the PerOcc. It creates a different execution plan than the first query, and might be more efficient or less efficient depending on your circumstances.
Again, this also assumes that a bed is 'occupied' if the discharge_date is null

Select w.WardCode, w.WardName, w.BedCount, dt.PerOcc
from Ward w
INNER JOIN
(Select Admission.WardCode,
CAST(COUNT(admission.WardCode) as numeric(3,2)) / ward.BedCount as PerOcc
from Admission Inner Join Ward
on Admission.WardCode = Ward.WardCode
where admission.discharge_Date IS NULL
Group By Admission.WardCode, Ward.BedCount

) dt
ON w.WardCode = dt.WardCode