VALIDATION PROBLEMS..PLS I NEED HELP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

VALIDATION PROBLEMS..PLS I NEED HELP

Author

Topic

Tijesuni
Starting Member

4 Posts

Posted - 2013-03-16 : 14:17:10

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-16 : 21:49:54

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-16 : 21:51:22

Duplicate of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=183826

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources