Problem with running total

Question

Post reply

Problem with running total

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

NineIron SSChampion Points: 12529 More actions · Answer 1

I fixed everything...................

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable

--===== Create the test table with

CREATE TABLE #mytable

(

PostPeriod varchar(50),

NPI varchar(50),

location varchar(50),

location_mapping varchar(50),

location_category varchar(50),

category varchar(50),

CPT varchar(8),

modifier nvarchar(50),

MTDCharges decimal(10,2),

MTDUnits decimal(10,2)

)

-- This is how I came up with the data

/*SELECT 'SELECT '

+ QUOTENAME(S_PostPd,'''')+','

+ QUOTENAME(S_ProvNPI,'''')+','

+ QUOTENAME(S_Location,'''')+','

+ QUOTENAME(location_mapping,'''')+','

+ QUOTENAME(location_category,'''')+','

+ QUOTENAME(category,'''')+','

+ QUOTENAME(S_CPT,'''')+','

+ QUOTENAME(S_Modifier,'''')+','

+ QUOTENAME(sum(S_Charge_Amt),'''')+','

+ QUOTENAME(sum(S_Units),'''')

+ ' UNION ALL'

FROM formatIDX_Service a

INNER JOIN U_ref_Location b ON

a.S_Location=b.location

INNER JOIN U_Ref_CategoryMapping c ON

a.S_CPT=c.CPT

WHERE S_PostPd BETWEEN '11101' AND '11103'

AND S_CPT!=''

AND S_ProvNPI!=''

AND S_ProvNPI='1679552582'

AND charindex('(',S_Provider,1)=0

group by S_PostPd, S_ProvNPI, S_Location, location_mapping, location_category, category, S_CPT, S_Modifier*/

--===== Insert the test data into the test table

INSERT INTO #mytable (PostPeriod, NPI, location, location_mapping, location_category, category, CPT, modifier, MTDCharges, MTDUnits)

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','ADMIN/INJ','096365',' ','2145','13' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','ADMIN/INJ','096365','59','660','4' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC ECHOS','093306',' ','12100','22' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC ECHOS','093306','26','1210','11' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC ECHOS','093306','TC','1760','4' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC HOLTERS','093224',' ','2480','8' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC HOLTERS','093224','QD','1550','5' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC NUCLEAR','078452',' ','9420','12' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC NUCLEAR','078452','26','750','6' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC NUCLEAR','078452','TC','500','1' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC OTHER','093000',' ','1265','23' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC PACERS','093280',' ','460','4' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC PACERS','093282',' ','140','1' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC PACERS','093283',' ','340','2' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC PACERS','093284',' ','200','1' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC PACERS','093289','26','80','1' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC PACERS','093289','TC','115','1' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC PACERS','093293',' ','120','1' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC STRESS','093015',' ','7830','27' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','COUNSELING','099406',' ','175','7' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','DRUG/INJECTABLE','096372',' ','315','7' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','DRUG/INJECTABLE','096374',' ','625','5' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','DRUG/INJECTABLE','A9500',' ','2035','11' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','DRUG/INJECTABLE','J0280',' ','45','9' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','DRUG/INJECTABLE','J1245',' ','1050','21' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','DRUG/INJECTABLE','J2785',' ','1300','20' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','DRUG/INJECTABLE','J7050',' ','45','9' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','MISCELLANEOUS','1000F',' ','0','1' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','MISCELLANEOUS','1034F',' ','0','2' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','MISCELLANEOUS','G8447',' ','0','26' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','OFFICE VISIT','006004',' ','0','1' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','OFFICE VISIT','099051',' ','40','1' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','OFFICE VISIT','099213',' ','4680','36' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','OFFICE VISIT','099213','25','780','6' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','OFFICE VISIT','099214',' ','5265','27' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','OFFICE VISIT','099214','25','390','2' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','RAD-GL CT SCAN','076376','26','50','1' UNION ALL

SELECT '11101','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','RAD-GL CT SCAN','076376','TC','200','1' UNION ALL

SELECT '11101','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','CARDIAC ECHOS','093306','26','3960','36' UNION ALL

SELECT '11101','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','CARDIAC ECHOS','093306','26/52','110','1' UNION ALL

SELECT '11101','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','CARDIAC NUCLEAR','078452','26','250','2' UNION ALL

SELECT '11101','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','CARDIAC OTHER','093010',' ','2115','47' UNION ALL

SELECT '11101','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','CONSULTATIONS','099254',' ','4760','14' UNION ALL

SELECT '11101','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','PHY-HOSPITAL','099222','AF','6500','25' UNION ALL

SELECT '11101','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','PHY-HOSPITAL','099223','AF','380','1' UNION ALL

SELECT '11101','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','PHY-HOSPITAL','099231',' ','160','2' UNION ALL

SELECT '11101','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','PHY-HOSPITAL','099232',' ','3780','27' UNION ALL

SELECT '11101','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','SKILLED NURSING','099309',' ','-160','-1' UNION ALL

SELECT '11101','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','CARDIAC ECHOS','093306',' ','550','1' UNION ALL

SELECT '11101','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','CARDIAC ECHOS','093306','26','1870','17' UNION ALL

SELECT '11101','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','CARDIAC NUCLEAR','078452','26','125','1' UNION ALL

SELECT '11101','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','CARDIAC OTHER','093010',' ','900','20' UNION ALL

SELECT '11101','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','CARDIAC STRESS','093016',' ','75','1' UNION ALL

SELECT '11101','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','CARDIAC STRESS','093018',' ','155','1' UNION ALL

SELECT '11101','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','CONSULTATIONS','099244',' ','2280','6' UNION ALL

SELECT '11101','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','OFFICE VISIT','099204','AF','600','2' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','ADMIN/INJ','096365',' ','4125','25' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC ECHOS','093306',' ','15950','29' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC ECHOS','093306','26','1320','12' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC ECHOS','093306','TC','440','1' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC HOLTERS','093224',' ','3410','11' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC HOLTERS','093224','QD','620','2' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC NUCLEAR','078452',' ','16485','21' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC NUCLEAR','078452','26','500','4' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC NUCLEAR','078452','TC','0','0' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC OTHER','093000',' ','1485','27' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC PACERS','093280',' ','575','5' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC PACERS','093282',' ','140','1' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC PACERS','093283','26','170','1' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC PACERS','093283','TC','170','1' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC PACERS','093284',' ','200','1' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC PACERS','093293',' ','240','2' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC PACERS','093293','26','120','1' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC PACERS','093293','TC','120','1' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC PACERS','093296',' ','80','1' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC STRESS','093015',' ','10440','36' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CONSULTATIONS','099244',' ','0','0' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','COUNSELING','099406',' ','50','2' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','DRUG/INJECTABLE','096372',' ','90','2' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','DRUG/INJECTABLE','096374',' ','125','1' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','DRUG/INJECTABLE','A9500',' ','3700','20' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','DRUG/INJECTABLE','J0280',' ','60','12' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','DRUG/INJECTABLE','J1245',' ','2250','45' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','DRUG/INJECTABLE','J2785',' ','1040','16' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','DRUG/INJECTABLE','J7050',' ','60','12' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','MISCELLANEOUS','1000F',' ','0','1' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','MISCELLANEOUS','1034F',' ','0','1' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','MISCELLANEOUS','G8447',' ','0','26' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','OFFICE VISIT','099204','AF','1500','5' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','OFFICE VISIT','099211',' ','160','4' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','OFFICE VISIT','099213',' ','6760','52' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','OFFICE VISIT','099213','25','390','3' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','OFFICE VISIT','099214',' ','5265','27' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','OFFICE VISIT','099215',' ','265','1' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','RAD-GL CT SCAN','076376',' ','750','3' UNION ALL

SELECT '11102','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','TEST','069210',' ','95','1' UNION ALL

