Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Summing charges based on the record level NRV code Expand / Collapse
Author
Message
Posted Wednesday, June 20, 2007 12:11 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 8:55 PM
Points: 93, Visits: 284
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.
Post #375394
Posted Wednesday, June 20, 2007 12:41 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 8:03 PM
Points: 6,266, Visits: 2,027
???

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
Post #375401
Posted Wednesday, June 20, 2007 1:49 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 8:55 PM
Points: 93, Visits: 284
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.
Post #375412
Posted Tuesday, October 29, 2013 1:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 28, 2014 10:01 AM
Points: 1, Visits: 73
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!
Post #1509538
Posted Tuesday, October 29, 2013 11:42 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:24 AM
Points: 3,591, Visits: 5,100
I am neither a health care professional nor particularly healthy, but perhaps I can help.

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!
Post #1509656
Posted Friday, April 11, 2014 8:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:16 AM
Points: 33, Visits: 159
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!
Post #1560958
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse