COGNOS QUERY
STRUCTURE,1,1
DATABASE,ARIntelligence
DATASOURCENAME,C:\GlobalCube\SYSTEM\ARIntelligence\IQD\service\Service_Eroeffnung_oA.imr
TITLE,Service_Eroeffnung_oA.imr
BEGIN SQL
select c107 as c1,
	   c106 as c2,
	   c105 as c3,
	   c104 as c4,
	   c103 as c5,
	   c102 as c6,
	   c73 as c7,
	   c101 as c8,
	   c100 as c9,
	   c99 as c10,
	   c98 as c11,
	   c97 as c12,
	   c96 as c13,
	   c95 as c14,
	   c94 as c15,
	   c93 as c16,
	   c92 as c17,
	   c91 as c18,
	   c90 as c19,
	   c89 as c20,
	   c88 as c21,
	   c87 as c22,
	   c80 as c23,
	   c86 as c24,
	   c85 as c25,
	   '1' as c26,
	   c84 as c27,
	   '' as c28,
	   c83 as c29,
	   c82 as c30,
	   c82 as c31,
	   c81 as c32,
	   c80 as c33,
	   c79 as c34,
	   c78 as c35,
	   c77 as c36,
	   c76 as c37,
	   c75 as c38,
	   c74 as c39,
	   c62 as c40,
	   c73 as c41,
	   1 as c42,
	   c72 as c43,
	   '' as c44,
	   c71 as c45,
	   c70 as c46,
	   c69 as c47,
	   c68 as c48,
	   c67 as c49,
	   c66 as c50,
	   '' as c51,
	   XCOUNT(c106 for c62) as c52,
	   1 / (XCOUNT(c106 for c62)) as c53,
	   (1 / (XCOUNT(c106 for c62))) as c54,
	   CASE WHEN ((c65) > 20) THEN (((1 / (XCOUNT(c106 for c62))))) ELSE (0) END as c55,
	   (@CURRENT_DATE) - INTERVAL '001 00:00:00.000' as c56,
	   c64 as c57,
	   c63 as c58
from 
(select (T1."No_" || ' - ' || T2."Name") as c62,
	   CASE WHEN (T6."Resource Group No_" = 'SB') THEN ('SB') ELSE ('Rest') END as c63,
	   T6."Resource Group No_" as c64,
	   extract(DAY FROM (now()) - T1."Order Date") as c65,
	   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 c66,
	   T1."Gen_ Prod_ Posting Group" as c67,
	   T1."Service Posting Group" as c68,
	   T4."First Name" || ' ' || T4."Last Name" as c69,
	   T4."Last Name" as c70,
	   T4."First Name" as c71,
	   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 c72,
	   T1."Order Date" as c73,
	   CASE WHEN (T2."No_" LIKE 'INT%') THEN ('Intern') ELSE (T3."Description") END as c74,
	   T3."Description" as c75,
	   T2."No_" || ' - ' || T2."Name" as c76,
	   T2."Name" as c77,
	   T2."No_" as c78,
	   T1."VIN" || ' - ' || T1."Model" as c79,
	   T1."Model" as c80,
	   CASE WHEN (T1."Make Code" IN ('BMW','BMW-MINI')) THEN (T1."Make Code") ELSE ('Fremdfabrikat') END as c81,
	   T2."Customer Group Code" as c82,
	   CASE WHEN (T1."Make Code" IN ('BMW-MINI')) THEN (T1."Make Code") ELSE ('BMW') END as c83,
	   (od_left(T1."Department Code",2)) as c84,
	   T1."Service Advisor No_" as c85,
	   T1."Service Advisor No__Pick-Up" as c86,
	   T1."Prod_ Year" as c87,
	   T1."Model No_" as c88,
	   T1."Model Code" as c89,
	   T1."Mileage" as c90,
	   T1."VIN" as c91,
	   T1."No_ Series" as c92,
	   T1."Document Date" as c93,
	   T1."Sell-to Customer Name 2" as c94,
	   T1."Sell-to Customer Name" as c95,
	   T1."Order Class" as c96,
	   T1."Salesperson Code" as c97,
	   T1."Customer Posting Group" as c98,
	   T1."Make Code" as c99,
	   T1."Department Code" as c100,
	   T1."Location Code" as c101,
	   T1."Bill-to Name 2" as c102,
	   T1."Bill-to Name" as c103,
	   T1."Bill-to Customer No_" as c104,
	   T1."Sell-to Customer No_" as c105,
	   T1."No_" as c106,
	   T1."Document Type" as c107
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) and (not T1."No_" LIKE 'VPL%'))
) 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,Invoice Date
COLUMN,56,Resource Group No
COLUMN,57,SB_Gruppe