C11/System/LOCOSOFT/IQD/NW/LOC_Belege_NW_GW_VK.sql

SELECT "Accounting Date" AS "Accounting Date",
	"Document Type" AS "Document Type",
	"Document Number" AS "Document Number",
	"Position In Document" AS "Position In Document",
	(
		day((
				convert(DATETIME, (
						CASE 
							WHEN ((MAX("Invoice_Date_1") OVER (PARTITION BY "Vehicle Reference_berechnet")) IS NULL)
								THEN ("Invoice Date_journal_accountings")
							ELSE ((MAX("Invoice_Date_1") OVER (PARTITION BY "Vehicle Reference_berechnet")))
							END
						))
				) - c142)
		) AS "Customer Number",
	"Nominal Account Number" AS "Nominal Account Number",
	"Is Balanced" AS "Is Balanced",
	"Clearing Number" AS "Clearing Number",
	"Document Date" AS "Document Date",
	"Posted Value" AS "Posted Value",
	"Debit Or Credit" AS "Debit Or Credit",
	"Posted Count" AS "Posted Count",
	"Branch Number" AS "Branch Number",
	"Customer Contra Account" AS "Customer Contra Account",
	"Nominal Contra Account" AS "Nominal Contra Account",
	"Contra Account Text" AS "Contra Account Text",
	"Account Form Page Number" AS "Account Form Page Number",
	"Account Form Page Line" AS "Account Form Page Line",
	"Serial Number Each Month" AS "Serial Number Each Month",
	"Employee Number" AS "Employee Number",
	"Invoice Date_journal_accountings" AS "Invoice Date_journal_accountings",
	"Invoice Number" AS "Invoice Number",
	"Dunning Level" AS "Dunning Level",
	"Last Dunning Date" AS "Last Dunning Date",
	"Journal Page" AS "Journal Page",
	"Journal Line" AS "Journal Line",
	"Cash Discount" AS "Cash Discount",
	"Term Of Payment" AS "Term Of Payment",
	"Posting Text" AS "Posting Text",
	"Vehicle Reference" AS "Vehicle Reference",
	"Vat Id Number" AS "Vat Id Number",
	"Account Statement Number" AS "Account Statement Number",
	"Account Statement Page" AS "Account Statement Page",
	"Vat Key" AS "Vat Key",
	"Days For Cash Discount" AS "Days For Cash Discount",
	"Day Of Actual Accounting" AS "Day Of Actual Accounting",
	"Skr51 Branch" AS "Skr51 Branch",
	"Skr51 Make" AS "Skr51 Make",
	"Skr51 Cost Center" AS "Skr51 Cost Center",
	"Skr51 Sales Channel" AS "Skr51 Sales Channel",
	"Skr51 Cost Unit" AS "Skr51 Cost Unit",
	"Previously Used Account No" AS "Previously Used Account No",
	"Free Form Accounting Text" AS "Free Form Accounting Text",
	"Free Form Document Text" AS "Free Form Document Text",
	"Nom_Account_Is Profit Loss Account" AS "Nom_Account_Is Profit Loss Account",
	"Rechtseinheit" AS "Rechtseinheit",
	"Betrieb" AS "Betrieb",
	"Bookkeep Date" AS "Bookkeep Date",
	"Marke" AS "Marke",
	"Text" AS "Text",
	"Mandant" AS "Mandant",
	"Betrag" AS "Betrag",
	"Konto_mit_Bezeichnung" AS "Konto_mit_Bezeichnung",
	"Menge_2" AS "Menge_2",
	"Stellen Cost Center" AS "Stellen Cost Center",
	"KST" AS "KST",
	"Stellen Sales Channel" AS "Stellen Sales Channel",
	"Absatzkanal" AS "Absatzkanal",
	"Stellen Cost Unit" AS "Stellen Cost Unit",
	"Kostentr�ger_mit_Null" AS "Kostentr�ger_mit_Null",
	"Kostentr�ger" AS "Kostentr�ger",
	"GuV_Bilanz" AS "GuV_Bilanz",
	"Susa" AS "Susa",
	"Acct Nr" AS "Acct Nr",
	"Document Type In Journal" AS "Document Type In Journal",
	"Document Type Description" AS "Document Type Description",
	"FZG_ja_nein" AS "FZG_ja_nein",
	SUM("FZG_ja_nein") OVER (PARTITION BY "Vehicle Reference_berechnet") AS "Summe_FZG_ja_nein",
	CASE 
		WHEN (SUM("FZG_ja_nein") OVER (PARTITION BY "Vehicle Reference_berechnet") > 0)
			THEN ('FZG-Gesch�ft')
		ELSE ('Buchungen ohne FZG-Gesch�ft')
		END AS "FZG_Gesch�ft ja/nein",
	"Invoice Type_Invoices" AS "Invoice Type_Invoices",
	"Invoice Number_Invoices" AS "Invoice Number_Invoices",
	"Invoice Date_Invoices" AS "Invoice Date_Invoices",
	"Is Canceled_Invoices" AS "Is Canceled_Invoices",
	"Vehicle Number_Invoices" AS "Vehicle Number_Invoices",
	MAX("Vehicle Number_Invoices") OVER (PARTITION BY "Vehicle Reference_berechnet") AS "Vehicle_Number_Max",
	"Invoice_Date_1" AS "Invoice_Date_1",
	MAX("Invoice_Date_1") OVER (PARTITION BY "Vehicle Reference_berechnet") AS "Invoice Date_FZG",
	CASE 
		WHEN (MAX("Invoice_Date_1") OVER (PARTITION BY "Vehicle Reference_berechnet") IS NULL)
			THEN ("Invoice Date_journal_accountings")
		ELSE MAX("Invoice_Date_1") OVER (PARTITION BY "Vehicle Reference_berechnet")
		END AS "Invoice Date",
	SUM("Betrag") OVER (PARTITION BY "Vehicle Reference_berechnet") AS "Summe Betrag",
	"Internal Number_Vehicles" AS "Internal Number_Vehicles",
	"Vin_Vehicles" AS "Vin_Vehicles",
	"Dealer Vehicle Type" AS "Dealer Vehicle Type",
	"Fahrzeugtyp" AS "Fahrzeugtyp",
	"Fahrzeugart" AS "Fahrzeugart",
	"Make Number_Vehicles" AS "Make Number_Vehicles",
	"Description_Make" AS "Description_Make",
	"Fabrikat" AS "Fabrikat",
	"Model Code_Models" AS "Model Code_Models",
	"Model_Detail" AS "Model_Detail",
	"Model" AS "Model",
	"Owner Number_Vehicles" AS "Owner Number_Vehicles",
	"Holder Number_Vehicles" AS "Holder Number_Vehicles",
	"First Name_Owner_Customer" AS "First Name_Owner_Customer",
	"Family Name_Owner_Customer" AS "Family Name_Owner_Customer",
	"First Name_Holder_Customer" AS "First Name_Holder_Customer",
	"Family Name_Holder_Customer" AS "Family Name_Holder_Customer",
	"Kunde" AS "Kunde",
	"Sales_Channel_FZG" AS "Sales_Channel_FZG",
	MAX("Sales_Channel_FZG") OVER (PARTITION BY "Vehicle Reference_berechnet") AS "Kundenart_Zahl",
	CASE 
		WHEN (MAX("Sales_Channel_FZG") OVER (PARTITION BY "Vehicle Reference_berechnet") BETWEEN 10 AND 19)
			THEN ('Privater Endkunde')
		WHEN (MAX("Sales_Channel_FZG") OVER (PARTITION BY "Vehicle Reference_berechnet") BETWEEN 20 AND 29)
			THEN ('Gewerbekunde')
		WHEN (MAX("Sales_Channel_FZG") OVER (PARTITION BY "Vehicle Reference_berechnet") BETWEEN 30 AND 39)
			THEN ('Gro�kunde')
		WHEN (MAX("Sales_Channel_FZG") OVER (PARTITION BY "Vehicle Reference_berechnet") BETWEEN 40 AND 49)
			THEN ('Sonderabnehmer')
		WHEN (MAX("Sales_Channel_FZG") OVER (PARTITION BY "Vehicle Reference_berechnet") BETWEEN 50 AND 59)
			THEN ('Wiederverk�ufer')
		ELSE NULL
		END AS "Kundenart",
	"Hauptbetrieb" AS "Hauptbetrieb",
	"Standort_alt" AS "Standort_alt",
	"Salesman Number_Vehicles" AS "Salesman Number_Vehicles",
	"Employee Number_Employees_Salesman" AS "Employee Number_Employees_Salesman",
	"Name_Employees_Salesman" AS "Name_Employees_Salesman",
	"Verk�ufer" AS "Verk�ufer",
	"Standort_FZG_Verkauf" AS "Standort_FZG_Verkauf",
	MAX("Standort_FZG_Verkauf") OVER (PARTITION BY "Vehicle Reference_berechnet") AS "Standort_Zahl",
	"Standort" AS "Standort",
	"FZG" AS "FZG",
	"Konto" AS "Konto",
	"Body Paint Code" AS "Body Paint Code",
	"Body Paint Description" AS "Body Paint Description",
	"Farbe" AS "Farbe",
	"Erl�s FZG" AS "Erl�s FZG",
	"Nachlass" AS "Nachlass",
	"VAK FZG" AS "VAK FZG",
	"Erl�s Zulass." AS "Erl�s Zulass.",
	"VAK Zulass." AS "VAK Zulass.",
	"Erl�s Gar." AS "Erl�s Gar.",
	"VAK Gar." AS "VAK Gar.",
	"Erl�s Nachr./Aufb." AS "Erl�s Nachr./Aufb.",
	"VAK Nachr./Aufb." AS "VAK Nachr./Aufb.",
	"VAK Fertigm." AS "VAK Fertigm.",
	"Erl�s �berf." AS "Erl�s �berf.",
	"VAK �berf." AS "VAK �berf.",
	"Erl�s Sonst._8900" AS "Erl�s Sonst._8900",
	"Erl�s Sonst." AS "Erl�s Sonst.",
	"VAK Sonst." AS "VAK Sonst.",
	"VAK intern" AS "VAK intern",
	"Erl�s Prov." AS "Erl�s Prov.",
	"Boni/VK-Hilfen" AS "Boni/VK-Hilfen",
	"Erl�s ges." AS "Erl�s ges.",
	"VAK ges." AS "VAK ges.",
	"Menge_1" AS "Menge_1",
	COUNT("Accounting Date") OVER (PARTITION BY "Vehicle Reference_berechnet") AS "Menge_2",
	("Menge_1" / COUNT("Accounting Date") OVER (PARTITION BY "Vehicle Reference_berechnet")) AS "Menge",
	"Vehicle Reference_berechnet" AS "Vehicle Reference_berechnet",
	"var. Kosten" AS "var. Kosten",
	"Dealer Vehicle Type_Vehicles" AS "Dealer Vehicle Type_Vehicles",
	"Dealer Vehicle Number_Vehicles" AS "Dealer Vehicle Number_Vehicles"
FROM (
	SELECT "Accounting Date",
		"Document Type",
		"Document Number",
		"Position In Document",
		"Nominal Account Number",
		"Is Balanced",
		"Clearing Number",
		"Document Date",
		"Posted Value",
		"Debit Or Credit",
		"Posted Count",
		"Branch Number",
		"Customer Contra Account",
		"Nominal Contra Account",
		"Contra Account Text",
		"Account Form Page Number",
		"Account Form Page Line",
		"Serial Number Each Month",
		"Employee Number",
		"Invoice Date_journal_accountings",
		"Invoice Number",
		"Dunning Level",
		"Last Dunning Date",
		"Journal Page",
		"Journal Line",
		"Cash Discount",
		"Term Of Payment",
		"Posting Text",
		"Vehicle Reference",
		"Vat Id Number",
		"Account Statement Number",
		"Account Statement Page",
		"Vat Key",
		"Days For Cash Discount",
		"Day Of Actual Accounting",
		"Skr51 Branch",
		"Skr51 Make",
		"Skr51 Cost Center",
		"Skr51 Sales Channel",
		"Skr51 Cost Unit",
		"Previously Used Account No",
		"Free Form Accounting Text",
		"Free Form Document Text",
		"Nom_Account_Is Profit Loss Account",
		"Rechtseinheit",
		"Betrieb",
		"Accounting Date" AS "Bookkeep Date",
		"Marke",
		"Text",
		'1' AS "Mandant",
		"Betrag",
		"Konto_mit_Bezeichnung",
		"Menge_2",
		"Stellen Cost Center",
		"KST",
		"Stellen Sales Channel",
		"Absatzkanal",
		"Stellen Cost Unit",
		"Kostentr�ger_mit_Null",
		"Kostentr�ger",
		"GuV_Bilanz",
		"Susa",
		"Nominal Account Number" AS "Acct Nr",
		"Document Type In Journal",
		"Document Type Description",
		"FZG_ja_nein",
		"Invoice Type_Invoices",
		"Invoice Number_Invoices",
		"Invoice Date_Invoices",
		"Is Canceled_Invoices",
		"Vehicle Number_Invoices",
		"Invoice_Date_1",
		"Internal Number_Vehicles",
		"Vin_Vehicles",
		"Dealer Vehicle Type",
		"Fahrzeugtyp",
		"Fahrzeugart",
		"Make Number_Vehicles",
		"Description_Make",
		"Fabrikat",
		"Model Code_Models",
		"Model_Detail",
		"Model",
		"Owner Number_Vehicles",
		"Holder Number_Vehicles",
		"First Name_Owner_Customer",
		"Family Name_Owner_Customer",
		"First Name_Holder_Customer",
		"Family Name_Holder_Customer",
		"Kunde",
		"Sales_Channel_FZG",
		"Rechtseinheit" AS "Hauptbetrieb",
		"Betrieb" AS "Standort_alt",
		"Salesman Number_Vehicles",
		"Employee Number_Employees_Salesman",
		"Name_Employees_Salesman",
		"Verk�ufer",
		"Standort_FZG_Verkauf",
		"Standort",
		"FZG",
		"Konto_mit_Bezeichnung" AS "Konto",
		"Body Paint Code",
		"Body Paint Description",
		"Body Paint Description" AS "Farbe",
		"Erl�s FZG",
		"Nachlass",
		"VAK FZG",
		"Erl�s Zulass.",
		"VAK Zulass.",
		"Erl�s Gar.",
		"VAK Gar.",
		"Erl�s Nachr./Aufb.",
		"VAK Nachr./Aufb.",
		"VAK Fertigm.",
		"Erl�s �berf.",
		"VAK �berf.",
		"Erl�s Sonst._8900",
		"Erl�s Sonst.",
		"VAK Sonst.",
		"VAK intern",
		"Erl�s Prov.",
		"Boni/VK-Hilfen",
		"Erl�s ges.",
		"VAK ges.",
		1 AS "Menge_1",
		"Vehicle Reference_berechnet",
		"var. Kosten",
		"Dealer Vehicle Type_Vehicles",
		"Dealer Vehicle Number_Vehicles",
		c152 AS c142,
		MAX("Vehicle Number_Invoices") OVER (PARTITION BY "Vehicle Reference_berechnet") AS c143,
		(
			CASE 
				WHEN ((MAX("Invoice_Date_1") OVER (PARTITION BY "Vehicle Reference_berechnet")) IS NULL)
					THEN (("Invoice Date_journal_accountings"))
				ELSE ((MAX("Invoice_Date_1") OVER (PARTITION BY "Vehicle Reference_berechnet")))
				END
			) AS c144,
		(
			CASE 
				WHEN ((SUM("FZG_ja_nein") OVER (PARTITION BY "Vehicle Reference_berechnet")) > 0)
					THEN ('FZG-Gesch�ft')
				ELSE ('Buchungen ohne FZG-Gesch�ft')
				END
			) AS c145
	FROM (
		SELECT (left(T1."vehicle_reference", 7)) + '_' + (right(T1."vehicle_reference", 17)) AS "Vehicle Reference_berechnet",
			(((nconvert((left(T1."invoice_date", 4)) + (substring(T1."invoice_date", 6, 2)) + (substring(T1."invoice_date", 9, 2)))))) AS "Invoice Date_journal_accountings",
			T2."in_buy_invoice_no_date" AS c152,
			T8."dealer_vehicle_number" AS "Dealer Vehicle Number_Vehicles",
			T8."dealer_vehicle_type" AS "Dealer Vehicle Type_Vehicles",
			CASE 
				WHEN (T1."nominal_account_number" IN (4300, 4560, 4580, 4530, 4540, 4590, 4591, 4690, 4621, 4622, 4623, 4500, 4501, 4502, 4503, 4504, 5501))
					THEN (
							(
								CASE 
									WHEN (T1."debit_or_credit" = 'H')
										THEN (T1."posted_value" / 100 * - 1)
									ELSE (T1."posted_value" / 100)
									END
								)
							)
				ELSE (0)
				END AS "var. Kosten",
			(
				CASE 
					WHEN (T1."nominal_account_number" IN (7000, 7010, 7100, 7101, 7110, 7111, 7120, 7510))
						THEN (
								(
									CASE 
										WHEN (T1."debit_or_credit" = 'H')
											THEN (T1."posted_value" / 100 * - 1)
										ELSE (T1."posted_value" / 100)
										END
									)
								)
					ELSE (0)
					END
				) + (
				(
					CASE 
						WHEN (T1."nominal_account_number" IN (7200))
							THEN (
									(
										CASE 
											WHEN (T1."debit_or_credit" = 'H')
												THEN (T1."posted_value" / 100 * - 1)
											ELSE (T1."posted_value" / 100)
											END
										)
									)
						ELSE (0)
						END
					) + (
					CASE 
						WHEN (T1."nominal_account_number" IN (7210))
							THEN (
									(
										CASE 
											WHEN (T1."debit_or_credit" = 'H')
												THEN (T1."posted_value" / 100 * - 1)
											ELSE (T1."posted_value" / 100)
											END
										)
									)
						ELSE (0)
						END
					) + (
					CASE 
						WHEN (T1."nominal_account_number" IN (7220))
							THEN (
									(
										CASE 
											WHEN (T1."debit_or_credit" = 'H')
												THEN (T1."posted_value" / 100 * - 1)
											ELSE (T1."posted_value" / 100)
											END
										)
									)
						ELSE (0)
						END
					) + (
					CASE 
						WHEN (T1."nominal_account_number" IN (7240))
							THEN (
									(
										CASE 
											WHEN (T1."debit_or_credit" = 'H')
												THEN (T1."posted_value" / 100 * - 1)
											ELSE (T1."posted_value" / 100)
											END
										)
									)
						ELSE (0)
						END
					) + (
					CASE 
						WHEN (T1."nominal_account_number" IN (7030))
							THEN (
									(
										CASE 
											WHEN (T1."debit_or_credit" = 'H')
												THEN (T1."posted_value" / 100 * - 1)
											ELSE (T1."posted_value" / 100)
											END
										)
									)
						ELSE (0)
						END
					)
				) + (
				CASE 
					WHEN (T1."nominal_account_number" IN (5001, 5005, 5006, 5007, 5008, 5003, 5004))
						THEN (
								(
									CASE 
										WHEN (T1."debit_or_credit" = 'H')
											THEN (T1."posted_value" / 100 * - 1)
										ELSE (T1."posted_value" / 100)
										END
									)
								)
					ELSE (0)
					END
				) + (
				CASE 
					WHEN (T1."nominal_account_number" IN (7800, 7850))
						THEN (
								(
									CASE 
										WHEN (T1."debit_or_credit" = 'H')
											THEN (T1."posted_value" / 100 * - 1)
										ELSE (T1."posted_value" / 100)
										END
									)
								)
					ELSE (0)
					END
				) AS "VAK ges.",
			(
				CASE 
					WHEN (T1."nominal_account_number" IN (8000, 8010, 8100, 8110, 8111, 8112, 8510, 8928, 8934))
						THEN (
								(
									CASE 
										WHEN (T1."debit_or_credit" = 'H')
											THEN (T1."posted_value" / 100 * - 1)
										ELSE (T1."posted_value" / 100)
										END
									) * - 1
								)
					ELSE (0)
					END
				) + (
				(
					CASE 
						WHEN (T1."nominal_account_number" IN (8200))
							THEN (
									(
										CASE 
											WHEN (T1."debit_or_credit" = 'H')
												THEN (T1."posted_value" / 100 * - 1)
											ELSE (T1."posted_value" / 100)
											END
										) * - 1
									)
						ELSE (0)
						END
					) + (
					CASE 
						WHEN (T1."nominal_account_number" IN (8210))
							THEN (
									(
										CASE 
											WHEN (T1."debit_or_credit" = 'H')
												THEN (T1."posted_value" / 100 * - 1)
											ELSE (T1."posted_value" / 100)
											END
										) * - 1
									)
						ELSE (0)
						END
					) + (
					CASE 
						WHEN (T1."nominal_account_number" IN (8220))
							THEN (
									(
										CASE 
											WHEN (T1."debit_or_credit" = 'H')
												THEN (T1."posted_value" / 100 * - 1)
											ELSE (T1."posted_value" / 100)
											END
										) * - 1
									)
						ELSE (0)
						END
					) + (
					CASE 
						WHEN (T1."nominal_account_number" IN (8030))
							THEN (
									(
										CASE 
											WHEN (T1."debit_or_credit" = 'H')
												THEN (T1."posted_value" / 100 * - 1)
											ELSE (T1."posted_value" / 100)
											END
										) * - 1
									)
						ELSE (0)
						END
					) + (
					CASE 
						WHEN (T1."nominal_account_number" IN (8900))
							THEN (
									(
										CASE 
											WHEN (T1."debit_or_credit" = 'H')
												THEN (T1."posted_value" / 100 * - 1)
											ELSE (T1."posted_value" / 100)
											END
										) * - 1
									)
						ELSE (0)
						END
					)
				) + (
				CASE 
					WHEN (T1."nominal_account_number" IN (8800, 8801, 8870, 8820, 8830))
						THEN (
								(
									CASE 
										WHEN (T1."debit_or_credit" = 'H')
											THEN (T1."posted_value" / 100 * - 1)
										ELSE (T1."posted_value" / 100)
										END
									) * - 1
								)
					ELSE (0)
					END
				) + (
				CASE 
					WHEN (T1."nominal_account_number" IN (8001, 8011, 5002))
						THEN (
								(
									CASE 
										WHEN (T1."debit_or_credit" = 'H')
											THEN (T1."posted_value" / 100 * - 1)
										ELSE (T1."posted_value" / 100)
										END
									) * - 1
								)
					ELSE (0)
					END
				) AS "Erl�s ges.",
			CASE 
				WHEN (T1."nominal_account_number" IN (7800, 7850))
					THEN (
							(
								CASE 
									WHEN (T1."debit_or_credit" = 'H')
										THEN (T1."posted_value" / 100 * - 1)
									ELSE (T1."posted_value" / 100)
									END
								)
							)
				ELSE (0)
				END AS "Boni/VK-Hilfen",
			CASE 
				WHEN (T1."nominal_account_number" IN (8800, 8801, 8870, 8820, 8830))
					THEN (
							(
								CASE 
									WHEN (T1."debit_or_credit" = 'H')
										THEN (T1."posted_value" / 100 * - 1)
									ELSE (T1."posted_value" / 100)
									END
								) * - 1
							)
				ELSE (0)
				END AS "Erl�s Prov.",
			CASE 
				WHEN (T1."nominal_account_number" IN (5001, 5005, 5006, 5007, 5008, 5003, 5004))
					THEN (
							(
								CASE 
									WHEN (T1."debit_or_credit" = 'H')
										THEN (T1."posted_value" / 100 * - 1)
									ELSE (T1."posted_value" / 100)
									END
								)
							)
				ELSE (0)
				END AS "VAK intern",
			(
				CASE 
					WHEN (T1."nominal_account_number" IN (7200))
						THEN (
								(
									CASE 
										WHEN (T1."debit_or_credit" = 'H')
											THEN (T1."posted_value" / 100 * - 1)
										ELSE (T1."posted_value" / 100)
										END
									)
								)
					ELSE (0)
					END
				) + (
				CASE 
					WHEN (T1."nominal_account_number" IN (7210))
						THEN (
								(
									CASE 
										WHEN (T1."debit_or_credit" = 'H')
											THEN (T1."posted_value" / 100 * - 1)
										ELSE (T1."posted_value" / 100)
										END
									)
								)
					ELSE (0)
					END
				) + (
				CASE 
					WHEN (T1."nominal_account_number" IN (7220))
						THEN (
								(
									CASE 
										WHEN (T1."debit_or_credit" = 'H')
											THEN (T1."posted_value" / 100 * - 1)
										ELSE (T1."posted_value" / 100)
										END
									)
								)
					ELSE (0)
					END
				) + (
				CASE 
					WHEN (T1."nominal_account_number" IN (7240))
						THEN (
								(
									CASE 
										WHEN (T1."debit_or_credit" = 'H')
											THEN (T1."posted_value" / 100 * - 1)
										ELSE (T1."posted_value" / 100)
										END
									)
								)
					ELSE (0)
					END
				) + (
				CASE 
					WHEN (T1."nominal_account_number" IN (7030))
						THEN (
								(
									CASE 
										WHEN (T1."debit_or_credit" = 'H')
											THEN (T1."posted_value" / 100 * - 1)
										ELSE (T1."posted_value" / 100)
										END
									)
								)
					ELSE (0)
					END
				) AS "VAK Sonst.",
			(
				CASE 
					WHEN (T1."nominal_account_number" IN (8200))
						THEN (
								(
									CASE 
										WHEN (T1."debit_or_credit" = 'H')
											THEN (T1."posted_value" / 100 * - 1)
										ELSE (T1."posted_value" / 100)
										END
									) * - 1
								)
					ELSE (0)
					END
				) + (
				CASE 
					WHEN (T1."nominal_account_number" IN (8210))
						THEN (
								(
									CASE 
										WHEN (T1."debit_or_credit" = 'H')
											THEN (T1."posted_value" / 100 * - 1)
										ELSE (T1."posted_value" / 100)
										END
									) * - 1
								)
					ELSE (0)
					END
				) + (
				CASE 
					WHEN (T1."nominal_account_number" IN (8220))
						THEN (
								(
									CASE 
										WHEN (T1."debit_or_credit" = 'H')
											THEN (T1."posted_value" / 100 * - 1)
										ELSE (T1."posted_value" / 100)
										END
									) * - 1
								)
					ELSE (0)
					END
				) + (
				CASE 
					WHEN (T1."nominal_account_number" IN (8030))
						THEN (
								(
									CASE 
										WHEN (T1."debit_or_credit" = 'H')
											THEN (T1."posted_value" / 100 * - 1)
										ELSE (T1."posted_value" / 100)
										END
									) * - 1
								)
					ELSE (0)
					END
				) + (
				CASE 
					WHEN (T1."nominal_account_number" IN (8900))
						THEN (
								(
									CASE 
										WHEN (T1."debit_or_credit" = 'H')
											THEN (T1."posted_value" / 100 * - 1)
										ELSE (T1."posted_value" / 100)
										END
									) * - 1
								)
					ELSE (0)
					END
				) AS "Erl�s Sonst.",
			CASE 
				WHEN (T1."nominal_account_number" IN (8900))
					THEN (
							(
								CASE 
									WHEN (T1."debit_or_credit" = 'H')
										THEN (T1."posted_value" / 100 * - 1)
									ELSE (T1."posted_value" / 100)
									END
								) * - 1
							)
				ELSE (0)
				END AS "Erl�s Sonst._8900",
			CASE 
				WHEN (T1."nominal_account_number" IN (7030))
					THEN (
							(
								CASE 
									WHEN (T1."debit_or_credit" = 'H')
										THEN (T1."posted_value" / 100 * - 1)
									ELSE (T1."posted_value" / 100)
									END
								)
							)
				ELSE (0)
				END AS "VAK �berf.",
			CASE 
				WHEN (T1."nominal_account_number" IN (8030))
					THEN (
							(
								CASE 
									WHEN (T1."debit_or_credit" = 'H')
										THEN (T1."posted_value" / 100 * - 1)
									ELSE (T1."posted_value" / 100)
									END
								) * - 1
							)
				ELSE (0)
				END AS "Erl�s �berf.",
			CASE 
				WHEN (T1."nominal_account_number" IN (7240))
					THEN (
							(
								CASE 
									WHEN (T1."debit_or_credit" = 'H')
										THEN (T1."posted_value" / 100 * - 1)
									ELSE (T1."posted_value" / 100)
									END
								)
							)
				ELSE (0)
				END AS "VAK Fertigm.",
			CASE 
				WHEN (T1."nominal_account_number" IN (7220))
					THEN (
							(
								CASE 
									WHEN (T1."debit_or_credit" = 'H')
										THEN (T1."posted_value" / 100 * - 1)
									ELSE (T1."posted_value" / 100)
									END
								)
							)
				ELSE (0)
				END AS "VAK Nachr./Aufb.",
			CASE 
				WHEN (T1."nominal_account_number" IN (8220))
					THEN (
							(
								CASE 
									WHEN (T1."debit_or_credit" = 'H')
										THEN (T1."posted_value" / 100 * - 1)
									ELSE (T1."posted_value" / 100)
									END
								) * - 1
							)
				ELSE (0)
				END AS "Erl�s Nachr./Aufb.",
			CASE 
				WHEN (T1."nominal_account_number" IN (7210))
					THEN (
							(
								CASE 
									WHEN (T1."debit_or_credit" = 'H')
										THEN (T1."posted_value" / 100 * - 1)
									ELSE (T1."posted_value" / 100)
									END
								)
							)
				ELSE (0)
				END AS "VAK Gar.",
			CASE 
				WHEN (T1."nominal_account_number" IN (8210))
					THEN (
							(
								CASE 
									WHEN (T1."debit_or_credit" = 'H')
										THEN (T1."posted_value" / 100 * - 1)
									ELSE (T1."posted_value" / 100)
									END
								) * - 1
							)
				ELSE (0)
				END AS "Erl�s Gar.",
			CASE 
				WHEN (T1."nominal_account_number" IN (7200))
					THEN (
							(
								CASE 
									WHEN (T1."debit_or_credit" = 'H')
										THEN (T1."posted_value" / 100 * - 1)
									ELSE (T1."posted_value" / 100)
									END
								)
							)
				ELSE (0)
				END AS "VAK Zulass.",
			CASE 
				WHEN (T1."nominal_account_number" IN (8200))
					THEN (
							(
								CASE 
									WHEN (T1."debit_or_credit" = 'H')
										THEN (T1."posted_value" / 100 * - 1)
									ELSE (T1."posted_value" / 100)
									END
								) * - 1
							)
				ELSE (0)
				END AS "Erl�s Zulass.",
			CASE 
				WHEN (T1."nominal_account_number" IN (7000, 7010, 7100, 7101, 7110, 7111, 7120, 7510))
					THEN (
							(
								CASE 
									WHEN (T1."debit_or_credit" = 'H')
										THEN (T1."posted_value" / 100 * - 1)
									ELSE (T1."posted_value" / 100)
									END
								)
							)
				ELSE (0)
				END AS "VAK FZG",
			CASE 
				WHEN (T1."nominal_account_number" IN (8001, 8011, 5002))
					THEN (
							(
								CASE 
									WHEN (T1."debit_or_credit" = 'H')
										THEN (T1."posted_value" / 100 * - 1)
									ELSE (T1."posted_value" / 100)
									END
								) * - 1
							)
				ELSE (0)
				END AS "Nachlass",
			CASE 
				WHEN (T1."nominal_account_number" IN (8000, 8010, 8100, 8110, 8111, 8112, 8510, 8928, 8934))
					THEN (
							(
								CASE 
									WHEN (T1."debit_or_credit" = 'H')
										THEN (T1."posted_value" / 100 * - 1)
									ELSE (T1."posted_value" / 100)
									END
								) * - 1
							)
				ELSE (0)
				END AS "Erl�s FZG",
			T8."body_paint_description" AS "Body Paint Description",
			T8."body_paint_code" AS "Body Paint Code",
			((left((((T1."nominal_account_number"))), 4)) + ' - ' + T4."account_description") AS "Konto_mit_Bezeichnung",
			(left(((left(T1."vehicle_reference", 7)) + '_' + (right(T1."vehicle_reference", 17))), 7)) + ' / ' + ((left((((T8."internal_number"))), 5))) + ' - ' + T10."description" + ' - ' + (
				(
					(left((((T8."holder_number"))), 7)) + ' - ' + (
						CASE 
							WHEN (T3."first_name" IS NULL)
								THEN ('')
							ELSE (T3."first_name")
							END
						) + ' ' + T3."family_name"
					)
				) AS "FZG",
			CASE 
				WHEN (T2."out_subsidiary" IS NULL)
					THEN ((('0' + (((T1."skr51_branch"))))))
				ELSE ('0' + (((T2."out_subsidiary"))))
				END AS "Standort",
			T2."out_subsidiary" AS "Standort_FZG_Verkauf",
			(left((((T8."salesman_number"))), 4)) + ' - ' + T12."name" AS "Verk�ufer",
			T12."name" AS "Name_Employees_Salesman",
			T12."employee_number" AS "Employee Number_Employees_Salesman",
			T8."salesman_number" AS "Salesman Number_Vehicles",
			('0' + (((T1."skr51_branch")))) AS "Betrieb",
			T1."subsidiary_to_company_ref" AS "Rechtseinheit",
			CASE 
				WHEN (
						(
							(
								CASE 
									WHEN (T1."nominal_account_number" IN (8000, 8010, 8100, 8110, 8510))
										THEN (1)
									ELSE (0)
									END
								) = 1
							)
						AND (T7."is_canceled" <> '1')
						)
					THEN (T1."skr51_sales_channel")
				ELSE NULL
				END AS "Sales_Channel_FZG",
			(
				(left((((T8."holder_number"))), 7)) + ' - ' + (
					CASE 
						WHEN (T3."first_name" IS NULL)
							THEN ('')
						ELSE (T3."first_name")
						END
					) + ' ' + T3."family_name"
				) AS "Kunde",
			T3."family_name" AS "Family Name_Holder_Customer",
			CASE 
				WHEN (T3."first_name" IS NULL)
					THEN ('')
				ELSE (T3."first_name")
				END AS "First Name_Holder_Customer",
			T11."family_name" AS "Family Name_Owner_Customer",
			T11."first_name" AS "First Name_Owner_Customer",
			T8."holder_number" AS "Holder Number_Vehicles",
			T8."owner_number" AS "Owner Number_Vehicles",
			(left((ucase(T10."description")), 3)) AS "Model",
			T10."description" AS "Model_Detail",
			T10."model_code" AS "Model Code_Models",
			CASE 
				WHEN (((rtrim((((T1."skr51_make"))))) + ' - ' + T5."skr51_make_description") = '3 - Husqvarna')
					THEN ('Husqvarna')
				ELSE (T9."description")
				END AS "Fabrikat",
			T9."description" AS "Description_Make",
			T8."make_number" AS "Make Number_Vehicles",
			CASE 
				WHEN (
						(
							CASE 
								WHEN (((left(T1."vehicle_reference", 1))) IN ('N'))
									THEN ('NW')
								WHEN (((left(T1."vehicle_reference", 1))) IN ('V'))
									THEN ('VFW')
								WHEN (((left(T1."vehicle_reference", 1))) IN ('G', 'D'))
									THEN ('GW')
								WHEN (((left(T1."vehicle_reference", 1))) IN ('T'))
									THEN ('TZ')
								ELSE NULL
								END
							) IN ('VFW', 'TZ', 'NW')
						)
					THEN ('Neuwagen')
				ELSE ('Gebrauchtwagen')
				END AS "Fahrzeugart",
			CASE 
				WHEN (((left(T1."vehicle_reference", 1))) IN ('N'))
					THEN ('NW')
				WHEN (((left(T1."vehicle_reference", 1))) IN ('V'))
					THEN ('VFW')
				WHEN (((left(T1."vehicle_reference", 1))) IN ('G', 'D'))
					THEN ('GW')
				WHEN (((left(T1."vehicle_reference", 1))) IN ('T'))
					THEN ('TZ')
				ELSE NULL
				END AS "Fahrzeugtyp",
			(left(T1."vehicle_reference", 1)) AS "Dealer Vehicle Type",
			T8."vin" AS "Vin_Vehicles",
			T8."internal_number" AS "Internal Number_Vehicles",
			CASE 
				WHEN (
						(
							CASE 
								WHEN (T1."nominal_account_number" IN (8000, 8010, 8100, 8110, 8510))
									THEN (1)
								ELSE (0)
								END
							) = 1
						)
					THEN (((((nconvert((left(T1."invoice_date", 4)) + (substring(T1."invoice_date", 6, 2)) + (substring(T1."invoice_date", 9, 2))))))))
				ELSE NULL
				END AS "Invoice_Date_1",
			T7."vehicle_number" AS "Vehicle Number_Invoices",
			T7."is_canceled" AS "Is Canceled_Invoices",
			T7."invoice_date" AS "Invoice Date_Invoices",
			T7."invoice_number" AS "Invoice Number_Invoices",
			T7."invoice_type" AS "Invoice Type_Invoices",
			CASE 
				WHEN (T1."nominal_account_number" IN (8000, 8010, 8100, 8110, 8510))
					THEN (1)
				ELSE (0)
				END AS "FZG_ja_nein",
			T6."document_type_description" AS "Document Type Description",
			T6."document_type_in_journal" AS "Document Type In Journal",
			T1."nominal_account_number" AS "Nominal Account Number",
			(left((((T1."nominal_account_number"))), 1)) AS "Susa",
			CASE 
				WHEN (T4."is_profit_loss_account" = 'J')
					THEN ('2')
				ELSE ('1')
				END AS "GuV_Bilanz",
			CASE 
				WHEN (T1."skr51_cost_unit" BETWEEN 1 AND 49)
					THEN (((rtrim((((T1."skr51_make"))))) + ' - ' + (rtrim((((T1."skr51_cost_unit"))))) + ' - ' + (T5."skr51_cost_unit_name")))
				ELSE ((rtrim((((T1."skr51_cost_unit"))))) + ' - ' + (T5."skr51_cost_unit_name"))
				END AS "Kostentr�ger",
			(rtrim((((T1."skr51_make"))))) + ' - ' + (rtrim((((T1."skr51_cost_unit"))))) + ' - ' + (T5."skr51_cost_unit_name") AS "Kostentr�ger_mit_Null",
			(len((((T1."skr51_cost_unit"))) + 'Z') - 1) AS "Stellen Cost Unit",
			(rtrim((((T1."skr51_sales_channel"))))) + ' - ' + T5."skr51_sales_channel_name" AS "Absatzkanal",
			(len((((T1."skr51_sales_channel"))) + 'Z') - 1) AS "Stellen Sales Channel",
			(rtrim((((T1."skr51_cost_center"))))) + ' - ' + T5."skr51_cost_center_name" AS "KST",
			(len((((T1."skr51_cost_center"))) + 'Z') - 1) AS "Stellen Cost Center",
			CASE 
				WHEN (T1."debit_or_credit" = 'H')
					THEN (T1."posted_count" / 100 * - 1)
				ELSE (T1."posted_count" / 100)
				END AS "Menge_2",
			CASE 
				WHEN (T1."debit_or_credit" = 'H')
					THEN (T1."posted_value" / 100 * - 1)
				ELSE (T1."posted_value" / 100)
				END AS "Betrag",
			CASE 
				WHEN ((- 1 * datediff(day, (getdate()), (convert(DATETIME, T1."accounting_date")))) <= 360)
					THEN (T1."invoice_number" + ' - ' + T1."posting_text" + '/' + T1."vehicle_reference" + ' - ' + (rtrim((((T1."employee_number"))))))
				ELSE ('Buchungen �lter 360 Tage')
				END AS "Text",
			(rtrim((((T1."skr51_make"))))) + ' - ' + T5."skr51_make_description" AS "Marke",
			T1."accounting_date" AS "Accounting Date",
			T4."is_profit_loss_account" AS "Nom_Account_Is Profit Loss Account",
			CASE 
				WHEN (T1."skr51_cost_unit" BETWEEN 1 AND 49)
					THEN ('Neuwagen')
				WHEN (T1."skr51_cost_unit" BETWEEN 50 AND 59)
					THEN ('Gebrauchtwagen')
				WHEN (T1."skr51_cost_unit" BETWEEN 60 AND 69)
					THEN ('Teile & Zubeh�r')
				WHEN (T1."skr51_cost_unit" BETWEEN 70 AND 79)
					THEN ('Service')
				WHEN (T1."skr51_cost_unit" = 0)
					THEN ('Ohne Kostentr�ger')
				ELSE NULL
				END AS "Free Form Document Text",
			T3."zip_code" AS "Free Form Accounting Text",
			T1."previously_used_account_no" AS "Previously Used Account No",
			T1."skr51_cost_unit" AS "Skr51 Cost Unit",
			T1."skr51_sales_channel" AS "Skr51 Sales Channel",
			T1."skr51_cost_center" AS "Skr51 Cost Center",
			T1."skr51_make" AS "Skr51 Make",
			T1."skr51_branch" AS "Skr51 Branch",
			T1."day_of_actual_accounting" AS "Day Of Actual Accounting",
			T1."days_for_cash_discount" AS "Days For Cash Discount",
			T1."vat_key" AS "Vat Key",
			T1."account_statement_page" AS "Account Statement Page",
			T1."account_statement_number" AS "Account Statement Number",
			T1."vat_id_number" AS "Vat Id Number",
			T1."vehicle_reference" AS "Vehicle Reference",
			T1."posting_text" AS "Posting Text",
			T1."term_of_payment" AS "Term Of Payment",
			T1."cash_discount" AS "Cash Discount",
			T1."journal_line" AS "Journal Line",
			T1."journal_page" AS "Journal Page",
			T1."last_dunning_date" AS "Last Dunning Date",
			T1."dunning_level" AS "Dunning Level",
			T1."invoice_number" AS "Invoice Number",
			T1."employee_number" AS "Employee Number",
			T1."serial_number_each_month" AS "Serial Number Each Month",
			T1."account_form_page_line" AS "Account Form Page Line",
			T1."account_form_page_number" AS "Account Form Page Number",
			T1."contra_account_text" AS "Contra Account Text",
			T1."nominal_contra_account" AS "Nominal Contra Account",
			T1."customer_contra_account" AS "Customer Contra Account",
			T1."branch_number" AS "Branch Number",
			T1."posted_count" AS "Posted Count",
			T1."debit_or_credit" AS "Debit Or Credit",
			T1."posted_value" AS "Posted Value",
			T1."document_date" AS "Document Date",
			T1."clearing_number" AS "Clearing Number",
			T1."is_balanced" AS "Is Balanced",
			T1."position_in_document" AS "Position In Document",
			T1."document_number" AS "Document Number",
			T1."document_type" AS "Document Type"
		FROM "nominal_accounts" T4,
			(
				(
					(
						(
							(
								(
									(
										(
											(
												(
													"journal_accountings" T1 LEFT JOIN "vehicles" T8 ON (right(T1."vehicle_reference", 17)) = T8."vin"
													) LEFT JOIN "LOCOSOFT"."dbo"."dealer_vehicles" T2 ON (T8."dealer_vehicle_type" = T2."dealer_vehicle_type")
													AND (T8."dealer_vehicle_number" = T2."dealer_vehicle_number")
												) LEFT JOIN "customers_suppliers" T3 ON T3."customer_number" = T8."holder_number"
											) LEFT JOIN "accounts_characteristics" T5 ON (
												(
													(
														(
															(T5."skr51_make" = T1."skr51_make")
															AND (T5."skr51_cost_center" = T1."skr51_cost_center")
															)
														AND (T5."skr51_sales_channel" = T1."skr51_sales_channel")
														)
													AND (T5."skr51_cost_unit" = T1."skr51_cost_unit")
													)
												AND (T5."skr51_branch" = T1."skr51_branch")
												)
											AND (T5."subsidiary_to_company_ref" = T1."subsidiary_to_company_ref")
										) LEFT JOIN "document_types" T6 ON T6."document_type_in_journal" = T1."document_type"
									) LEFT JOIN "invoices" T7 ON T7."invtype_invnr" = T1."invoice_number"
								) LEFT JOIN "makes" T9 ON T8."make_number" = T9."make_number"
							) LEFT JOIN "models" T10 ON (T8."make_number" = T10."make_number")
							AND (T8."model_code" = T10."model_code")
						) LEFT JOIN "customers_suppliers" T11 ON T11."customer_number" = T8."owner_number"
					) LEFT JOIN "employees" T12 ON (T8."salesman_number" <> 0)
					AND (T8."salesman_number" = T12."salesman_number")
				)
		WHERE (
				(T4."nominal_account_number" = T1."nominal_account_number")
				AND (T4."subsidiary_to_company_ref" = T1."subsidiary_to_company_ref")
				)
			AND (
				(
					(T4."is_profit_loss_account" = 'J')
					AND (T1."vehicle_reference" <> ' ')
					)
				AND (T1."nominal_account_number" IN (8000, 8001, 8010, 8011, 5002, 8100, 8110, 8111, 8200, 8210, 8510, 8800, 8820, 8830, 8870, 8928, 8934, 7000, 7010, 7100, 7101, 7110, 7111, 7120, 7200, 7210, 7240, 7510, 5001, 5005, 5006, 5007, 5008, 5003, 5004, 4300, 4560, 4580, 4530, 4540, 4590, 4591, 4690, 4621, 4622, 4623, 4500, 4501, 4502, 4503, 4504, 5501, 7220, 7800, 7850, 7801, 7802, 7803, 7804, 7805, 7806, 7807, 7820, 7825, 7827, 7808, 8030, 7030, 8870, 8871, 8872, 8220, 7220))
				)
		) D2
	) D1
WHERE (
		(
			(
				(
					(c143 = "Internal Number_Vehicles")
					OR ("Internal Number_Vehicles" IS NULL)
					)
				OR (c143 IS NULL)
				)
			AND (c144 >= convert(DATE, '2017-01-01'))
			)
		AND (c145 = 'FZG-Gesch�ft')
		)
	-- order by "Vehicle Reference_berechnet" asc,"Vehicle Reference" asc,"Nominal Account Number" asc