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
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