COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_CARLO
DATASOURCENAME,C:\GlobalCube\System\IQD\nw\nw_ae_archiv.imr
TITLE,nw_ae_archiv.imr
BEGIN SQL
select c1 as c1,
	   c2 as c2,
	   c3 as c3,
	   c4 as c4,
	   c5 as c5,
	   c6 as c6,
	   c7 as c7,
	   c8 as c8,
	   c9 as c9,
	   c10 as c10,
	   c11 as c11,
	   c12 as c12,
	   c13 as c13,
	   c14 as c14,
	   c15 as c15,
	   c16 as c16,
	   c17 as c17,
	   c18 as c18,
	   c19 as c19,
	   c20 as c20,
	   c21 as c21,
	   c22 as c22,
	   c23 as c23,
	   c24 as c24,
	   c25 as c25,
	   c26 as c26,
	   c27 as c27,
	   c28 as c28,
	   c29 as c29,
	   c30 as c30,
	   c31 as c31,
	   c32 as c32,
	   c33 as c33,
	   c34 as c34,
	   c35 as c35,
	   c36 as c36,
	   c37 as c37,
	   c38 as c38,
	   c39 as c39,
	   c40 as c40,
	   c41 as c41,
	   c42 as c42,
	   c43 as c43,
	   c44 as c44,
	   c45 as c45,
	   c46 as c46,
	   c47 as c47,
	   c48 as c48,
	   c49 as c49,
	   c50 as c50,
	   c51 as c51,
	   c52 as c52,
	   c53 as c53,
	   c54 as c54,
	   c55 as c55,
	   c56 as c56,
	   c57 as c57,
	   c58 as c58,
	   c59 as c59,
	   c60 as c60,
	   c61 as c61,
	   (c65 / (XCOUNT(c2 for c61))) as c62,
	   c63 as c63,
	   c64 as c64,
	   c65 as c65,
	   XCOUNT(c2 for c61) as c66
from 
(select c1 as c1,
	   c2 as c2,
	   c3 as c3,
	   c4 as c4,
	   c5 as c5,
	   c6 as c6,
	   c7 as c7,
	   c8 as c8,
	   c9 as c9,
	   c10 as c10,
	   c11 as c11,
	   c12 as c12,
	   c13 as c13,
	   c14 as c14,
	   c15 as c15,
	   c16 as c16,
	   c17 as c17,
	   c18 as c18,
	   c19 as c19,
	   c20 as c20,
	   c21 as c21,
	   c22 as c22,
	   c23 as c23,
	   c24 as c24,
	   c25 as c25,
	   c26 as c26,
	   c27 as c27,
	   c28 as c28,
	   c29 as c29,
	   c30 as c30,
	   c31 as c31,
	   c32 as c32,
	   c33 as c33,
	   c34 as c34,
	   c35 as c35,
	   c36 as c36,
	   c37 as c37,
	   c38 as c38,
	   c39 as c39,
	   c40 as c40,
	   c41 as c41,
	   c42 as c42,
	   c43 as c43,
	   c44 as c44,
	   c45 as c45,
	   c46 as c46,
	   c47 as c47,
	   c48 as c48,
	   c49 as c49,
	   c50 as c50,
	   c51 as c51,
	   c52 as c52,
	   c53 as c53,
	   c54 as c54,
	   c55 as c55,
	   c56 as c56,
	   c57 as c57,
	   c58 as c58,
	   c59 as c59,
	   c60 as c60,
	   CASE WHEN (c44 IS NOT NULL) THEN (XMAX(c2 for c29) || ' - ' || c29 || ' - ' || c42 || ' - ' || c57 || ' - ' || c44 || ' / ' || c23 || ' - ' || c67) ELSE (XMAX(c2 for c29) || ' - ' || c29 || ' - ' || c42 || ' - ' || c57 || ' / ' || c23 || ' - ' || (c67)) END as c61,
	   c63 as c63,
	   XMAX(c7 for c29) as c64,
	   c65 as c65
from 
(select c126 as c1,
	   c125 as c2,
	   c124 as c3,
	   c123 as c4,
	   c122 as c5,
	   c121 as c6,
	   c120 as c7,
	   c119 as c8,
	   c118 as c9,
	   c117 as c10,
	   c116 as c11,
	   c115 as c12,
	   c114 as c13,
	   c113 as c14,
	   c112 as c15,
	   c111 as c16,
	   c110 as c17,
	   c109 as c18,
	   c108 as c19,
	   c107 as c20,
	   c106 as c21,
	   c105 as c22,
	   c83 as c23,
	   c104 as c24,
	   c103 as c25,
	   c102 as c26,
	   c101 as c27,
	   c100 as c28,
	   c71 as c29,
	   c99 as c30,
	   c98 as c31,
	   c97 as c32,
	   c96 as c33,
	   c95 as c34,
	   c94 as c35,
	   c93 as c36,
	   c92 as c37,
	   c91 as c38,
	   c90 as c39,
	   c89 as c40,
	   '1' as c41,
	   c88 as c42,
	   c87 as c43,
	   c86 as c44,
	   c86 as c45,
	   c85 as c46,
	   c85 as c47,
	   c84 as c48,
	   c83 as c49,
	   c82 as c50,
	   c81 as c51,
	   c80 as c52,
	   c79 as c53,
	   c78 as c54,
	   c77 as c55,
	   c76 as c56,
	   c74 as c57,
	   c75 as c58,
	   '' as c59,
	   c74 as c60,
	   c73 as c63,
	   1 as c65,
	   c72 as c67,
	   XMAX(c120 for c71) as c68
from 
(select T1."Fahrgestellnummer" as c71,
	   asciiz(extract(YEAR FROM T1."Auftragsdatum"),4) || '-' || asciiz(extract(MONTH FROM T1."Auftragsdatum"),2) || '-' || asciiz(extract(DAY FROM T1."Auftragsdatum"),2) as c72,
	   (substring((upper((CASE WHEN (T1."Modell" IS NOT NULL) THEN ((ucase((od_left(T1."Modell",4))))) ELSE ('NV') END))) from 1 for 4)) as c73,
	   T3."Produktbuchungsgruppe" as c74,
	   CASE WHEN ((CASE WHEN (T3."Produktbuchungsgruppe" LIKE '%VFW%') THEN ('Vorführwagen') WHEN ((od_left(T3."Produktbuchungsgruppe",6)) IN ('F_MIET')) THEN ('Mietwagen') WHEN (T3."Produktbuchungsgruppe" LIKE '%GWD%') THEN ('GW diffb.') WHEN (T3."Produktbuchungsgruppe" LIKE '%GWR%') THEN ('GW regelb.') ELSE ('Neuwagen') END) IN ('GW regelb.','GW diffb.')) THEN ('Gebrauchtwagen') WHEN ((CASE WHEN (T3."Produktbuchungsgruppe" LIKE '%VFW%') THEN ('Vorführwagen') WHEN ((od_left(T3."Produktbuchungsgruppe",6)) IN ('F_MIET')) THEN ('Mietwagen') WHEN (T3."Produktbuchungsgruppe" LIKE '%GWD%') THEN ('GW diffb.') WHEN (T3."Produktbuchungsgruppe" LIKE '%GWR%') THEN ('GW regelb.') ELSE ('Neuwagen') END) IN ('Vorführwagen','Neuwagen','Mietwagen')) THEN ('Neuwagen') ELSE null END as c75,
	   CASE WHEN (T3."Produktbuchungsgruppe" LIKE '%VFW%') THEN ('Vorführwagen') WHEN ((od_left(T3."Produktbuchungsgruppe",6)) IN ('F_MIET')) THEN ('Mietwagen') WHEN (T3."Produktbuchungsgruppe" LIKE '%GWD%') THEN ('GW diffb.') WHEN (T3."Produktbuchungsgruppe" LIKE '%GWR%') THEN ('GW regelb.') ELSE ('Neuwagen') END as c76,
	   T3."Lagerbuchungsgruppe" as c77,
	   T3."Anzahl Vorbesitzer" as c78,
	   T3."Erstzulassung" as c79,
	   T3."Statistikgruppe" as c80,
	   T3."Fahrzeugstatus" as c81,
	   CASE WHEN (T1."Fahrgestellnummer" IS NOT NULL) THEN (T1."Nr_" || ' - ' || T1."Fahrgestellnummer" || ' - ' || T1."Verk_ an Name") ELSE (T1."Nr_" || ' - ' || T1."Verk_ an Name") END as c82,
	   T1."Verk_ an Name" as c83,
	   CASE WHEN (T1."Modell" IS NOT NULL) THEN ((ucase((od_left(T1."Modell",4))))) ELSE ('NV') END as c84,
	   T3."Markencode" as c85,
	   T2."Name" as c86,
	   T2."Code" as c87,
	   CASE WHEN (T1."Lagerortcode" IN ('00HB','01F1')) THEN ('00HB') ELSE (T1."Lagerortcode") END as c88,
	   T1."Vermittlung" as c89,
	   T1."Finanzierung_Leasing" as c90,
	   T1."Fahrzeug VK-Preisdatum" as c91,
	   T1."Haupt Lieferdatum" as c92,
	   T1."Letztes Lieferdatum" as c93,
	   T1."Erstes Lieferdatum" as c94,
	   T1."Bestellart" as c95,
	   T1."Kundenbestätigungdatum" as c96,
	   T1."Auftragsart" as c97,
	   T1."Modell" as c98,
	   T1."Amtliches Kennzeichen" as c99,
	   T1."Archivierungsgrund" as c100,
	   T1."Arch_ durch Benutzer" as c101,
	   T1."Archivierungsdatum" as c102,
	   T1."Belegdatum" as c103,
	   T1."Verk_ an Name 2" as c104,
	   T1."Geschäftsbuchungsgruppe" as c105,
	   T1."Sammelrechnung" as c106,
	   T1."Letzte Buchungsnr_" as c107,
	   T1."Buchungsnr_" as c108,
	   T1."Rechnung" as c109,
	   T1."Auftragsgruppe" as c110,
	   T1."Verkäufercode" as c111,
	   T1."Mengenrabatt zulassen" as c112,
	   T1."VK-Preise inkl_ MWSt" as c113,
	   T1."Debitorenbuchungsgruppe" as c114,
	   T1."Markencode" as c115,
	   T1."Kostenstellencode" as c116,
	   T1."Lagerortcode" as c117,
	   T1."Lieferdatum" as c118,
	   T1."Buchungsdatum" as c119,
	   T1."Auftragsdatum" as c120,
	   T1."Rech_ an Name 2" as c121,
	   T1."Rech_ an Name" as c122,
	   T1."Rech_ an Deb_-Nr_" as c123,
	   T1."Verk_ an Deb_-Nr_" as c124,
	   T1."Nr_" as c125,
	   T1."Belegart" as c126
from (("CARLO"."import"."Archiv_Verkaufskopf" T1 
left outer join "CARLO"."import"."Verkaeufer_Einkaeufer" T2 on T1."Verkäufercode" = T2."Code") 
left outer join "CARLO"."import"."Fahrzeug" T3 on T3."Fahrgestellnummer" = T1."Fahrgestellnummer")
where (((((T1."Belegart" = 1) and (T1."Nr_" LIKE '%FVAN%')) and (T1."Auftragsdatum" >= TIMESTAMP '2017-01-01 00:00:00.000')) and (T1."Archivierungsgrund" <> 1)) and (T1."Lieferdatum" <> TIMESTAMP '1753-01-01 00:00:00.000'))
) D3
) D1
where (c7 = c68)
) D2
order by c29 asc

END SQL
COLUMN,0,Belegart
COLUMN,1,Nr
COLUMN,2,Verk An Deb -nr
COLUMN,3,Rech An Deb -nr
COLUMN,4,Rech An Name
COLUMN,5,Rech An Name 2
COLUMN,6,Auftragsdatum
COLUMN,7,Buchungsdatum
COLUMN,8,Lieferdatum
COLUMN,9,Lagerortcode
COLUMN,10,Kostenstellencode
COLUMN,11,Markencode
COLUMN,12,Debitorenbuchungsgruppe
COLUMN,13,Vk-preise Inkl Mwst
COLUMN,14,Mengenrabatt Zulassen
COLUMN,15,Verkäufercode
COLUMN,16,Auftragsgruppe
COLUMN,17,Rechnung
COLUMN,18,Buchungsnr
COLUMN,19,Letzte Buchungsnr
COLUMN,20,Sammelrechnung
COLUMN,21,Geschäftsbuchungsgruppe
COLUMN,22,Verk An Name
COLUMN,23,Verk An Name 2
COLUMN,24,Belegdatum
COLUMN,25,Archivierungsdatum
COLUMN,26,Arch Durch Benutzer
COLUMN,27,Archivierungsgrund
COLUMN,28,Fahrgestellnummer
COLUMN,29,Amtliches Kennzeichen
COLUMN,30,Modell
COLUMN,31,Auftragsart
COLUMN,32,Kundenbestätigungdatum
COLUMN,33,Bestellart
COLUMN,34,Erstes Lieferdatum
COLUMN,35,Letztes Lieferdatum
COLUMN,36,Haupt Lieferdatum
COLUMN,37,Fahrzeug Vk-preisdatum
COLUMN,38,Finanzierung Leasing
COLUMN,39,Vermittlung
COLUMN,40,Hauptbetrieb
COLUMN,41,Standort
COLUMN,42,Code_verkäufer_einkäufer
COLUMN,43,Name_verkäufer_einkäufer
COLUMN,44,Verkäufer
COLUMN,45,Markencode_FZG
COLUMN,46,Fabrikat
COLUMN,47,Model
COLUMN,48,Kunde
COLUMN,49,FZG
COLUMN,50,Fahrzeugstatus
COLUMN,51,Statistikgruppe
COLUMN,52,Erstzulassung
COLUMN,53,Anzahl Vorbesitzer
COLUMN,54,Lagerbuchungsgruppe
COLUMN,55,Fahrzeugtyp
COLUMN,56,Produktbuchungsgruppe
COLUMN,57,Fahrzeugart
COLUMN,58,Kundenart
COLUMN,59,Fahrzeugtyp_1
COLUMN,60,FZG_Detail
COLUMN,61,Menge
COLUMN,62,Fahrzeugtyp_1_neu
COLUMN,63,Maximum_Auftragsdatum
COLUMN,64,Menge_1
COLUMN,65,Menge_2