January 10, 2019 at 5:43 am
I'm not getting the expected/desired results from my query. The data represents surgical procedures done during the year. I need to calculate a running total by provider, by insurance(FSC). The month of December, YearMonth="2018-12", had two procedures being performed by the same provider and same FSC. The running total is "duplicating" the number of procedures with UNICARE as the FSC. Instead of getting 6 units, the query is coming up with 7. Any thoughts?
select*
from #T
order by YearMonth,
FSC
select
YearMonth,
Provider,
CPT,
FSC,
sum(Units) over(partition by YearMonth, Provider, CPT, FSC order by YearMonth rows unbounded preceding) as MTDUnits
from #T
create table #T
(
YearMonth char(7),
Provider char(2),
CPT char(5),
FSC varchar(100),
Location varchar(25),
Units int
)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-01','MD','39599','BMC MASSHEALTH','INPATIENT',1)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-01','MD','39599','HEALTH PLAN INC','INPATIENT',1)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-02','MD','39599','BCBS FEDERAL','INPATIENT',1)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-04','MD','39599','BCBS FEDERAL','INPATIENT',0)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-04','MD','39599','HMO BLUE-SMG PCP','INPATIENT',1)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-04','MD','39599','TUFTS HEALTH PLAN/HMO SMG PCP','INPATIENT',0)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-05','MD','39599','BMC SIGNATURE ACO','INPATIENT',2)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-05','MD','39599','CIGNA','INPATIENT',1)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-05','MD','39599','HEALTH PLAN INC','INPATIENT',1)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-05','MD','39599','HMO BLUE-SMG PCP','INPATIENT',1)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-05','MD','39599','TUFTS PPO/DPO','INPATIENT',1)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-06','MD','39599','BMC SIGNATURE ACO','INPATIENT',1)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-06','MD','39599','HARVARD PILGRIM-OUTSIDE PCP','INPATIENT',1)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-06','MD','39599','HMO BLUE-SMG PCP','INPATIENT',1)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-06','MD','39599','NETWORK HEALTH-COMMCARE','INPATIENT',1)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-06','MD','39599','TUFTS MEDICARE PREF-SMG PCP','INPATIENT',1)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-07','MD','39599','BMC SIGNATURE ACO','INPATIENT',2)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-07','MD','39599','MEDICARE','INPATIENT',1)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-07','MD','39599','NETWORK HEALTH-COMMCARE','INPATIENT',1)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-08','MD','39599','BCBS FEDERAL','INPATIENT',1)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-08','MD','39599','BMC COMM CARE','INPATIENT',1)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-09','MD','39599','BMC COMM CARE','INPATIENT',1)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-09','MD','39599','BMC SIGNATURE ACO','INPATIENT',2)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-09','MD','39599','HEALTH PLAN INC','INPATIENT',1)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-12','MD','39599','BMC MASSHEALTH','INPATIENT',1)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-12','MD','39599','UNICARE','INPATIENT',1)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-12','MD','39599','BCBS INDEMINITY PPO','INPATIENT',1)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-12','MD','39599','MEDICARE','INPATIENT',1)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-12','MD','39599','TUFTS HEALTH PLAN/HMO SMG PCP','INPATIENT',1)
insert into #T(YearMonth, Provider, CPT, FSC, Location, Units) values('2018-12','MD','39599','UNICARE','INPATIENT',1)
January 10, 2019 at 6:02 am
I think the problem is that you have two rows for the same YearMonth. If you group them first then do the sum it should work:;with cte as
(
select YearMonth,
Provider,
CPT,
FSC,
sum(Units) as Units
from #T
group by YearMonth, Provider, CPT, FSC
)
select YearMonth,
Provider,
CPT,
FSC,
sum(Units) over(partition by YearMonth, Provider, CPT, FSC order by YearMonth rows unbounded preceding) as MTDUnits
from CTE
January 10, 2019 at 6:09 am
I'll give it a shot. Thanx.
January 10, 2019 at 8:17 am
I should also add that it's pointless to ORDER BY one of the partition expressions (in this case YearMonth). By definition, all rows within a partition have the same exact value for each of the partition expressions, so ordering by them doesn't guarantee a specific order and may produce different results every time. I would probably order by MTDUnits instead.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 10, 2019 at 8:26 am
Good point. Thanx.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply