I'm sure there is a better way to do this

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 2000 Forums

Transact-SQL (2000)

I'm sure there is a better way to do this

Author

Topic

melissar
Starting Member

15 Posts

Posted - 2003-06-04 : 12:46:31

This is my query
SELECT DISTINCT pat_id,pat_title,
pat_lname, pat_fname, pat_mi, pat_ssn,
pat_dob, T_Visits.vis_pat_src_id,
(SELECT SUM(T_Balances.bal_curr_amt)
FROM T_Visits JOIN T_Patients p2 ON vis_pat_id = pat_id
JOIN T_Procedures ON vis_id = proced_vis_id
JOIN T_Balances ON proced_id = bal_proced_id
WHERE bal_dt = (SELECT MAX(bal_dt)
FROM T_Balances
WHERE proced_id = bal_proced_id and vis_id = proced_vis_id)
AND p1.pat_id = p2.pat_id)AS balance

FROM T_Patients p1 JOIN T_Visits ON pat_id=vis_pat_id
WHERE (SELECT SUM(T_Balances.bal_curr_amt)
FROM T_Visits JOIN T_Patients p2 ON vis_pat_id = pat_id
JOIN T_Procedures ON vis_id = proced_vis_id
JOIN T_Balances ON proced_id = bal_proced_id
WHERE bal_dt = (SELECT MAX(bal_dt)
FROM T_Balances
WHERE proced_id = bal_proced_id and vis_id = proced_vis_id)
AND p1.pat_id = p2.pat_id) <> 0
ORDER BY balance DESC

Isn't there a better way to exclude any records that have 0 balances then my where clause?

Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-04 : 13:21:36

Wow, that's a duzzy...I think I hurt mtself formatting the code...but I'll try and see what your doing...if you could give the business reason and the expected end result it might be helpful

EDIT:

quote:

I'm sure there is a better way to do this

I'm sure there is too


SELECT DISTINCT   pat_id
		, pat_title 
		, pat_lname
		, pat_fname
		, pat_mi
		, pat_ssn
		, pat_dob
		, T_Visits.vis_pat_src_id
		, ( SELECT SUM(T_Balances.bal_curr_amt) 
		      FROM T_Visits 
		      JOIN T_Patients p2 
		        ON vis_pat_id = pat_id 
		      JOIN T_Procedures 
			ON vis_id = proced_vis_id 
		      JOIN T_Balances 
			ON proced_id = bal_proced_id 
		     WHERE bal_dt = (  SELECT MAX(bal_dt) 
					 FROM T_Balances 
					WHERE proced_id = bal_proced_id 
					  AND vis_id = proced_vis_id) 
		       AND p1.pat_id = p2.pat_id
		 )AS balance 
	   FROM T_Patients p1 
	   JOIN T_Visits 
	     ON pat_id=vis_pat_id 
	  WHERE ( SELECT SUM(T_Balances.bal_curr_amt) 
		    FROM T_Visits JOIN T_Patients p2 
		      ON vis_pat_id = pat_id 
		    JOIN T_Procedures 
		      ON vis_id = proced_vis_id 
		    JOIN T_Balances 
		      ON proced_id = bal_proced_id 
		   WHERE bal_dt = (SELECT MAX(bal_dt) 
				     FROM T_Balances 
				    WHERE proced_id = bal_proced_id 
				      AND vis_id = proced_vis_id) 
		     AND p1.pat_id = p2.pat_id) <> 0 
       ORDER BY balance DESC

Brett

8-)

Edited by - x002548 on 06/04/2003 13:22:37

melissar
Starting Member

15 Posts

Posted - 2003-06-04 : 13:26:35

The query selects the patient outstanding balance information. The balances are based on visits, which have one to many procedures, which have one to many balances. The max date is to select the current balance (I didn't design the DB, just trying to do some development

). What I need to do is exclude the patients with 0 balances from the query. That is what the ugly where clause does.

Hope that makes more sense.

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-04 : 13:34:16

Again, I'm not saying this is Optimized, but maybe this should work (INSTO...a New Internet Acronym?)


SELECT DISTINCT   pat_id
		, pat_title 
		, pat_lname
		, pat_fname
		, pat_mi
		, pat_ssn
		, pat_dob
		, T_Visits.vis_pat_src_id
		, xxx.Balance
	   FROM T_Patients p1 
	   JOIN T_Visits 
	     ON pat_id=vis_pat_id 
	   JOIN ( SELECT SUM(T_Balances.bal_curr_amt) As Balances, p2.pat_id
		      FROM T_Visits 
		      JOIN T_Patients p2 
		        ON vis_pat_id = pat_id 
		      JOIN T_Procedures 
			ON vis_id = proced_vis_id 
		      JOIN T_Balances 
			ON proced_id = bal_proced_id 
		     WHERE bal_dt = (  SELECT MAX(bal_dt) 
					 FROM T_Balances 
					WHERE proced_id = bal_proced_id 
					  AND vis_id = proced_vis_id) 
		       
		 )AS xxx
	     ON p1.pat_id = xxx.pat_id
	  WHERE xxx.Balance <> 0
       ORDER BY balance DESC