how to use max value to update
-
I have one table which the payment from multiple insurance company and another table contain the patient information. I want to update the patient information with the insurance company information who paid the most bill.
InpatientMedStat :
PID Payor
111
222
333
table two
PID Payor PayAmount
111 MIC 10.00
111 ACB 110.00
111 EDE 1110.00
223 MIC 10.00
121 ACB 110.00
222 Blue 454.00
222 Red 545.00
144 EDE 14500.00
I need to update the InpatientMedStat table PID 111 with payor as EDE . PID 222 Payor will fill with Red .
EDE and Red they paid the largest amount in the table two payamount for corresponing pid. Thanks.
I do not want to set up the recordset as I did in the vb application and update the record. Thanks.
-
Untested, and it's Friday, so probably not the ideal solution, but it should work:
UPDATE
i
SET
i.Payor = p.Payor
FROM
InpatientMedStat AS i
INNER JOIN (
SELECT
PID
,Payor
,MaxAmt
FROM
[Table two] AS t1
INNER JOIN (
SELECT
PID
,MaxAmt = Max(PayAmount)
FROM
[Table two]
GROUP BY
PID
) AS t2 ON
t1.PID = t2.PID AND
t1.PayAmount = t2.MaxAmount
) AS p ON
i.PID = p.PID
A few things to consider. It's actually much easier to accomplish this with a correlated subquery, but if this table is decent size, that can have major performance issues, so I gave the longer, but more efficient version. Secondly, this code doesn't handle ties in a specific manner (the last one it runs across gets into the update), so if you have a business rule for that (such as lowest alphabetically, first of the two payments made, etc), you'll need to add that to the code. By tie, I mean that two Payors both paid the same maximum amount for the same PID.
-
Something like this should work...
UPDATEims
SETims.Payr = t2.Payor
FROMInpatientMedStat ims
JOIN(SELECT t2_1.PID, t2_1.Payor, t2_1.PayAmount
FROM table_two t2_1
JOIN (SELECT PID, MAX(PayAmount) AS max_PayAmount
FROM table_two
) t2_max
ONt2_1.PID = t2_max.PID
ANDt2_1.PayAmount = t2_max.PID
) t2
Onims.PID = t2.PID
-
I f there are multiple payments made by a single insurance company to the same patient, you might need to sum up the PayAmount first.
SELECT PID, Payor, SUM(PayAmount) into #Table2
from table_two
group by PID, Payor
UPDATE ims
SET ims.Payor = t2.Payor
FROM InpatientMedStat ims
JOIN (SELECT Distinct t2_1.PID, t2_1.Payor
FROM table_two t2_1
JOIN (SELECT PID, MAX(PayAmount) AS max_PayAmount
FROM #Table2
GROUP BY PID
) t2_max
ON t2_1.PID = t2_max.PID
AND t2_1.PayAmount = t2_max.PID
) t2
On ims.PID = t2.PID
-
UPDATE InpatientMedStat
SET InpatientMedStat.InsurancePlanCode = t2.Payor
FROM InpatientMedStat ims
JOIN (SELECT Distinct rptPXA3.PNO, rptPXA3.Payor, rptPXA3.Payment
FROM rptPXA3 t2_1
JOIN (SELECT PNO, MAX(Payment ) AS MaxPayAmount
FROM rptPXA3
GROUP BY PNO
) t2_max
ON t2_1.PNO = t2_max.PNO
AND t2_1.Payment = t2_max.MaxPayAmount
) t2
On ims.PatientAccountNumber = t2.PNO
According to the posting suggestion , I wrote my query as above. It gave me the error as
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'rptPXA3' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'rptPXA3' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'rptPXA3' does not match with a table name or alias name used in the query.
I do not know why I have alias problem. Thx.
-
You did not build "According to the posting suggestion".
You duilt differently.
Try again and now pay attention to details.
_____________
Code for TallyGenerator -
I know these two (Aaron Ingold 
and Ronald San Juan are wrong on payamount could not equal to pid.
UPDATE ims
SET ims.Payor = t2.Payor
FROM InpatientMedStat ims
JOIN (SELECT Distinct t2_1.PID, t2_1.Payor
FROM table_two t2_1
JOIN (SELECT PID, MAX(PayAmount) AS max_PayAmount
FROM #Table2
GROUP BY PID
) t2_max
ON t2_1.PID = t2_max.PID
AND t2_1.PayAmount = t2_max.PID
) t2
On ims.PID = t2.PID
right now I tried to understand David McFarland 's posting. I do not know what you implied here ? Thx.
-
I see you are getting more and more helpfull to those in need. Thanx for sharing all the usefull information.
-
The forum will benefit much more from 2 prolific posters with 2 very different work experience than just one zelus poster.
Thanx for the help!
-
It's a simple "copy-paste" mistake.
You should take care of such things by yourself. It's too obvious.
ON t2_1.PID = t2_max.PID
AND t2_1.PayAmount = t2_max.max_PayAmount
_____________
Code for TallyGenerator -
Thank you poiniting out. I looked at the code. Right now I understand why.
Here is my version according to the other posters.
UPDATE InpatientMedStat
SET InpatientMedStat.InsurancePlanCode = t2.Payor
FROM InpatientMedStat ims
JOIN (SELECT t2_1.PNO, t2_1.Payor, t2_1.Payment
FROM rptPXA3 t2_1
JOIN (SELECT PNO, MAX(Payment ) AS MaxPayAmount
FROM rptPXA3
GROUP BY PNO
) t2_max
ON t2_1.PNO = t2_max.PNO
AND t2_1.Payment = t2_max.MaxPayAmount
) t2
On ims.PatientAccountNumber = t2.PNO
I messed up the Alias part.
Happy Holiday !!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply