C11/System/ARI/IQD/Service/SRD_Teile_neu_3.sql

SELECT "Register No",
	"Entry No",
	"Srd Item No",
	"Dealer No",
	"Transaction Type",
	"Transaction Type Specification",
	"Xtimestamp",
	"Current Qty On-hand",
	"Open Order Quantity",
	"Average Cost",
	"Customer Backorder",
	"Enable For Requisition",
	"Area",
	"Document Type",
	"Document No",
	"Posting Date",
	"Quantity",
	"Gross Price",
	"Unit Price",
	"Customer Order No",
	"Customer Text",
	"Customer No",
	"Vin",
	"Mileage",
	"Salesperson Code",
	"Item No",
	'1' AS "Hauptbetrieb",
	"Standort",
	"Transaction Type Specification" AS "BA Spezifikation",
	"First Name",
	"Last Name",
	"Verk�ufer",
	"No_Customer",
	"Name_Customer",
	"Customer Group Code",
	"Customer Posting Group",
	"Location Code_Customer",
	"Customer Type",
	"Umsatzart",
	"Kundenart",
	"Kunde",
	"Bmw Parts Type",
	"Item Group Code",
	"Teileart-Gruppe",
	"Artikelgruppe-Gruppe",
	"Item Group Code" AS "Artikelgruppe",
	"Teileart",
	"Menge",
	"VK",
	"EK",
	"Posting Date" AS "Invoice Date",
	"Verkauf / Einkauf",
	"Bewegungsart",
	"Umsatz_alt",
	"Einsatz_alt",
	"Bewegungsart Detail",
	"Description",
	"Betrag Lagerzugang_alt",
	"Menge VK_alt",
	"Menge Lagerzugang_alt",
	"Description_Artikel",
	"Teil",
	"Abteilung",
	"Beleg_ori",
	"Beleg",
	"Make Code",
	"Marke",
	"Amount",
	"Adjusted Cost",
	"Cost Posted To G L",
	"Einsatz_neu",
	COUNT("Srd Item No") OVER (PARTITION BY c85) AS "Anzahl Datens�tze",
	("Umsatz_alt") / (COUNT("Srd Item No") OVER (PARTITION BY c85)) AS "Umsatz",
	CASE 
		WHEN (("Einsatz_neu") IS NOT NULL)
			THEN (("Einsatz_neu") / (COUNT("Srd Item No") OVER (PARTITION BY c85)))
		ELSE (0)
		END AS "Einsatz",
	("Menge VK_alt") / (COUNT("Srd Item No") OVER (PARTITION BY c85)) AS "Menge VK",
	("Menge Lagerzugang_alt") / (COUNT("Srd Item No") OVER (PARTITION BY c85)) AS "Menge Lagerzugang",
	("Betrag Lagerzugang_alt") / (COUNT("Srd Item No") OVER (PARTITION BY c85)) AS "Betrag Lagerzugang",
	"Extra Code 3",
	"Inland/Export",
	"Artikelgruppe-Gruppe numerisch",
	"4 Stellen Document No",
	"Beleg_Tagesbericht"
FROM (
	SELECT ((((T1."Entry No_"))) + T1."Document No_") AS c85,
		CASE 
			WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 3)
				THEN (
						(
							CASE 
								WHEN (T3."No_" IS NOT NULL)
									THEN (
											T1."Document No_" + ' / ' + (
												CASE 
													WHEN (T3."No_" IS NOT NULL)
														THEN (T3."No_" + ' - ' + T3."Name")
													ELSE NULL
													END
												)
											)
								ELSE (T1."Document No_")
								END
							)
						)
			ELSE NULL
			END AS "Beleg_Tagesbericht",
		(left(T1."Document No_", 4)) AS "4 Stellen Document No",
		CASE 
			WHEN (T4."Item Group Code" BETWEEN '00' AND '22')
				THEN ('Artikelgruppe 0 - 22')
			WHEN (T4."Item Group Code" BETWEEN '23' AND '33')
				THEN ('Artikelgruppe 23 - 33')
			WHEN (T4."Item Group Code" BETWEEN '34' AND '99')
				THEN ('Artikelgruppe 34 - 99')
			ELSE ('Teileart fehlt/Rest')
			END AS "Artikelgruppe-Gruppe numerisch",
		CASE 
			WHEN (T3."Extra Code 3" = 'EXPORT')
				THEN ('Anteil Export')
			ELSE ('Anteil Inland')
			END AS "Inland/Export",
		T3."Extra Code 3" AS "Extra Code 3",
		CASE 
			WHEN (
					(
						CASE 
							WHEN (T1."Transaction Type" IN ('LC'))
								THEN ('Verkauf')
							ELSE ('Einkauf')
							END
						) = 'Einkauf'
					)
				THEN (((convert(FLOAT, T1."Quantity"))) * ((convert(FLOAT, T1."Unit Price"))))
			ELSE (0)
			END AS "Betrag Lagerzugang_alt",
		CASE 
			WHEN (
					(
						CASE 
							WHEN (T1."Transaction Type" IN ('LC'))
								THEN ('Verkauf')
							ELSE ('Einkauf')
							END
						) = 'Einkauf'
					)
				THEN (((convert(FLOAT, T1."Quantity"))))
			ELSE (0)
			END AS "Menge Lagerzugang_alt",
		CASE 
			WHEN (
					(
						CASE 
							WHEN (T1."Transaction Type" IN ('LC'))
								THEN ('Verkauf')
							ELSE ('Einkauf')
							END
						) = 'Verkauf'
					)
				THEN (((convert(FLOAT, T1."Quantity"))))
			ELSE (0)
			END AS "Menge VK_alt",
		CASE 
			WHEN (
					(
						(
							CASE 
								WHEN (
										(
											CASE 
												WHEN (T1."Transaction Type" IN ('LC'))
													THEN ('Verkauf')
												ELSE ('Einkauf')
												END
											) = 'Verkauf'
										)
									THEN (((convert(FLOAT, T1."Quantity"))) * ((convert(FLOAT, T1."Average Cost"))))
								ELSE (0)
								END
							) < 0
						)
					AND (((convert(FLOAT, T6."Adjusted Cost"))) > 0)
					)
				THEN (((convert(FLOAT, T6."Adjusted Cost"))) * - 1)
			WHEN (
					(
						(
							CASE 
								WHEN (
										(
											CASE 
												WHEN (T1."Transaction Type" IN ('LC'))
													THEN ('Verkauf')
												ELSE ('Einkauf')
												END
											) = 'Verkauf'
										)
									THEN (((convert(FLOAT, T1."Quantity"))) * ((convert(FLOAT, T1."Average Cost"))))
								ELSE (0)
								END
							) > 0
						)
					AND (((convert(FLOAT, T6."Adjusted Cost"))) < 0)
					)
				THEN (((convert(FLOAT, T6."Adjusted Cost"))) * - 1)
			ELSE (((convert(FLOAT, T6."Adjusted Cost"))))
			END AS "Einsatz_neu",
		CASE 
			WHEN (
					(
						CASE 
							WHEN (T1."Transaction Type" IN ('LC'))
								THEN ('Verkauf')
							ELSE ('Einkauf')
							END
						) = 'Verkauf'
					)
				THEN (((convert(FLOAT, T1."Quantity"))) * ((convert(FLOAT, T1."Unit Price"))))
			ELSE (0)
			END AS "Umsatz_alt",
		(convert(FLOAT, T6."Cost Posted to G_L")) AS "Cost Posted To G L",
		(convert(FLOAT, T6."Adjusted Cost")) AS "Adjusted Cost",
		(convert(FLOAT, T6."Amount")) AS "Amount",
		CASE 
			WHEN (T4."Make Code" IN ('BMW', 'BMW-C1', 'BMW-MOT', 'BMWI'))
				THEN ('BMW')
			WHEN (T4."Make Code" IN ('BMW-MINI'))
				THEN ('MINI')
			ELSE ('Andere')
			END AS "Marke",
		T4."Make Code" AS "Make Code",
		CASE 
			WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 180)
				THEN (
						(
							CASE 
								WHEN (T3."No_" IS NOT NULL)
									THEN (
											T1."Document No_" + ' / ' + (
												CASE 
													WHEN (T3."No_" IS NOT NULL)
														THEN (T3."No_" + ' - ' + T3."Name")
													ELSE NULL
													END
												)
											)
								ELSE (T1."Document No_")
								END
							)
						)
			ELSE NULL
			END AS "Beleg",
		CASE 
			WHEN (T3."No_" IS NOT NULL)
				THEN (
						T1."Document No_" + ' / ' + (
							CASE 
								WHEN (T3."No_" IS NOT NULL)
									THEN (T3."No_" + ' - ' + T3."Name")
								ELSE NULL
								END
							)
						)
			ELSE (T1."Document No_")
			END AS "Beleg_ori",
		CASE 
			WHEN (T1."Area" = 1)
				THEN ('T & Z')
			WHEN (T1."Area" = 4)
				THEN ('Service')
			WHEN (T1."Area" = 0)
				THEN ('Sonstige')
			ELSE NULL
			END AS "Abteilung",
		T1."Item No_" + ' - ' + T4."Description" AS "Teil",
		T4."Description" AS "Description_Artikel",
		T5."Description" AS "Description",
		T1."Transaction Type Specification" + ' - ' + T5."Description" AS "Bewegungsart Detail",
		CASE 
			WHEN (
					(
						CASE 
							WHEN (T1."Transaction Type" IN ('LC'))
								THEN ('Verkauf')
							ELSE ('Einkauf')
							END
						) = 'Verkauf'
					)
				THEN (((convert(FLOAT, T1."Quantity"))) * ((convert(FLOAT, T1."Average Cost"))))
			ELSE (0)
			END AS "Einsatz_alt",
		CASE 
			WHEN (T1."Transaction Type" = 'BA')
				THEN ('BA - Bestellung')
			WHEN (T1."Transaction Type" = 'RA')
				THEN ('RA - R�ckgaben')
			WHEN (T1."Transaction Type" = 'LB')
				THEN ('LB - Lagerzug�nge maschinell')
			WHEN (T1."Transaction Type" = 'LC')
				THEN ('LC - Lagerabg�nge')
			WHEN (T1."Transaction Type" = 'LA')
				THEN ('LA - Lagerzug�nge manuell')
			ELSE NULL
			END AS "Bewegungsart",
		CASE 
			WHEN (T1."Transaction Type" IN ('LC'))
				THEN ('Verkauf')
			ELSE ('Einkauf')
			END AS "Verkauf / Einkauf",
		T1."Posting Date" AS "Posting Date",
		(convert(FLOAT, T1."Average Cost")) AS "EK",
		(convert(FLOAT, T1."Unit Price")) AS "VK",
		(convert(FLOAT, T1."Quantity")) AS "Menge",
		CASE 
			WHEN (T4."Parts Category" = '1')
				THEN ('1 - Teile')
			WHEN (T4."Parts Category" = '2')
				THEN ('2 - Tauschteile')
			WHEN (T4."Parts Category" = '3')
				THEN ('3 - Nachr�stteile')
			WHEN (T4."Parts Category" = '4')
				THEN ('4 - ')
			WHEN (T4."Parts Category" = '5')
				THEN ('5 - R�der, Felgen')
			WHEN (T4."Parts Category" = '6')
				THEN ('6 - ')
			WHEN (T4."Parts Category" = '7')
				THEN ('7 - Accessoires')
			WHEN (T4."Parts Category" = '8')
				THEN ('8 - Reifen')
			WHEN (T4."Parts Category" = '9')
				THEN ('9 - �le, Sonstiges')
			ELSE NULL
			END AS "Teileart",
		T4."Item Group Code" AS "Item Group Code",
		CASE 
			WHEN (T4."Item Group Code" IN ('B', 'C', 'A'))
				THEN ('Artikelgruppe A - C')
			WHEN (T4."Item Group Code" IN ('E', 'K', 'H', 'F', 'I', 'G', 'D', 'J'))
				THEN ('Artikelgruppe D - K')
			WHEN (T4."Item Group Code" BETWEEN '23' AND '33')
				THEN ('Artikelgruppe D + E neu')
			WHEN (T4."Item Group Code" BETWEEN '34' AND '99')
				THEN ('Artikelgruppe F - K neu')
			ELSE ('Teileart fehlt/Rest')
			END AS "Artikelgruppe-Gruppe",
		CASE 
			WHEN (T4."Parts Category" IN ('1', '2'))
				THEN ('Teileart 1 - 2')
			WHEN (T4."Parts Category" IN ('3', '4', '5', '6', '7', '8', '9'))
				THEN ('Teileart 3 - 9')
			ELSE ('Teileart fehlt')
			END AS "Teileart-Gruppe",
		T4."Parts Category" AS "Bmw Parts Type",
		CASE 
			WHEN (T3."No_" IS NOT NULL)
				THEN (T3."No_" + ' - ' + T3."Name")
			ELSE NULL
			END AS "Kunde",
		T3."Gen_ Bus_ Posting Group" AS "Kundenart",
		CASE 
			WHEN (T3."No_" LIKE 'I%')
				THEN ('Intern')
			WHEN (T3."No_" LIKE 'G%')
				THEN ('GWL')
			WHEN (
					(
						(NOT T3."No_" LIKE 'I%')
						AND (NOT T3."No_" LIKE 'G%')
						)
					AND (T3."No_" IS NOT NULL)
					)
				THEN ('Extern')
			ELSE NULL
			END AS "Umsatzart",
		T3."Customer Type" AS "Customer Type",
		T3."Location Code" AS "Location Code_Customer",
		T3."Customer Posting Group" AS "Customer Posting Group",
		T3."Customer Group Code" AS "Customer Group Code",
		T3."Name" AS "Name_Customer",
		T3."No_" AS "No_Customer",
		T2."First Name" + ' ' + T2."Last Name" AS "Verk�ufer",
		T2."Last Name" AS "Last Name",
		T2."First Name" AS "First Name",
		T1."Transaction Type Specification" AS "Transaction Type Specification",
		CASE 
			WHEN (T1."Dealer No_" IN ('00357', '29682', '22075', '28303'))
				THEN ('10')
			WHEN (T1."Dealer No_" IN ('00557', '29619', '40119'))
				THEN ('20')
			ELSE NULL
			END AS "Standort",
		T1."Item No_" AS "Item No",
		T1."Salesperson Code" AS "Salesperson Code",
		T1."Mileage" AS "Mileage",
		T1."VIN" AS "Vin",
		T1."Customer No_" AS "Customer No",
		T1."Customer Text" AS "Customer Text",
		T1."Customer Order No_" AS "Customer Order No",
		T1."Unit Price" AS "Unit Price",
		T1."Gross Price" AS "Gross Price",
		T1."Quantity" AS "Quantity",
		T1."Document No_" AS "Document No",
		T1."Document Type" AS "Document Type",
		T1."Area" AS "Area",
		T1."Enable for Requisition" AS "Enable For Requisition",
		T1."Customer Backorder" AS "Customer Backorder",
		T1."Average Cost" AS "Average Cost",
		T1."Open Order Quantity" AS "Open Order Quantity",
		T1."Current Qty_ On-Hand" AS "Current Qty On-hand",
		T1."xTimestamp" AS "Xtimestamp",
		T1."Transaction Type" AS "Transaction Type",
		T1."Dealer No_" AS "Dealer No",
		T1."SRD Item No_" AS "Srd Item No",
		T1."Entry No_" AS "Entry No",
		T1."Register No_" AS "Register No"
	FROM (
		(
			(
				(
					(
						"Vogl7x"."dbo"."BMW AH Vogl$BMW SRD Transaction" T1 LEFT JOIN "Vogl7x"."dbo"."BMW AH Vogl$Employee" T2 ON T1."Salesperson Code" = T2."No_"
						) LEFT JOIN "Vogl7x"."dbo"."BMW AH Vogl$Customer" T3 ON T1."Customer No_" = T3."No_"
					) LEFT JOIN "Vogl7x"."dbo"."BMW AH Vogl$Item" T4 ON T1."Item No_" = T4."No_"
				) LEFT JOIN "Vogl7x"."dbo"."BMW AH Vogl$BMW SRD Movement Type" T5 ON (T1."Transaction Type" = T5."Movement Type Code")
				AND (T1."Transaction Type Specification" = T5."Movement Type Specification")
			) LEFT JOIN "Vogl7x"."dbo"."BMW AH Vogl$Value Entry" T6 ON (
				(T6."Item No_" = T1."SRD Item No_")
				AND (T6."Document No_" = T1."Document No_")
				)
			AND (T6."Posting Date" = T1."Posting Date")
		)
	WHERE (
			(T1."Posting Date" >= convert(DATETIME, '2020-01-01 00:00:00.000'))
			AND (NOT T1."Document No_" LIKE 'FILAG%')
			)
	) D1
	-- order by "Xtimestamp" asc