Improvement of scripting - please help!

[#1]

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;