Something like this to get you started. But, this would not work correctly if a patient was admitted multiple times and the patient id remained the same. Can the Ward_Charge change? From what I know of hospitals, they are looking for a reason to raise their prices, so I can't imagine that there won't be frequent changes in Ward_Charges.
DATEDIFF(dd,Admit_Date,Discharge_Date)*w.Ward_Charge - SUM(AdvancePayment)
INNER JOIN WardDetails w ON w.WardId = pd.Ward_ID
INNER JOIN Payments p ON p.PatientID = pd.PatientID
w.Ward_ChargeIf you can post some sample data in a consumable format, that would make it easier to provide more accurate solutions. See here if you need help in posting: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx