COGNOS QUERY
STRUCTURE,1,1
DATABASE,ARIntelligence
DATASOURCENAME,C:\GlobalCube\SYSTEM\ARIntelligence\IQD\service\Service_Eroeffnung.imr
TITLE,Service_Eroeffnung.imr
BEGIN SQL
select c106 as c1,
	   c105 as c2,
	   c104 as c3,
	   c103 as c4,
	   c102 as c5,
	   c101 as c6,
	   c72 as c7,
	   c100 as c8,
	   c99 as c9,
	   c98 as c10,
	   c97 as c11,
	   c96 as c12,
	   c95 as c13,
	   c94 as c14,
	   c93 as c15,
	   c92 as c16,
	   c91 as c17,
	   c90 as c18,
	   c89 as c19,
	   c88 as c20,
	   c87 as c21,
	   c86 as c22,
	   c79 as c23,
	   c85 as c24,
	   c84 as c25,
	   '1' as c26,
	   c83 as c27,
	   '' as c28,
	   c82 as c29,
	   c81 as c30,
	   c81 as c31,
	   c80 as c32,
	   c79 as c33,
	   c78 as c34,
	   c77 as c35,
	   c76 as c36,
	   c75 as c37,
	   c74 as c38,
	   c73 as c39,
	   c61 as c40,
	   c72 as c41,
	   1 as c42,
	   c71 as c43,
	   '' as c44,
	   c70 as c45,
	   c69 as c46,
	   c68 as c47,
	   c67 as c48,
	   c66 as c49,
	   c65 as c50,
	   '' as c51,
	   XCOUNT(c105 for c61) as c52,
	   1 / (XCOUNT(c105 for c61)) as c53,
	   (1 / (XCOUNT(c105 for c61))) as c54,
	   CASE WHEN ((c64) > 20) THEN (((1 / (XCOUNT(c105 for c61))))) ELSE (0) END as c55,
	   c63 as c56,
	   c62 as c57
from 
(select (T1."No_" || ' - ' || T2."Name") as c61,
	   CASE WHEN (T6."Resource Group No_" = 'SB') THEN ('SB') ELSE ('Rest') END as c62,
	   T6."Resource Group No_" as c63,
	   extract(DAY FROM (now()) - T1."Order Date") as c64,
	   CASE WHEN ((T5."No_" IN ('0052050','0052051','0052052','0052054')) and (T5."Quantity" > 0)) THEN (1) WHEN ((T5."No_" IN ('0052050','0052051','0052052','0052054')) and (T5."Quantity" < 0)) THEN (-1) ELSE (0) END as c65,
	   T1."Gen_ Prod_ Posting Group" as c66,
	   T1."Service Posting Group" as c67,
	   T4."First Name" || ' ' || T4."Last Name" as c68,
	   T4."Last Name" as c69,
	   T4."First Name" as c70,
	   CASE WHEN (T1."Location Code" = 'LBS') THEN ('10') WHEN (T1."Location Code" = 'WLS') THEN ('20') WHEN (T1."Location Code" = 'STA') THEN ('30') WHEN (T1."Location Code" = 'GER') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') ELSE null END as c71,
	   T1."Order Date" as c72,
	   CASE WHEN (T2."No_" LIKE 'INT%') THEN ('Intern') ELSE (T3."Description") END as c73,
	   T3."Description" as c74,
	   T2."No_" || ' - ' || T2."Name" as c75,
	   T2."Name" as c76,
	   T2."No_" as c77,
	   T1."VIN" || ' - ' || T1."Model" as c78,
	   T1."Model" as c79,
	   CASE WHEN (T1."Make Code" IN ('BMW','BMW-MINI')) THEN (T1."Make Code") ELSE ('Fremdfabrikat') END as c80,
	   T2."Customer Group Code" as c81,
	   CASE WHEN (T1."Make Code" IN ('BMW-MINI')) THEN (T1."Make Code") ELSE ('BMW') END as c82,
	   (od_left(T1."Department Code",2)) as c83,
	   T1."Service Advisor No_" as c84,
	   T1."Service Advisor No__Pick-Up" as c85,
	   T1."Prod_ Year" as c86,
	   T1."Model No_" as c87,
	   T1."Model Code" as c88,
	   T1."Mileage" as c89,
	   T1."VIN" as c90,
	   T1."No_ Series" as c91,
	   T1."Document Date" as c92,
	   T1."Sell-to Customer Name 2" as c93,
	   T1."Sell-to Customer Name" as c94,
	   T1."Order Class" as c95,
	   T1."Salesperson Code" as c96,
	   T1."Customer Posting Group" as c97,
	   T1."Make Code" as c98,
	   T1."Department Code" as c99,
	   T1."Location Code" as c100,
	   T1."Bill-to Name 2" as c101,
	   T1."Bill-to Name" as c102,
	   T1."Bill-to Customer No_" as c103,
	   T1."Sell-to Customer No_" as c104,
	   T1."No_" as c105,
	   T1."Document Type" as c106
from "Automag7x"."dbo"."Automag GmbH$Service Line" T5,
	(((("Automag7x"."dbo"."Automag GmbH$Service Header" T1 left outer join "Automag7x"."dbo"."Automag GmbH$Customer" T2 on T2."No_" = T1."Sell-to Customer No_") left outer join "Automag7x"."dbo"."Automag GmbH$Customer Group" T3 on T2."Customer Group Code" = T3."Code") left outer join "Automag7x"."dbo"."Automag GmbH$Employee" T4 on T4."No_" = T1."Service Advisor No_") left outer join "Automag7x"."dbo"."Automag GmbH$Resource" T6 on T1."Service Advisor No_" = T6."No_")
where (T1."No_" = T5."Document No_")
and (T1."Document Type" <> 0)
) D1
order by c2 asc

END SQL
COLUMN,0,Document Type
COLUMN,1,No
COLUMN,2,Sell-to Customer No
COLUMN,3,Bill-to Customer No
COLUMN,4,Bill-to Name
COLUMN,5,Bill-to Name 2
COLUMN,6,Order Date
COLUMN,7,Location Code
COLUMN,8,Department Code
COLUMN,9,Make Code
COLUMN,10,Customer Posting Group
COLUMN,11,Salesperson Code
COLUMN,12,Order Class
COLUMN,13,Sell-to Customer Name
COLUMN,14,Sell-to Customer Name 2
COLUMN,15,Document Date
COLUMN,16,No Series
COLUMN,17,Vin
COLUMN,18,Mileage
COLUMN,19,Model Code
COLUMN,20,Model No
COLUMN,21,Prod Year
COLUMN,22,Model_ori
COLUMN,23,Service Advisor No Pick-up
COLUMN,24,Service Advisor No
COLUMN,25,Hauptbetrieb
COLUMN,26,Standort_Department
COLUMN,27,Umsatzart
COLUMN,28,Marke
COLUMN,29,Customer Group Code
COLUMN,30,Kundenart_ori
COLUMN,31,Fabrikat
COLUMN,32,Model
COLUMN,33,Fahrzeug
COLUMN,34,Cust_No
COLUMN,35,Cust_Name
COLUMN,36,Kunde
COLUMN,37,Cust_Group_Description
COLUMN,38,Kundenart
COLUMN,39,Order Number
COLUMN,40,Auftragsdatum
COLUMN,41,DG_1
COLUMN,42,Standort
COLUMN,43,Order Number_Rg_Ausg
COLUMN,44,First Name
COLUMN,45,Last Name
COLUMN,46,Serviceberater
COLUMN,47,Auftragsart
COLUMN,48,Auftragsart_1
COLUMN,49,Serv.beratung am FZG
COLUMN,50,Arbeitsgang
COLUMN,51,DG_2
COLUMN,52,Durchgänge eröffnet
COLUMN,53,offene Aufträge
COLUMN,54,offene Aufträge älter 20 Tage
COLUMN,55,Resource Group No
COLUMN,56,SB_Gruppe