sum in script

[rcorcoran]

Hi

REally sorry but I can't get this sum to work in the script - see below where I am trying to sum Metrics 18.205.3 and 18.205.4 as Outpatients in a resident table and grouping by TLMKey, SLMKey and Metric Key.  While it loads it does not add the metrics as required.

If anyone had a minute to look at it it would really belp me

Thanks

SET

ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='€#,##0.00;-€#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

OLEDB CONNECT32 TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Healthstat;Data Source=cifdevelopment;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=EL6470B0001;Use Encryption for Data=False;Tag with column collation when possible=False];


//-------- Start Multiple Select Statements ------

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,
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))
or (tlmkey=18 and slmkey = 205 and (MetricKey>=3 or MetricKey <=4))
and (ServiceProviderType='H' or ServiceProviderType='T' or ServiceProviderType='O')

;

LEFT JOIN (EWTD)
LOAD
TLMKey,
SLMKey,
MetricKey,
//sum(if((TLMKey=18 and SLMKey=142 and Match(MetricKey,3,4,5)) ,ActualValue)) as Outpatients
sum(if((TLMKey=18 and SLMKey=205) and ((MetricKey=3) and (MetricKey=4)) ,ActualValue)) as Outpatients
//sum(if((TLMKey=18 and SLMKey=142 and MetricKey=3) and (TLMKey=18 and SLMKey=142 and MetricKey=5) and (TLMKey=18 and SLMKey=142 and MetricKey=4))ActualValue as Outpatients


RESIDENT EWTD
GROUP BY TLMKey,SLMKey,MetricKey;

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

];

[VishalWaghole]

Hi Rhona,

What type of error you facing?

-- Regards,

Vishal Waghole

[rcorcoran]

It isn't giving me an error just not adding the metrics

Rhona

[jaimeaguilar]

Hi,

By definition if you need to perform any aggregation calculation within script (Sum, Max, Min, Avg, etc) you need to include a group by in your table with all the involved fields (In some way it's like using aggr at script level):

Table1:

Load

     Field1,

     Field2,

     Fieldn

From Table1

Group by Field1,Field2,Fieldn;

regards

[maxgro]

you have a strange condition in sum if

and ((MetricKey=3) and (MetricKey=4))

[santharubban]

try this

LEFT JOIN (EWTD)

LOAD

TLMKey,

SLMKey,

MetricKey,

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

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

RESIDENT EWTD

GROUP BY TLMKey,SLMKey,MetricKey;