UtterAccess Discussion Forums > Running Sum Question

UtterAccess Discussion Forums > And More... > Microsoft SQL Server

NineIron

Dec 9 2011, 10:08 AM

Here is my code...
#T2 contains data from post periods 11010, 11011, 11012, 11101, 11102, and 11103.
Post period 11103 does not contain any CPT codes of 96372, where the previous periods do. So, when it does the running sum, it doesn't sum the units from previous periods for that CPT code.
Any thoughts?

CODE

select PostPeriod, NPI, provider, location, location_mapping, location_category, category, CPT, modifier, multiplier, wRVU10,
    MTDCharges, MTDUnits, AdjwRVU10, MTDNetwRVU10,

    (select sum(isnull(b.MTDUnits,0))
    from #T2 b
    where b.PostPeriod<=a.PostPeriod
     and b.NPI=a.NPI
     and b.location=a.location
     and b.category=a.category
     and b.CPT=a.CPT
     and b.modifier=a.modifier) as YTDUnits
from #T2 a
where PostPeriod<='11103'
group by PostPeriod, NPI, provider, location, location_category, location_mapping, category, CPT, modifier, multiplier, wRVU10,
    MTDCharges, MTDUnits, AdjwRVU10, MTDNetwRVU10

Elwynd

Feb 9 2012, 10:35 AM

Doing it as a subquery in the select bit is the equivalent of an inner join, but you need an outer join. I'd do it the other way around:

CODE

SELECT a.PostPeriod, a.NPI, a.provider, a.location, a.location_mapping, a.location_category, a.category, a.CPT, a.modifier, a.multiplier, a.wRVU10,
    a.MTDCharges, a.MTDUnits, a.AdjwRVU10, a.MTDNetwRVU10, isnull(b.YTDUnits,0)
FROM #T2 a
LEFT JOIN (
  SELECT PostPeriod, NPI, Location, Category, CPT, modifier, sum(MTDUnits) as YTDUnits
  FROM #T2
  GROUP BY  PostPeriod, NPI, Location, Category, CPT, modifier
  ) as b
ON
     b.PostPeriod<=a.PostPeriod
     and b.NPI=a.NPI
     and b.location=a.location
     and b.category=a.category
     and b.CPT=a.CPT
     and b.modifier=a.modifier
WHERE a.PostPeriod <='11103'

Also I'm not sure why you needed the group by on the outer query...

Elwynd

Feb 9 2012, 12:43 PM

Lol, I've just seen that the OP was last year...! Twit

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.