5 Replies Latest reply: Jul 22, 2014 12:14 PM by Santharubban Kumarasamy Shanmugasundaram RSS

    sum in script

    Rhona Corcoran

       

      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

      ]
      ;