SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW [transform_imr].[Aftersales_Rechnungen_ben_AW_final] AS
SELECT "Invoice Type"
     , "Invoice Number"
     , "Subsidiary"
     , "Invoice Date"
     , "Service Date"
     , "Is Canceled"
     , "Vehicle Number"
     , "Invoice_Type_Invoice_Number"
     , '1' AS "Hauptbetrieb"
     , "Standort"
     , "Serviceberater"
     , "Umsatzart"
     , "Fabrikat"
     , "Fabrikat" AS "Description_Makes"
     , "Description_Models"
     , "Model"
     , "Fahrzeug"
     , "Kostenstelle"
     , "Marke"
     , 'Service' AS "Auftragsart"
     , "Geschäftsart"
     , "Kunde"
     , "Time Units"
     , "Time Units_Zahl"
     , 10 AS "AW/Std."
     , "verk. Std._"
     , "Order Number"
     , "Order Number_Rg_Ausg"
     , "Kundenart"
     , "Summe Duration Minutes"
     , "Order Number_ben_AW"
     , 1.0 AS "Anzahl_Datensätze_1"
     , count("Order Number_ben_AW") OVER (PARTITION BY "Order Number") AS "Anzahl_Datensätze_2"
     , ([c38]) / (count("Order Number_ben_AW") OVER (PARTITION BY "Order Number")) AS "ben. Std."
FROM (SELECT (
    CASE
        WHEN ([Serviceberater_Rechnung]."Serviceberater" IS NOT NULL)
            THEN (
            (left(((([invoices]."invoice_type"))), 1)) + (left(((([invoices]."invoice_number"))), 9)) + ' - ' + [Serviceberater_Rechnung]."Serviceberater" + ' - ' + (
                CASE
                    WHEN ([customers_suppliers]."first_name" IS NULL)
                        THEN ((left(((([customers_suppliers]."customer_number"))), 7)) + ' - ' + [customers_suppliers]."family_name")
                    ELSE ((left(((([customers_suppliers]."customer_number"))), 7)) + ' - ' + [customers_suppliers]."first_name" + ' ' + [customers_suppliers]."family_name")
                END
                )
            )
        ELSE (
            (left(((([invoices]."invoice_type"))), 1)) + (left(((([invoices]."invoice_number"))), 9)) + ' - ' + (
                CASE
                    WHEN ([customers_suppliers]."first_name" IS NULL)
                        THEN ((left(((([customers_suppliers]."customer_number"))), 7)) + ' - ' + [customers_suppliers]."family_name")
                    ELSE ((left(((([customers_suppliers]."customer_number"))), 7)) + ' - ' + [customers_suppliers]."first_name" + ' ' + [customers_suppliers]."family_name")
                END
                )
            )
    END
    ) AS "Order Number"
           , (0.0 + [ben_AW_Order_Number]."Summe_Duration_Minutes") / 60.0 AS [c38]
           , [ben_AW_Order_Number]."order_number" AS "Order Number_ben_AW"
           , [ben_AW_Order_Number]."Summe_Duration_Minutes" AS "Summe Duration Minutes"
           , (left(((([employees]."employee_number"))), 4)) + ' - ' + [employees]."name" AS "Kundenart"
           , CASE
                 WHEN ((- 1 * datediff(DAY, (getdate()), (convert(datetime, [invoices]."invoice_date")))) <= 4)
                     THEN (
                     (
                         CASE
                             WHEN ([Serviceberater_Rechnung]."Serviceberater" IS NOT NULL)
                                 THEN (
                                 (left(((([invoices]."invoice_type"))), 1)) + (left(((([invoices]."invoice_number"))), 9)) + ' - ' + [Serviceberater_Rechnung]."Serviceberater" + ' - ' + (
                                     CASE
                                         WHEN ([customers_suppliers]."first_name" IS NULL)
                                             THEN ((left(((([customers_suppliers]."customer_number"))), 7)) + ' - ' + [customers_suppliers]."family_name")
                                         ELSE ((left(((([customers_suppliers]."customer_number"))), 7)) + ' - ' + [customers_suppliers]."first_name" + ' ' + [customers_suppliers]."family_name")
                                     END
                                     )
                                 )
                             ELSE (
                                 (left(((([invoices]."invoice_type"))), 1)) + (left(((([invoices]."invoice_number"))), 9)) + ' - ' + (
                                     CASE
                                         WHEN ([customers_suppliers]."first_name" IS NULL)
                                             THEN ((left(((([customers_suppliers]."customer_number"))), 7)) + ' - ' + [customers_suppliers]."family_name")
                                         ELSE ((left(((([customers_suppliers]."customer_number"))), 7)) + ' - ' + [customers_suppliers]."first_name" + ' ' + [customers_suppliers]."family_name")
                                     END
                                     )
                                 )
                         END
                         )
                     )

             END AS "Order Number_Rg_Ausg"
           , ((convert(float, [labours]."time_units"))) / 10.0 AS "verk. Std._"
           , (convert(float, [labours]."time_units")) AS "Time Units_Zahl"
           , [labours]."time_units" AS "Time Units"
           , CASE
                 WHEN ([customers_suppliers]."first_name" IS NULL)
                     THEN ((left(((([customers_suppliers]."customer_number"))), 7)) + ' - ' + [customers_suppliers]."family_name")
                 ELSE ((left(((([customers_suppliers]."customer_number"))), 7)) + ' - ' + [customers_suppliers]."first_name" + ' ' + [customers_suppliers]."family_name")
             END AS "Kunde"
           , [customers_suppliers]."zip_code" AS "Geschäftsart"
           , CASE
                 WHEN ([makes]."description" = 'Ford')
                     THEN ('1')
                 WHEN ([makes]."description" = 'Nissan')
                     THEN ('3')
                 WHEN ([makes]."description" = 'Suzuki')
                     THEN ('2')
                 ELSE ('9')
             END AS "Marke"
           , CASE
                 WHEN ((left(((([charge_type_descriptions]."type"))), 1)) IN ('1', '6'))
                     THEN ('41 - After Sales Kundendienst eigene Werkstatt')
                 WHEN ([charge_type_descriptions]."type" IN (40, 41, 44, 45, 46, 47, 48, 49, 88, 89, 50, 90))
                     THEN ('41 - After Sales Kundendienst eigene Werkstatt')
                 WHEN ((left(((([charge_type_descriptions]."type"))), 1)) IN ('2'))
                     THEN ('44 - After Sales Kundendienst eigene Karosserieabteilung')
                 WHEN ([charge_type_descriptions]."type" IN (42, 92))
                     THEN ('44 - After Sales Kundendienst eigene Karosserieabteilung')
                 WHEN ((left(((([charge_type_descriptions]."type"))), 1)) IN ('3'))
                     THEN ('45 - After Sales Kundendienst eigene Lackiererei')
                 WHEN ([charge_type_descriptions]."type" IN (43, 93))
                     THEN ('45 - After Sales Kundendienst eigene Lackiererei')
             END AS "Kostenstelle"
           , (left((left(((([invoices]."vehicle_number"))), 5)) + ' - ' + [models]."description", 100)) AS "Fahrzeug"
           , (left((upper([models]."description")), 3)) AS "Model"
           , [models]."description" AS "Description_Models"
           , [makes]."description" AS "Fabrikat"
           , CASE
                 WHEN ([invoices]."invoice_type" = 6)
                     THEN ('GWL')
                 WHEN (
                     ([invoices]."invoice_type" = 4)
                         OR ([customers_suppliers]."customer_number" = 100001)
                     )
                     THEN ('intern')
                 ELSE ('extern')
             END AS "Umsatzart"
           , [Serviceberater_Rechnung]."Serviceberater" AS "Serviceberater"
           , ((([invoices]."subsidiary"))) AS "Standort"
           , (left(((([invoices]."invoice_type"))), 1)) + '_' + (left(((([invoices]."invoice_number"))), 9)) AS "Invoice_Type_Invoice_Number"
           , [invoices]."vehicle_number" AS "Vehicle Number"
           , [invoices]."is_canceled" AS "Is Canceled"
           , [invoices]."service_date" AS "Service Date"
           , [invoices]."invoice_date" AS "Invoice Date"
           , [invoices]."subsidiary" AS "Subsidiary"
           , [invoices]."invoice_number" AS "Invoice Number"
           , [invoices]."invoice_type" AS "Invoice Type"
      FROM [LOCOSOFT]."dbo"."invoices" [invoices]
               LEFT JOIN [LOCOSOFT]."dbo"."labours" [labours] ON ([labours]."invoice_number" = [invoices]."invoice_number")
          AND ([labours]."invoice_type" = [invoices]."invoice_type")
               INNER JOIN "transform_ims"."ben_AW_Order_Number" [ben_AW_Order_Number] ON ([labours]."order_number" = [ben_AW_Order_Number]."order_number")
               LEFT JOIN [LOCOSOFT]."dbo"."customers_suppliers" [customers_suppliers] ON [customers_suppliers]."customer_number" = [invoices]."paying_customer"
               LEFT JOIN [LOCOSOFT]."dbo"."vehicles" [vehicles] ON [invoices]."vehicle_number" = [vehicles]."internal_number"
               LEFT JOIN [LOCOSOFT]."dbo"."makes" [makes] ON [vehicles]."make_number" = [makes]."make_number"
               LEFT JOIN [LOCOSOFT]."dbo"."models" [models] ON ([vehicles]."make_number" = [models]."make_number")
          AND ([vehicles]."model_code" = [models]."model_code")
               LEFT JOIN [LOCOSOFT]."dbo"."charge_type_descriptions" [charge_type_descriptions] ON ([charge_type_descriptions]."type" = [labours]."charge_type")
          AND ([charge_type_descriptions]."client_db" = [labours]."client_db")
               LEFT JOIN [LOCOSOFT]."dbo"."employees" [employees] ON [invoices]."creating_employee" = [employees]."employee_number"
               LEFT JOIN "transform_ims"."Serviceberater_Rechnung" [Serviceberater_Rechnung] ON [invoices]."invtype_invnr" = [Serviceberater_Rechnung].[Invoice_Type_Invoice_Number]

      WHERE [invoices]."invoice_type" BETWEEN 2 AND 6
        AND ([invoices]."is_canceled" <> 1)
        AND ([invoices]."invoice_date" >= convert(datetime, '2017-01-01T00:00:00'))
        AND (convert(float, [labours]."time_units") <> 0)
        AND (convert(float, [labours]."time_units") IS NOT NULL)) [D1]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO
