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