8 Replies Latest reply: Jul 9, 2014 6:09 AM by Rhona Corcoran

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:
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:

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

]
;

• ###### Re: sum in load

Try this:

EWTD:

TLMKey,

SLMKey,

MetricDesc,

MetricKey,

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

from SourcePath Group By TLMKey,SLMKey,MetricKey;

• ###### Re: sum in load

Thanks Harshal

Where do I put this do I put it after my full load or do you mean to put

in the full EWTD Table and then group by at the end

Sorry Confused

Rhona

Rhona Corcoran

ICT Services

Health Service Executive, Dr. Steeven's Hospital, Dublin 8.

Tel: 01 6352710   (am only) ;

Email: rhona.corcoran@hse.ie <mailto:rhona.corcoran@hse.ie>

• ###### Re: sum in load

You are using aggr function in script level so u need to mention group by at the end of table in which you are trying to use sum function.

Put it at the end of EWTD table after Sourcepath...

• ###### Re: sum in load

Thanks for your help will try that

Rhona Corcoran

ICT Services

Health Service Executive, Dr. Steeven's Hospital, Dublin 8.

Tel: 01 6352710   (am only) ;

Email: rhona.corcoran@hse.ie <mailto:rhona.corcoran@hse.ie>

• ###### Re: sum in load

Rhona ,

You can Load full EWTD table first without performing sum comment that after getting data for EWTD table ,

take resident load of EWTD table with new table and apply logic over their...

NewTbl:

TLMKey,

SLMKey,

MetricKey,

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

resident EWTD Group By TLMKey,SLMKey,MetricKey;

It will result some synthetic key..If possible do follow below approach..

EWTD:

...

store EWTD into EWTD.qvd;

drop table EWTD;

NewEWTD:

NewEWTD_1:

TLMKey &' '& SLMKey &' '& MetricKey as Key,

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

resident EWTD Group By TLMKey,SLMKey,MetricKey;

• ###### Re: sum in load

Sorry to take up all your time but this is what I have in now and it is

saying invalid expression.  When I reload the data:

EWTD:

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

,

if((TLMKey = 18 and SLMKey = 200 and MetricKey=4) or (TLMKey =

18 and SLMKey = 200 and MetricKey=46),MetricKey) as [Rates of

//Primary Care Metrics

if(TLMKey = 10 and SLMKey = 103 and (MetricKey>=1 or MetricKey

<=4),MetricKey) as ,

if(TLMKey=10 and SLMKey=192 and MetricKey=20,MetricKey) as

,

if(TLMKey = 10 and SLMKey = 191 and (MetricKey>=68 or

MetricKey <=79),MetricKey) as ,

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 ,

if((TLMKey=10 and SLMKey=101 and MetricKey=1) or (TLMKey=10

and SLMKey=101 and MetricKey=2),MetricKey) as ,

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')

;

ActualValue,

TLMKey,

SLMKey,

MetricDesc,

MetricKey,

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

,ActualValue)) as Outpatients

resident EWTD Group By TLMKey,SLMKey,MetricKey;

MonthMapping:

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

];

Rhona Corcoran

ICT Services

Health Service Executive, Dr. Steeven's Hospital, Dublin 8.

Tel: 01 6352710   (am only) ;

Email: rhona.corcoran@hse.ie <mailto:rhona.corcoran@hse.ie>

• ###### Re: Re: sum in load

Rhona ,

Try to implement like mentioned in attached qvw...

Hopeit works..

PFA

• ###### Re: sum in load

Thanks will try that and wont annoy you again