Min/Max Dollar amount - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Min/Max Dollar amount

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2011-04-01 : 13:25:52

Hi I am try to figure out a way to find the the revcode that has the min charged amount and the max charged amount.

This is what I have, but the claim number and be different for the max and the min.

This is what I want it to look like.

Revcode claim# Min Charge claim# Max Charge
636 12548 .78 45874 802.00

How can I do this?

This is what I have, but I need to add the claim# field in.

Select Distinct
---claimnumber1,
RevCode,
min(BilledCharges) as [Min Billed],
Max(BilledCharges) as [Max Billed]
From VW_Hospital_Billed_Charges_OutPatient_InPatient_CPT t1
where t1.clms_from >= '01/01/2010' and t1.clms_thru <= '12/31/2010' and t1.revcode = '636' and
t1.PROVIDERTAXIDNUMBER = '141340054'
group by RevCode
order by revcode

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-01 : 15:44:34

I think I understood your requirement, but if this isn't what you are looking for can you tell me more?

with CTE as
(
select
   RevCode,
   claimnumber,
   BilledCharges,
   row_number() over (partition by RevCode order by BilledCharges  asc) as minRow,
   row_number() over (partition by RevCode order by BilledCharges desc) as maxRow
from
   YourTable
)
select
   RevCode,
   a.ClaimNumber as MinClaimNumber,
   a.BilledCharges as MinCharges,
   b.ClaimNumber as MaxClaimNumber,
   b.BilledCharges as MaxCharges
from
   CTE a
   inner join CTE b on 
      a.RevCode = b.RevCode
where
   a.minRow = 1
   and b.maxRow = 1

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2011-04-04 : 09:55:51

This is perfect. Than you so much!

Now how would I add parameters for this. I need to be able to prompt for Date range and Tax ID. How would I do that?

See this below in red.

where clms_from >= @DateFrom and clms_thru <= @Datethru and PROVIDERTAXIDNUMBER = @TIN

with CTE as
(
select
RevCode,
claimnumber1,
BilledCharges,
row_number() over (partition by RevCode order by BilledCharges asc) as minRow,
row_number() over (partition by RevCode order by BilledCharges desc) as maxRow
from VW_Hospital_Billed_Charges_OutPatient_InPatient_CPT
----YourTable
)
select
a.RevCode as MinRevcode,
a.claimnumber1 as MinClaimNumber,
a.BilledCharges as MinCharges,
b.RevCode as MaxRevcode,
b.claimnumber1 as MaxClaimNumber,
b.BilledCharges as MaxCharges
from
CTE a
inner join CTE b on
a.RevCode = b.RevCode
where
a.minRow = 1
and b.maxRow = 1

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-04 : 11:10:17

Assuming that clms_from, clms_thru, and PROVIDERTAXIDNUMBER are columns in VW_Hospital_Billed_Charges_OutPatient_InPatient_CPT, all you need to do is to put the where condition after the view/table name.


with CTE as
(
select
RevCode,
claimnumber1,
BilledCharges,
row_number() over (partition by RevCode order by BilledCharges asc) as minRow,
row_number() over (partition by RevCode order by BilledCharges desc) as maxRow
from VW_Hospital_Billed_Charges_OutPatient_InPatient_CPT
where clms_from >= @DateFrom and clms_thru <= @Datethru and PROVIDERTAXIDNUMBER = @TIN
----YourTable
)

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2011-04-04 : 12:12:10

I figured it out on how to add the parameters.

Thanks for you help!!!!

@bottomdate datetime,
@topdate datetime,
@tin [varchar](9),
@BillTypes [varchar](50)

AS

with CTE as
(
select
RevCode,
claimnumber1,
BilledCharges,
row_number() over (partition by RevCode order by BilledCharges asc) as minRow,
row_number() over (partition by RevCode order by BilledCharges desc) as maxRow
from VW_Hospital_Billed_Charges_OutPatient_InPatient_CPT
where clms_from >= @bottomdate and clms_thru <= @topdate and (PROVIDERTAXIDNUMBER IN (@TIN)) and (CLM_4 IN(@BillTypes))

)
select
a.RevCode as MinRevcode,
a.claimnumber1 as MinClaimNumber,
a.BilledCharges as MinCharges,
b.RevCode as MaxRevcode,
b.claimnumber1 as MaxClaimNumber,
b.BilledCharges as MaxCharges
from
CTE a
inner join CTE b on
a.RevCode = b.RevCode
where
a.minRow = 1
and b.maxRow = 1

order by MinRevcode

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources