C11/System/NAVISION/IQD/NW/verkaeufer_aktiv_plan.sql

select T1."User ID" as "User Id",
	   T1."Employee No_" as "Employee No",
	   T1."Salesperson" as "Salesperson",
	   T2."No_" as "No",
	   T2."First Name" as "First Name",
	   T2."Last Name" as "Last Name",
	   T2."Name" as "Name",
	   T2."Status" as "Status",
	   T2."Inactive Date" as "Inactive Date",
	   T2."Termination Date" as "Termination Date",
	   T2."Department Code" as "Department Code",
	   (right(T2."Department Code",2)) as "KST",
	   CASE WHEN (((right(T2."Department Code",2))) = '20') THEN (216) ELSE (0) END as "Plan_Stück",
	   (CASE WHEN (((right(T2."Department Code",2))) = '20') THEN (216) ELSE (0) END) / 12 as "Stk_1",
	   (CASE WHEN (((right(T2."Department Code",2))) = '20') THEN (216) ELSE (0) END) / 12 as "Stk_2",
	   (CASE WHEN (((right(T2."Department Code",2))) = '20') THEN (216) ELSE (0) END) / 12 as "Stk_3",
	   (CASE WHEN (((right(T2."Department Code",2))) = '20') THEN (216) ELSE (0) END) / 12 as "Stk_4",
	   (CASE WHEN (((right(T2."Department Code",2))) = '20') THEN (216) ELSE (0) END) / 12 as "Stk_5",
	   (CASE WHEN (((right(T2."Department Code",2))) = '20') THEN (216) ELSE (0) END) / 12 as "Stk_6",
	   (CASE WHEN (((right(T2."Department Code",2))) = '20') THEN (216) ELSE (0) END) / 12 as "Stk_7",
	   (CASE WHEN (((right(T2."Department Code",2))) = '20') THEN (216) ELSE (0) END) / 12 as "Stk_8",
	   (CASE WHEN (((right(T2."Department Code",2))) = '20') THEN (216) ELSE (0) END) / 12 as "Stk_9",
	   (CASE WHEN (((right(T2."Department Code",2))) = '20') THEN (216) ELSE (0) END) / 12 as "Stk_10",
	   (CASE WHEN (((right(T2."Department Code",2))) = '20') THEN (216) ELSE (0) END) / 12 as "Stk_11",
	   (CASE WHEN (((right(T2."Department Code",2))) = '20') THEN (216) ELSE (0) END) / 12 as "Stk_12",
	   '2016' as "Invoice Date",
	   CASE WHEN (((right(T2."Department Code",2))) = '20') THEN ('GA') ELSE null END as "Fahrzeugart",
	   T1."Salesperson" + ' - ' + T2."First Name" + ' ' + T2."Last Name" as "Verkäufer_Plan",
	   'aktive Verkäufer' as "Verkäufer Gruppe"
from ("NAVISION"."import"."User_Setup" T1 left outer join "NAVISION"."import"."Employee" T2 on ((((((T2."No_" = T1."Employee No_") and (T2."Status" = 0)) and (T2."Inactive Date" = convert(datetime, '1753-01-01 00:00:00.000'))) and (T2."Termination Date" = convert(datetime, '1753-01-01 00:00:00.000'))) and (T1."Salesperson" <> ' ')) and (T1."Salesperson" <> 'DIV')) and (T2."Client_DB" = T1."Client_DB"))
where ((((T1."Salesperson" <> ' ') and (not T1."Salesperson" IN ('DIV','BMWREL'))) and ((convert(float, T1."Salesperson")) BETWEEN 100 AND 999)) and (T2."Status" = 0))
-- order by "Salesperson" asc