SELECT '11102','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','CARDIAC ECHOS','093306','26','5610','51' UNION ALL

SELECT '11102','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','CARDIAC ECHOS','093312','26','215','1' UNION ALL

SELECT '11102','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','CARDIAC OTHER','093010',' ','31725','705' UNION ALL

SELECT '11102','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','CARDIAC OTHER','093010','77','225','5' UNION ALL

SELECT '11102','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','CARDIAC OTHER','093010','GV','45','1' UNION ALL

SELECT '11102','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','CARDIAC STRESS','093016',' ','375','5' UNION ALL

SELECT '11102','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','CARDIAC STRESS','093018',' ','775','5' UNION ALL

SELECT '11102','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','CONSULTATIONS','099254',' ','340','1' UNION ALL

SELECT '11102','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','PHY-HOSPITAL','099222',' ','260','1' UNION ALL

SELECT '11102','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','PHY-HOSPITAL','099222','AF','3900','15' UNION ALL

SELECT '11102','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','PHY-HOSPITAL','099231',' ','80','1' UNION ALL

SELECT '11102','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','PHY-HOSPITAL','099232',' ','140','1' UNION ALL

SELECT '11102','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','PHY-HOSPITAL','099233',' ','-200','-1' UNION ALL

SELECT '11102','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','CARDIAC ECHOS','093306','26','2090','19' UNION ALL

SELECT '11102','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','CARDIAC HOLTERS','093227',' ','210','2' UNION ALL

SELECT '11102','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','CARDIAC HOLTERS','093227','QT','210','2' UNION ALL

SELECT '11102','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','CARDIAC OTHER','093010',' ','4770','106' UNION ALL

SELECT '11102','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','CARDIAC STRESS','093016',' ','225','3' UNION ALL

SELECT '11102','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','CARDIAC STRESS','093018',' ','465','3' UNION ALL

SELECT '11102','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','CONSULTATIONS','099244',' ','1140','3' UNION ALL

SELECT '11102','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','PHY-HOSPITAL','099219','AF','200','1' UNION ALL

SELECT '11102','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','PHY-HOSPITAL','099222','AF','260','1' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','ADMIN/INJ','096365',' ','3795','23' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','ADMIN/INJ','096365','59','330','2' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC ECHOS','093306',' ','18700','34' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC ECHOS','093306','26','1980','18' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC ECHOS','093306','TC','1760','4' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC HOLTERS','093224',' ','3720','12' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC HOLTERS','093224','QD','930','3' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC NUCLEAR','078452',' ','15700','20' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC NUCLEAR','078452','26','625','5' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC OTHER','093000',' ','2035','37' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC PACERS','093279',' ','110','1' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC PACERS','093280',' ','345','3' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC PACERS','093280','26','25','1' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC PACERS','093280','TC','90','1' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC PACERS','093283',' ','340','2' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CARDIAC STRESS','093015',' ','11600','40' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','CONSULTATIONS','099244',' ','760','2' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','COUNSELING','099406',' ','175','7' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','COUNSELING','099407',' ','100','2' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','DRUG/INJECTABLE','096372',' ','-135','-3' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','DRUG/INJECTABLE','096372','59','135','3' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','DRUG/INJECTABLE','A9500',' ','3885','21' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','DRUG/INJECTABLE','J0280',' ','40','8' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','DRUG/INJECTABLE','J1245',' ','775','20' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','DRUG/INJECTABLE','J2785',' ','1040','16' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','DRUG/INJECTABLE','J7050',' ','40','8' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','MISCELLANEOUS','1000F',' ','0','3' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','MISCELLANEOUS','1034F',' ','0','3' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','MISCELLANEOUS','G8447',' ','0','41' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','OFFICE VISIT','099024',' ','0','1' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','OFFICE VISIT','099204',' ','300','1' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','OFFICE VISIT','099204','25','300','1' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','OFFICE VISIT','099204','25/AF','300','1' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','OFFICE VISIT','099204','AF','2100','7' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','OFFICE VISIT','099211',' ','40','1' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','OFFICE VISIT','099213',' ','6630','51' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','OFFICE VISIT','099213','25','910','7' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','OFFICE VISIT','099214',' ','6240','32' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','OFFICE VISIT','099215','AF','265','1' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','OFFICE VISIT','099215','AF/25','265','1' UNION ALL

