COGNOS QUERY
STRUCTURE,1,1
DATABASE,Navision2
DATASOURCENAME,C:\gaps_bmw\Portal\System\IQD\Service\Service_Eröffnung.imr
TITLE,Service_Eröffnung.imr
BEGIN SQL
select c108 as c1,
	   c107 as c2,
	   c106 as c3,
	   c105 as c4,
	   c104 as c5,
	   c103 as c6,
	   c73 as c7,
	   c102 as c8,
	   c101 as c9,
	   c100 as c10,
	   c99 as c11,
	   c98 as c12,
	   c97 as c13,
	   c96 as c14,
	   c95 as c15,
	   c94 as c16,
	   c93 as c17,
	   c92 as c18,
	   c91 as c19,
	   c90 as c20,
	   c89 as c21,
	   c88 as c22,
	   c80 as c23,
	   c87 as c24,
	   c86 as c25,
	   c85 as c26,
	   '1' as c27,
	   c84 as c28,
	   '' as c29,
	   c83 as c30,
	   c82 as c31,
	   c82 as c32,
	   c81 as c33,
	   c80 as c34,
	   c79 as c35,
	   c78 as c36,
	   c77 as c37,
	   c76 as c38,
	   c75 as c39,
	   c74 as c40,
	   c62 as c41,
	   c73 as c42,
	   1 as c43,
	   c72 as c44,
	   '' as c45,
	   c71 as c46,
	   c70 as c47,
	   c69 as c48,
	   c68 as c49,
	   c67 as c50,
	   c66 as c51,
	   '' as c52,
	   XCOUNT(c107 for c62) as c53,
	   1 / (XCOUNT(c107 for c62)) as c54,
	   (1 / (XCOUNT(c107 for c62))) as c55,
	   CASE WHEN ((c65) > 20) THEN (((1 / (XCOUNT(c107 for c62))))) ELSE (0) END 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."Service Advisor No__Scheduling" as c87,
	   T1."Prod_ Year" as c88,
	   T1."Model No_" as c89,
	   T1."Model Code" as c90,
	   T1."Mileage" as c91,
	   T1."VIN" as c92,
	   T1."No_ Series" as c93,
	   T1."Document Date" as c94,
	   T1."Sell-to Customer Name 2" as c95,
	   T1."Sell-to Customer Name" as c96,
	   T1."Order Class" as c97,
	   T1."Salesperson Code" as c98,
	   T1."Customer Posting Group" as c99,
	   T1."Make Code" as c100,
	   T1."Department Code" as c101,
	   T1."Location Code" as c102,
	   T1."Bill-to Name 2" as c103,
	   T1."Bill-to Name" as c104,
	   T1."Bill-to Customer No_" as c105,
	   T1."Sell-to Customer No_" as c106,
	   T1."No_" as c107,
	   T1."Document Type" as c108
from "DMS1"."dbo"."Automag GmbH$Service Line" T5,
	(((("DMS1"."dbo"."Automag GmbH$Service Header" T1 left outer join "DMS1"."dbo"."Automag GmbH$Customer" T2 on T2."No_" = T1."Sell-to Customer No_") left outer join "DMS1"."dbo"."Automag GmbH$Customer Group" T3 on T2."Customer Group Code" = T3."Code") left outer join "DMS1"."dbo"."Automag GmbH$Employee" T4 on T4."No_" = T1."Service Advisor No_") left outer join "DMS1"."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 Scheduling
COLUMN,24,Service Advisor No Pick-up
COLUMN,25,Service Advisor No
COLUMN,26,Hauptbetrieb
COLUMN,27,Standort_Department
COLUMN,28,Umsatzart
COLUMN,29,Marke
COLUMN,30,Customer Group Code
COLUMN,31,Kundenart_ori
COLUMN,32,Fabrikat
COLUMN,33,Model
COLUMN,34,Fahrzeug
COLUMN,35,Cust_No
COLUMN,36,Cust_Name
COLUMN,37,Kunde
COLUMN,38,Cust_Group_Description
COLUMN,39,Kundenart
COLUMN,40,Order Number
COLUMN,41,Auftragsdatum
COLUMN,42,DG_1
COLUMN,43,Standort
COLUMN,44,Order Number_Rg_Ausg
COLUMN,45,First Name
COLUMN,46,Last Name
COLUMN,47,Serviceberater
COLUMN,48,Auftragsart
COLUMN,49,Auftragsart_1
COLUMN,50,Serv.beratung am FZG
COLUMN,51,Arbeitsgang
COLUMN,52,DG_2
COLUMN,53,Durchgänge eröffnet
COLUMN,54,offene Aufträge
COLUMN,55,offene Aufträge älter 20 Tage
COLUMN,56,Resource Group No
COLUMN,57,SB_Gruppe