# sum in load

**Rhona Corcoran**Jul 9, 2014 4:45 AM

Hi I still havent got very far with this. so here is the full script. You can see where I am trying to sum Metric Key 3,4 and 5 where the TLMKey is 18 and the SLMKey is 142. I know you have to group by somehow but not sure how. If anyone had a minute could they help please. Thanks

EWTD:**LOAD** ActualValue,

TLMKey,

SLMKey,

MetricDesc,

MetricKey,

//Acute hospital metrics

if((TLMKey=18 and SLMKey=142 and MetricKey=3) or (TLMKey=18 and SLMKey=142 and MetricKey=5) or (TLMKey=18 and SLMKey=142 and MetricKey=4),MetricKey) as Discharges,

sum(if((TLMKey=18 and SLMKey=142 and Match(MetricKey,3,4,5)) ,ActualValue)) as Outpatients,

if(TLMKey=18 and SLMKey=166 and MetricKey=4,MetricKey) as [Reducing Health Care acquired Infection],

if((TLMKey = 18 and SLMKey = 200 and MetricKey=4) or (TLMKey = 18 and SLMKey = 200 and MetricKey=46),MetricKey) as [Rates of Readmission],

//Primary Care Metrics

if(TLMKey = 10 and SLMKey = 103 and (MetricKey>=1 or MetricKey <=4),MetricKey) as [Community Intervention Team by Source],

if(TLMKey=10 and SLMKey=192 and MetricKey=20,MetricKey) as [Physio Wait >12 wks],

if(TLMKey = 10 and SLMKey = 191 and (MetricKey>=68 or MetricKey <=79),MetricKey) as [OccTherapy wait> 16 weeks],

if((TLMKey=10 and SLMKey=101 and MetricKey=1) or (TLMKey=10 and SLMKey=101 and MetricKey=2) or (TLMKey=10 and SLMKey=101 and MetricKey=11) or(TLMKey=18 and SLMKey=142 and MetricKey=12),MetricKey) as [Ortho No Wait 12 Mths],

if((TLMKey=10 and SLMKey=101 and MetricKey=1) or (TLMKey=10 and SLMKey=101 and MetricKey=2),MetricKey) as [Ortho Wait >4 yrs],

if((TLMKey=10 and SLMKey = 101 and (MetricKey>=1 or MetricKey <=4) or (TLMKey=10 and SLMKey = 101 and (MetricKey>=5 or MetricKey <=16))),MetricKey) as Orthodontics,

RDODesc,

RDOKey,

ReferredMM,

ReferredYYYY,

SGKey,

SGName,

ServiceProvider,

ServiceProviderType,

ServiceProviderNum,

if(ServiceProviderType='H',ServiceProvider) as Hospital,

if(ServiceProviderType='T',ServiceProvider) as HospitalGroup,

if(ServiceProviderType='O',ServiceProvider) as Team

;

**SQL** *SELECT* ****FROM* *Healthstat.dbo."MMI_ScorecardLevel3"**where* *(tlmkey* *=* *18* *and* *slmkey* *=* *142* *and* *MetricKey=3)* *or* *(tlmkey* *=* *18* *and* *slmkey* *=* *142* *and* *MetricKey=4)or(tlmkey* *=* *18* *and* *slmkey* *=* *142* *and* *MetricKey=5)or* *(tlmkey* *=* *18* *and* *slmkey* *=* *166* *and* *MetricKey=4)* *or* *(tlmkey* *=* *18* *and* *slmkey* *=* *200* *and* *MetricKey=46* *)* *or* *(tlmkey* *=* *10* *and* *slmkey* *=* *192* *and* *MetricKey=20* *)or* *(tlmkey* *=* *18* *and* *slmkey* *=* *200* *and* *MetricKey=4)or* *(tlmkey* *=* *10* *and* *slmkey* *=* *103* *and* *(MetricKey>=1* *or* *MetricKey* *<=4))or* *(tlmkey* *=* *10* *and* *slmkey* *=* *191* *and* *(MetricKey>=68* *or* *MetricKey* *<=79))or* *(tlmkey* *=* *10* *and* *slmkey* *=* *101* *and* *(MetricKey>=1* *or* *MetricKey* *<=2))* *or* *(tlmkey* *=* *10* *and* *slmkey* *=* *101* *and* *(MetricKey>=11* *or* *MetricKey* *<=12))* *or* *(tlmkey* *=* *10* *and* *slmkey* *=* *101* *and* *MetricKey=10)* *or* *(tlmkey* *=* *10* *and* *slmkey* *=* *101* *and* *MetricKey=16)* *or* *(tlmkey=10* *and* *slmkey* *=* *101* *and* *(MetricKey>=1* *or* *MetricKey* *<=4))* *or* *(tlmkey=10* *and* *slmkey* *=* *101* *and* *(MetricKey>=5* *or* *MetricKey<=16))**and* *(ServiceProviderType='H'* *or* *ServiceProviderType='T'* *or* *ServiceProviderType='O')*;

MonthMapping:

**LOAD** * INLINE [

ReferredMM, Month

1, Jan

2, Feb

3, Mar

4, Apr

5, May

6, June

7, July

8, Aug

9, Sept

10, Oct

11, Nov

12, Dec

];