6 Replies Latest reply: Jun 4, 2014 7:47 AM by Anand Chouhan RSS

    sum in script

    Rhona Corcoran

      Ok

       

      So I am trying to add the values together of the two metrics below in the script so that I can then use this value in other calculations in a table and I am not having much luck.  Can anyone help?

       

       

      sum

       

      (TLMKey=18 and SLMKey=200 and MetricKey=46) and (TLMKey=18 and SLMKey=200 and MetricKey=47),ActualValue as

      [Surgical Admissions]

       

      Full load statement:

       

       

      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

      ,
      if

      ((TLMKey

      =18 and

      SLMKey

      =142 and

      MetricKey

      =3) or

      (TLMKey

      =18 and

      SLMKey

      =142 and

      MetricKey

      =5),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

      =46) or

      (TLMKey

      =18 and

      SLMKey

      =200 and

      MetricKey

      =47),MetricKey

      ) as

      [Surgical Admissions]

      ,
      RDODesc

      ,
      RDOKey

      ,
      ReferredMM

      ,
      ReferredYYYY

      ,
      SGKey

      ,
      SGName

      ,
      ServiceProvider

      ,
      ServiceProviderType

      ,
      ServiceProviderNum

      ,
      if

      (ServiceProviderType

      ='H',ServiceProvider

      ) as

      Hospital

      ,
      if

      (ServiceProviderType

      ='T',ServiceProvider

      ) as

      HospitalGroup


      ;


      SQL

      SELECT

      *


      FROM

      Healthstat.dbo."MMI_ScorecardLevel3"


      where

      (tlmkey

      =

      18

      and

      slmkey

      =

      142

      and

      MetricKey=3)

      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=18

      and

      slmkey=200

      and

      MetricKey=47)

      and

      (ServiceProviderType='H'

      or

      ServiceProviderType='T')

      ;


      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

      ]

      ;

       

       

      Thanks for all the help so far this is a great forum

       

      Rhona

        • Re: sum in script
          Tresesco B

          Could you post the entire load statement (for this one only) you are trying with?

            • Re: sum in script
              Rhona Corcoran

              Hi

               

              I have put it up now

               

              Thanks

                • Re: sum in script
                  Tresesco B

                  I guess you have to try like:

                  Load

                            Sum(If(......)) as YourNewFieldName

                  From <> Group By;

                  • Re: sum in script
                    Anand Chouhan

                    Update now check sorry i forget to add drop statement

                     

                    Take resident table and load your table like below script

                     

                    tmpEWTD:

                    LOAD

                    ActualValue,

                    TLMKey,

                    SLMKey,

                    MetricDesc,

                    MetricKey,

                    if((TLMKey=18 and SLMKey=142 and MetricKey =3) or (TLMKey =18 and SLMKey =142 and MetricKey =5),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=46) or (TLMKey=18 and SLMKey=200 and MetricKey=47),MetricKey) as[Surgical Admissions],

                    RDODesc,

                    RDOKey,

                    ReferredMM,

                    ReferredYYYY,

                    SGKey,

                    SGName,

                    ServiceProvider,

                    ServiceProviderType,

                    ServiceProviderNum,

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

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

                     

                    SQL

                    SELECT

                    *

                    FROM

                    Healthstat.dbo."MMI_ScorecardLevel3"

                    where

                    (tlmkey=18 and slmkey=142 and MetricKey=3) 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=18 and slmkey=200 and MetricKey=47)

                    and

                    (ServiceProviderType='H' or ServiceProviderType='T');

                     

                    Noconcatenate

                    EWTD:

                    Load

                    ActualValue,

                    TLMKey,

                    SLMKey,

                    MetricDesc,

                    MetricKey,

                    Discharges,

                    [Reducing Health Care acquired Infection],

                    [Surgical Admissions],

                    RDODesc,

                    RDOKey,

                    ReferredMM,

                    ReferredYYYY,

                    SGKey,

                    SGName,

                    ServiceProvider,

                    ServiceProviderType,

                    ServiceProviderNum,

                    Hospital,

                    HospitalGroup,

                    sum(if((TLMKey=18 and SLMKey=200 and Match(MetricKey,46,47)) ,ActualValue)) as [Surgical Admissions]

                    Resident tmpEWTD

                    Group By

                    ActualValue,

                    TLMKey,

                    SLMKey,

                    MetricDesc,

                    MetricKey,

                    Discharges,

                    [Reducing Health Care acquired Infection],

                    [Surgical Admissions],

                    RDODesc,

                    RDOKey,

                    ReferredMM,

                    ReferredYYYY,

                    SGKey,

                    SGName,

                    ServiceProvider,

                    ServiceProviderType,

                    ServiceProviderNum,

                    Hospital,

                    HospitalGroup;

                     

                    Drop table tmpEWTD; //This is important

                      • Re: sum in script
                        Rhona Corcoran

                        Thanks anand I have tried this but the load is failing with the following error:

                         

                        Syntax error, missing/misplaced FROM:

                        tmpEWTD:

                         

                        I cant see where it is wrong

                          • Re: sum in script
                            Anand Chouhan

                            For your table load from SQL i renamed as

                             

                            And then create the resident load of the table by Name EWTD and call tmpEWTD on that and do aggregation and make the SUM field named [Surgical Admissions]


                            tmpEWTD:///This are Important

                            LOAD

                            ActualValue,

                            TLMKey,

                            SLMKey,

                            MetricDesc,

                            MetricKey,

                            if((TLMKey=18 and SLMKey=142 and MetricKey =3) or (TLMKey =18 and SLMKey =142 and MetricKey =5),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=46) or (TLMKey=18 and SLMKey=200 and MetricKey=47),MetricKey) as[Surgical Admissions],

                            RDODesc,

                            RDOKey,

                            ReferredMM,

                            ReferredYYYY,

                            SGKey,

                            SGName,

                            ServiceProvider,

                            ServiceProviderType,

                            ServiceProviderNum,

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

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

                             

                            SQL

                            SELECT

                            *

                            FROM

                            Healthstat.dbo."MMI_ScorecardLevel3"

                            where

                            (tlmkey=18 and slmkey=142 and MetricKey=3) 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=18 and slmkey=200 and MetricKey=47)

                            and

                            (ServiceProviderType='H' or ServiceProviderType='T');

                             

                            Noconcatenate /////This are Important

                            EWTD: ///This are Important

                            Load

                            ActualValue,

                            TLMKey,

                            SLMKey,

                            MetricDesc,

                            MetricKey,

                            Discharges,

                            [Reducing Health Care acquired Infection],

                            [Surgical Admissions],

                            RDODesc,

                            RDOKey,

                            ReferredMM,

                            ReferredYYYY,

                            SGKey,

                            SGName,

                            ServiceProvider,

                            ServiceProviderType,

                            ServiceProviderNum,

                            Hospital,

                            HospitalGroup,

                            sum(if((TLMKey=18 and SLMKey=200 and Match(MetricKey,46,47)) ,ActualValue)) as [Surgical Admissions] ///This are Important

                            Resident tmpEWTD ///This are Important

                            Group By ///This are Important

                            ActualValue,

                            TLMKey,

                            SLMKey,

                            MetricDesc,

                            MetricKey,

                            Discharges,

                            [Reducing Health Care acquired Infection],

                            [Surgical Admissions],

                            RDODesc,

                            RDOKey,

                            ReferredMM,

                            ReferredYYYY,

                            SGKey,

                            SGName,

                            ServiceProvider,

                            ServiceProviderType,

                            ServiceProviderNum,

                            Hospital,

                            HospitalGroup;

                             

                            Drop table tmpEWTD; //This is important



                            Note:- Now check i update and use Noconcatenate also.