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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy