0 Replies Latest reply: Jul 8, 2010 7:12 AM by ben.shreeve RSS

    Improvement of scripting - please help!

      Hi all,

      I'm currently using the script below, essentially it loads the same data set but refines it by a few criteria each time, it then converts it into a format to fit with a larger system. I want to simplify it so that I don't have to have a complete select statement for each variable. I have tried performing a standard Load statement with IF statements and Counts, but cannot get it to function.

       

      In the end I really want to end up with a table of data which is merely Period, Fiscal Year, Structure Key and Overall Count or value. My current script produces this but I was hoping that you guys might be able to suggest a more efficient way of making it work?

       

      Thanks again

       

      Ben

       

       

      ////================================================================================

      // COMPLAINTS DATA

      ////================================================================================

       

      ODBC CONNECT TO Standard_Apps_Data;

      Complaints_Data:

      ////================================================================================

      // Invoice Issues

      ////================================================================================

       

      Select

      Left(Period_Reported,2) as Period,

      Right(Period_Reported,4) as Fiscal_Year,

      Count(*) as P1Invoice

      FROM "Complaints_Data_Prototype".dbo.T600

      Where Complaint_Priority='P1 - Critical'

      and Complaint_Category='Service'

      and Complaint_Type='Documentation'

      and Complaint_Component='Invoice'

      group by Period_Reported

      ;

      Join (Complaints_Data)

      Select

      Left(Period_Reported,2) as Period,

      Right(Period_Reported,4) as Fiscal_Year,

      Count(*) as P2Invoice

      FROM "Complaints_Data_Prototype".dbo.T600

      Where Complaint_Priority='P2 - Major'

      and Complaint_Category='Service'

      and Complaint_Type='Documentation'

      and Complaint_Component='Invoice'

      group by Period_Reported

      ;

      Join (Complaints_Data)

      Select

      Left(Period_Reported,2) as Period,

      Right(Period_Reported,4) as Fiscal_Year,

      Count(*) as P3Invoice

      FROM "Complaints_Data_Prototype".dbo.T600

      Where Complaint_Priority='P3 - Minor'

      and Complaint_Category='Service'

      and Complaint_Type='Documentation'

      and Complaint_Component='Invoice'

      group by Period_Reported

      ;

      ////=================================================================================

      // Quality Issues

      ////=================================================================================

       

      //==============SUGAR QUALITY========================================================

      Join (Complaints_Data)

      Select

      Left(Period_Reported,2) as Period,

      Right(Period_Reported,4) as Fiscal_Year,

      Count(*) as P1QualitySugar

      FROM "Complaints_Data_Prototype".dbo.T600

      Where Complaint_Priority='P1 - Critical'

      and Complaint_Category='Product'

      and Complaint_Type='Quality'

      and Complaint_Component='Sugar'

      group by Period_Reported

      ;

      Join (Complaints_Data)

      Select

      Left(Period_Reported,2) as Period,

      Right(Period_Reported,4) as Fiscal_Year,

      Count(*) as P2QualitySugar

      FROM "Complaints_Data_Prototype".dbo.T600

      Where Complaint_Priority='P2 - Major'

      and Complaint_Category='Product'

      and Complaint_Type='Quality'

      and Complaint_Component='Sugar'

      group by Period_Reported

      ;

      Join (Complaints_Data)

      Select

      Left(Period_Reported,2) as Period,

      Right(Period_Reported,4) as Fiscal_Year,

      Count(*) as P3QualitySugar

      FROM "Complaints_Data_Prototype".dbo.T600

      Where Complaint_Priority='P3 - Minor'

      and Complaint_Category='Product'

      and Complaint_Type='Quality'

      and Complaint_Component='Sugar'

      group by Period_Reported

      ;

      //==============LIQUID QUALITY========================================================

       

      Join (Complaints_Data)

      Select

      Left(Period_Reported,2) as Period,

      Right(Period_Reported,4) as Fiscal_Year,

      Count(*) as P1QualityLiquid

      FROM "Complaints_Data_Prototype".dbo.T600

      Where Complaint_Priority='P1 - Critical'

      and Complaint_Category='Product'

      and Complaint_Type='Quality'

      and Complaint_Component='Liquid'

      group by Period_Reported

      ;

      Join (Complaints_Data)

      Select

      Left(Period_Reported,2) as Period,

      Right(Period_Reported,4) as Fiscal_Year,

      Count(*) as P2QualityLiquid

      FROM "Complaints_Data_Prototype".dbo.T600

      Where Complaint_Priority='P2 - Major'

      and Complaint_Category='Product'

      and Complaint_Type='Quality'

      and Complaint_Component='Liquid'

      group by Period_Reported

      ;

      Join (Complaints_Data)

      Select

      Left(Period_Reported,2) as Period,

      Right(Period_Reported,4) as Fiscal_Year,

      Count(*) as P3QualityLiquid

      FROM "Complaints_Data_Prototype".dbo.T600

      Where Complaint_Priority='P3 - Minor'

      and Complaint_Category='Product'

      and Complaint_Type='Quality'

      and Complaint_Component='Liquid'

      group by Period_Reported

      ;

      //==============PACKAGING QUALITY========================================================

       

      Join (Complaints_Data)

      Select

      Left(Period_Reported,2) as Period,

      Right(Period_Reported,4) as Fiscal_Year,

      Count(*) as P1QualityPackaging

      FROM "Complaints_Data_Prototype".dbo.T600

      Where Complaint_Priority='P1 - Critical'

      and Complaint_Category='Product'

      and Complaint_Type='Quality'

      and Complaint_Component='Packaging'

      group by Period_Reported

      ;

      Join (Complaints_Data)

      Select

      Left(Period_Reported,2) as Period,

      Right(Period_Reported,4) as Fiscal_Year,

      Count(*) as P2QualityPackaging

      FROM "Complaints_Data_Prototype".dbo.T600

      Where Complaint_Priority='P2 - Major'

      and Complaint_Category='Product'

      and Complaint_Type='Quality'

      and Complaint_Component='Packaging'

      group by Period_Reported

      ;

      Join (Complaints_Data)

      Select

      Left(Period_Reported,2) as Period,

      Right(Period_Reported,4) as Fiscal_Year,

      Count(*) as P3QualityPackaging

      FROM "Complaints_Data_Prototype".dbo.T600

      Where Complaint_Priority='P3 - Minor'

      and Complaint_Category='Product'

      and Complaint_Type='Quality'

      and Complaint_Component='Packaging'

      group by Period_Reported

      ;

      ////=============================================================================

      // COMPLAINTS SET

      ////============================================================================

       

      Complaints:

      Crosstable (ComplaintsTempDesc, Value,2)

      Load

      Period,

      Fiscal_Year,

      P1Invoice,

      P2Invoice,

      P3Invoice,

      P1QualitySugar,

      P2QualitySugar,

      P3QualitySugar,

      P1QualityLiquid,

      P2QualityLiquid,

      P3QualityLiquid,

      P1QualityPackaging,

      P2QualityPackaging,

      P3QualityPackaging

      Resident Complaints_Data;

      Left Join (Complaints)

      Load * Inline [ComplaintsTempDesc, StructureKey%

      ;

      Drop Field ComplaintsTempDesc;

      Drop Table Complaints_Data;