Query Problem

Question

Post reply

Query Problem

465789psw

Hall of Fame

Points: 3314
More actions
March 22, 2004 at 11:56 am

#101927

select distinct AccountNumber,
                originalBilledAmount,
               tbl_PerformedProcedure.PatientPrimaryKey,
                PhysicianID,
                cptcode
from tbl_BillingMaster INNER JOIN tbl_PerformedProcedure
on tbl_BillingMaster.PatientPrimaryKey=tbl_PerformedProcedure.PatientPrimaryKey and billingcoveredfromdate BETWEEN '2003-01-01' and '2003-12-31'
compute sum(OriginalBilledAmount)
I have a db that does not make a lot of since and looks like it was poorly written so any help would be appreciated. When I write the above statment with out the last two col.in the select list I get 3252 rows and when I run the statement above I get 4723 rows.
THANKS

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Consider you are doing distinct on you output so by themselves

AccountNumber,

originalBilledAmount,

tbl_PerformedProcedure.PatientPrimaryKey

You get 3252 but when you add in

PhysicianID,

cptcode

You will of course have more potentials for differences should accoutn for that.

If you remove distinct they should be the same either way.

If there is a reason for distinct please explain what you are wanting.

465789psw Hall of Fame Points: 3314 More actions · Answer 2

Thanks, to be more specific I am trying to get the total sum billed, 1 statment time I get 9.4 mil. when I add two add two col. I get 12.5 millon.

465789psw Hall of Fame Points: 3314 More actions · Answer 3

I started with this and rec. dubs., so I added the distinct and the join in order to get rid of them maybe I am going about it a little wrong?

select AccountNumber,originalBilledAmount

from tbl_BillingMaster INNER JOIN tbl_PerformedProcedure

on tbl_BillingMaster.PatientPrimaryKey=tbl_PerformedProcedure.PatientPrimaryKey and billingcoveredfromdate BETWEEN '2003-01-01' and '2003-12-31'

compute sum(OriginalBilledAmount)

AccountNumber OriginalBilledAmount

19170 1600.0

34701 4006.0

Peter Evans Ten Centuries Points: 1187 More actions · Answer 4

Looks like you getting some form of record mutliplication because of flag field that needs to be in the join condition? But it might also be because one of the tables is double entry as in start record stop record or billed record paid record. Its hard to tell with out a little better explanation of the relation between the tables and better description of what data is in the tables.

Peter Evans (__PETER Peter_)

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 5

Yes, as Peter rightly mentioned, the relationships between the tables and the data storage in the tables would help analyze the issue.

Prasad Bhogadi
www.inforaise.com

kkeeffe SSC Eights! Points: 989 More actions · Answer 6

Try dividing your billed amount by the number of procedure records...

Select t1.AccountNumber

, t1.originalBilledAmount / t3.ProcRecords As AdjBilledAmt

, tbl_PerformedProcedure.PatientPrimaryKey

, PhysicianID

, t2.cptcode

From tbl_BillingMaster t1

Inner Join tbl_PerformedProcedure t2

On t1.PatientPrimaryKey = t2.PatientPrimaryKey

Inner Join

(

Select AccountNumber

, Cast(Count(*) As decimal(9,2)) As ProcRecords

From tbl_BillingMaster t1

Inner Join tbl_PerformedProcedure t2

On t1.PatientPrimaryKey = t2.PatientPrimaryKey

Where billingcoveredfromdate Between '2003-01-01' And '2003-12-31'

&nbsp As t3

On t1.AccountNumber = t3.AccountNumber

... then do your sum on the calculated field AdjBilledAmt

k2

465789psw Hall of Fame Points: 3314 More actions · Answer 7

SOUNDS LIKE YOU GUYS ARE HEADED IN THE RIGHT DIRECTION. Looks like this is where the problem lies. heres a sample. of one patient.

SELECT Facility,PatientPrimaryKey,ServiceCode,DateOfService,description

FROM tbl_PerformedProcedure where PatientPrimaryKey = 4

Facility PatientPrimaryKey ServiceCode DateOfService Description

-------- ----------------- ----------- ------------------------------------------------------ ----------------------------------------------------------------------------------------------------

99 4 wrstcarp 2001-07-17 00:00:00 carpal tunnel release

99 4 Catphaco 2001-07-17 00:00:00 Cataract Extraction with phaco and insertion of IOL

99 4 Catphaco 2001-07-24 00:00:00 Cataract Extraction with phaco and insertion of IOL

99 4 breastbx 2001-07-19 00:00:00 breast biopsy

99 4 breastbx 2001-07-26 00:00:00 breast biopsy

99 4 breastbx 2001-07-31 00:00:00 breast biopsy