June 20, 2007 at 12:11 pm
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.
June 20, 2007 at 12:41 pm
???
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
June 20, 2007 at 1:49 pm
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.
October 29, 2013 at 1:05 pm
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!
October 29, 2013 at 11:42 pm
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 NRV525300 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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 11, 2014 at 8:19 am
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!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply