SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 VALIDATION PROBLEMS..PLS I NEED HELP
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Tijesuni
Starting Member

Nigeria
4 Posts

Posted - 03/16/2013 :  14:17:10  Show Profile  Reply with Quote
Validation to be performed
1)Final Payment should store final amount chargeable and must be calculated as
Total Bill - AdvancePayment
2)Total bill will be calculated at time of discharge by adding the ward charges for the no of days the patient was admitted

TABLES

PAYMENTS
PaymentID
PatientID
AdvancePayment
FinalPayment
PaymentStatus

WARDDETAILS
WardID
Ward_Name
Ward_Charge

PATIENTDETAILS
PatientD
Bld_Grp
DoctorID
Admit_Date
Discharge_Date
Ward_ID

I'll be glad whoever can help..THANKS

James K
Flowing Fount of Yak Knowledge

3695 Posts

Posted - 03/16/2013 :  21:49:54  Show Profile  Reply with Quote
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.
SELECT
	pd.PatientID,
	DATEDIFF(dd,Admit_Date,Discharge_Date)*w.Ward_Charge -  SUM(AdvancePayment)
		AS FinalPayment
FROM
	PatientDetails pd
	INNER JOIN WardDetails w ON w.WardId = pd.Ward_ID
	INNER JOIN Payments p ON p.PatientID = pd.PatientID
GROUP BY
	pd.PatientID,
	pd.Admit_Date,
	pd.Discharge_Date,
	w.Ward_Charge
If 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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3695 Posts

Posted - 03/16/2013 :  21:51:22  Show Profile  Reply with Quote
Duplicate of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=183826
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000