Full Version: Running Sum Question
NineIron
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,

(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,
Elwynd
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,
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
Lol, I've just seen that the OP was last year...! Twit