SELECT '11103','1679552582','DOCTORS OFFICE-LIBERTY STREET','DOCTORS OFFICE','NON LAB','RAD-GL CT SCAN','076376',' ','500','2' UNION ALL

SELECT '11103','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','CARDIAC ECHOS','093306',' ','550','1' UNION ALL

SELECT '11103','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','CARDIAC ECHOS','093306','26','4950','45' UNION ALL

SELECT '11103','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','CARDIAC NUCLEAR','078452','26','750','6' UNION ALL

SELECT '11103','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','CARDIAC OTHER','093010',' ','2070','46' UNION ALL

SELECT '11103','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','CARDIAC STRESS','093016','53','75','1' UNION ALL

SELECT '11103','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','CARDIAC STRESS','093018',' ','155','1' UNION ALL

SELECT '11103','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','CONSULTATIONS','099254',' ','4420','13' UNION ALL

SELECT '11103','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','PHY-HOSPITAL','099222',' ','1040','4' UNION ALL

SELECT '11103','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','PHY-HOSPITAL','099222','AF','7280','28' UNION ALL

SELECT '11103','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','PHY-HOSPITAL','099231',' ','880','11' UNION ALL

SELECT '11103','1679552582','INPATIENT-BROCKTON HOSPITAL','INPATIENT HOSPITAL','NON LAB','PHY-HOSPITAL','099232',' ','2240','16' UNION ALL

SELECT '11103','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','CARDIAC ECHOS','093306','26','2860','26' UNION ALL

SELECT '11103','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','CARDIAC ECHOS','093312','26','215','1' UNION ALL

SELECT '11103','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','CARDIAC HOLTERS','093227',' ','210','2' UNION ALL

SELECT '11103','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','CARDIAC NUCLEAR','078452','26','375','3' UNION ALL

SELECT '11103','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','CARDIAC OTHER','093010',' ','540','12' UNION ALL

SELECT '11103','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','OFFICE VISIT','099204',' ','600','2' UNION ALL

SELECT '11103','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','OFFICE VISIT','099204','AF','900','3' UNION ALL

SELECT '11103','1679552582','OUTPATIENT-BROCKTON HOSPITAL','OP HOSPITAL','NON LAB','PHY-HOSPITAL','099219',' ','200','1'

--My code that doesn't work

select PostPeriod, NPI, location, location_mapping, location_category, category, CPT, modifier, MTDCharges, MTDUnits,

(select sum(isnull(b.MTDCharges,0))

from #mytable 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 YTDCharges,

(select sum(isnull(b.MTDUnits,0))

from #mytable 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 #mytable a

where PostPeriod<='11103'

group by PostPeriod, NPI, location, location_mapping, location_category, category, CPT, modifier, MTDCharges, MTDUnits

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

1) you still haven't provided what you think is the expected outcome for some set of rows that you think is giving you bad output.

2) your use of month-to-date is not meaningful. MTD is only valid in the context of a particular month: is it January, February, ... current month at runtime, what? Typically when such reports are done you will see a table of outputs with a column for every month of the current year then a yearly total:

cola colb colc ... JanTotal FebTotal MarTotal ... DecTotal YearlyTotal

In your output, what really are you looking for in a single month-to-date computation?

BTW, I will point out that forums are typically used for simple question/answer scenarios. Your need really isn't that. 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

David Burrows SSC Guru Points: 65138 More actions · Answer 3

OK, my first idea for an answer before I did the cte thinng was

SELECTa.PostPeriod, a.NPI, a.location, a.location_mapping, a.location_category, a.category, a.CPT, a.modifier, a.MTDCharges, a.MTDUnits,

