SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Summing charges based on the record level NRV code


Summing charges based on the record level NRV code

Author
Message
J M-314995
J M-314995
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 328
My TSQL skill/knowledge level: Newbie (please go easy)

I'm trying to put together a report. I work at a smaller than average hospital. (I'm hoping there are healthcare people reading this forum...crossing my fingers)

So far I am able to pull...

Acct# Plan_Code Patient_type NRV


Before I go any further, I can't really disclose the data model our software vendor made available for us (which maybe why I might not be able to get the help I need from this list).

I need help with the NRV section of this report. I need an NRV_total column. I'm able to pull all NRV codes associated with each visit but I don't know how to add up all charges associated with each NRV code. Every charge code in our system is assigned to an NRV code.

Here is what what my report sort of looks like now... (Please use column headers above to reference)

525300 Blue Cross Outpatient 250
525300 Blue Cross Outpatient 258
525300 Blue Cross Outpatient 300

...and so on until it finishes listing all NRV codes for that visit and then it starts with the next account number that fits into the criteria I set.

How do I do take the NRV code of 250 and add up all the charges on account 525300? I know where to find the charge information but I don't know how to make SQL look at this one line at a time to add everything up.

Because I feel I'm at the newbie level, I probably didn't give enough information for people to try and help me out. Please let me know what other information is needed for me to get any assistance.

Psuedo code is welcome. I'll see what I can do from that.

I'd search the forum for help first but I don't know what terminology to use to do any searching. If anyone can give me terminology to use to search the forum or Books Online, I'd gladly welcome that as well.
noeld
noeld
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9528 Visits: 2048
???

select Account_nbr, NRV_CODE, SUM(charges) as Total_NRV
from [sourcetable]
group by Account_nbr, NRV_CODE

???

Or maybe what you want is simply :

select NRV_CODE, SUM(charges) as Total_NRV
from [sourcetable]
group by NRV_CODE


* Noel
J M-314995
J M-314995
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 328
The last time I used sum() on a different report, it summed up all charges throughout the report (not just the record level sum() ) and entered that one amount on every record.

This is a sample of a report that I wanted when using sum()... (Note: Amount= sum of however many times the charge code was used on patient accounts)

Charge_code Amount
...
0060N 195.00
0080N 255.45
11588 56.90
13065 298.00
...

This is a sample of what I got (not what I wanted)... (if you sum the whole amount column from that report)
...
0060N 15298.75
0080N 15298.75
11588 15298.75
13065 15298.75
...


When I get that part of the report done, I'll try to use the sum() function again and will post on whether or not that worked. Thanks.
Alan Bell
Alan Bell
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 78
I also work in a hospital writing SQL reports from our vendor system. I don't claim to be a SQL Guru, but my SQL knowledge outweighs my medical background of only 3 months!

The "group by" clause of the expression is the key in the example given. It will group the results on the fields listed in the "group by" clause and the "sum" expressions will be specific to those groups.

I am assuming that when you used "sum" before and were getting a report wide total, you did not have a group by clause.

I hope this helps clarify things a little.

Good luck!
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7219 Visits: 6431
I am neither a health care professional nor particularly healthy, but perhaps I can help. :-P

J M-314995 (6/20/2007)

Acct# Plan_Code Patient_type NRV
525300 Blue Cross Outpatient 250
525300 Blue Cross Outpatient 258
525300 Blue Cross Outpatient 300


So you have the above table and another table that contains the charges for each NRV. Nobody has mentioned that you'll need to JOIN above, probably on NRV (but possibly also on Acct#) to the second table of NRV charges.

Then you need to do a SELECT SUM(charges) with a GROUP BY. Whether that GROUP BY is by Acct# or Acct#, NRV or maybe even Acct#, Plan_Code, Patient_type, NRV, I can't tell from your description.

You needn't disclose proprietary data modeling information if you simply obfuscate your table/column names and data, then include only what is needed to give you a proper answer. And then you also should provide expected results.

Be sure to post your DDL and sample data in a consumable format, for example:

CREATE TABLE #Vists
(
Acct# VARCHAR(6)
,Plan_Code VARCHAR(10)
,Patient_type VARCHAR(20)
,NRV INT
);

INSERT INTO #Visits
SELECT '525300','Blue Cross','Outpatient',250
UNION ALL SELECT '525300','Blue Cross','Outpatient',258
UNION ALL SELECT '525300','Blue Cross','Outpatient',300;




Along with the second table that contains your NRV charges.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Drock
Drock
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 185
I also work at a small hospital and have been writing SQL to pull data out of our HIS.

Do you have a table in your database that stores each charge that is entered by a biller? This table would contain one row for each charge applied to each visit.

The reason I ask is because this is where I pull the nrv and the charge amount. Summing up these charges for a visit and grouping by visit and nrv will then provide the total for that nrv for that patient account. We've used this in the past to get an understanding of how much we spend on supplies, and in particular, on orthopedics.

Good luck!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search