Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;