SUM(b.MTDCharges) AS [YTDCharges],

SUM(b.MTDUnits) AS [YTDUnits]

FROM #mytable a

JOIN #mytable b

ON b.NPI=a.NPI

AND b.location=a.location

AND b.category=a.category

AND b.CPT=a.CPT

AND b.modifier=a.modifier

AND b.PostPeriod<=a.PostPeriod

GROUP BY a.PostPeriod, a.NPI, a.location, a.location_mapping, a.location_category, a.category, a.CPT, a.modifier, a.MTDCharges, a.MTDUnits

Far away is close at hand in the images of elsewhere.
Anon.

NineIron SSChampion Points: 12529 More actions · Answer 4

PostPeriod NPI location location_mapping location_category category MTDUnits YTDUnits

110101679552582DOCTORS OFFICE-LIBERTY STREETDOCTORS OFFICENON LABADMIN/INJ13.0013.00

110111679552582DOCTORS OFFICE-LIBERTY STREETDOCTORS OFFICENON LABADMIN/INJ16.0029.00

110121679552582DOCTORS OFFICE-LIBERTY STREETDOCTORS OFFICENON LABADMIN/INJ18.0042.00

111011679552582DOCTORS OFFICE-LIBERTY STREETDOCTORS OFFICENON LABADMIN/INJ17.0064.00

111021679552582DOCTORS OFFICE-LIBERTY STREETDOCTORS OFFICENON LABADMIN/INJ25.0080.00

111031679552582DOCTORS OFFICE-LIBERTY STREETDOCTORS OFFICENON LABADMIN/INJ25.00114.00

Results are above. Code is below. I created a temp table based on the sample of data that I sent in the previous post. See below for query. They ran a query on the temporary table, #T3.

As you add up the MTD(PostPeriods) and YTD columns, you can see that they don't add up correctly.

select PostPeriod, NPI, location, location_mapping, location_category, category, CPT, modifier, MTDCharges, MTDUnits,

(select sum(isnull(b.MTDCharges,0))

from #mytable 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 YTDCharges,

(select sum(isnull(b.MTDUnits,0))

from #mytable 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

into #T3

from #mytable a

where PostPeriod<='11103'

group by PostPeriod, NPI, location, location_mapping, location_category, category, CPT, modifier, MTDCharges, MTDUnits

select PostPeriod, NPI, location, location_mapping, location_category, category, sum(MTDUnits) as MTDUnits, sum(YTDUnits) as YTDUnits

from #T3

where category='ADMIN/INJ'

group by PostPeriod, NPI, location, location_mapping, location_category, category

order by PostPeriod

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Sorry, but I am at a loss here. You are now dropping columns off of the T3 query. This is too complex (for me anyway) to help you via a forum thread. Perhaps someone else can spend enough time with you to figure out a) exactly what you need from the data and b) how to get there. Or you can hire a professional to do the same. Best of luck with it either way!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

NineIron (12/14/2011)
Jeff,
Pardon my ignorance. I should have read your post regarding forum etiquette first.
I hope I did this correctly.........here is three months of data for one provider. From here I need YTD charges and units. I don't have an identity column in the data.

I've never had a better compliment than being confused for Jeff Moden. Thank you! 😀

I'm Craig, Jeff wrote the article I linked too.

I'll dig through the rest of this in a bit, work's busy, but I just had to enjoy that moment. I haven't forgotten the thread though.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

NineIron SSChampion Points: 12529 More actions · Answer 7

NineIron

SSChampion

Points: 12529

December 14, 2011 at 12:08 pm

#1421528

Sorry for the trouble.

NineIron SSChampion Points: 12529 More actions · Answer 8

My bad. Sorry.

Please use my last thread with the code and sample data. I got things messed up a bit earlier.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 9

It's been 10 days since the last post on this thread. Did you find a solution or do you still need one?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

NineIron SSChampion Points: 12529 More actions · Answer 10

I didn't get a working solution but, I've moved on.

Thanx anyways.