SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW [transform_imr].[Zeit_ben_Std_LG]
AS
SELECT DISTINCT "Order Number" AS "Order Number"
              , "Labour Type" AS "Labour Type"
              , "Code_Labour_Types" AS "Code_Labour_Types"
              , "Description_Labour_Types" AS "Description_Labour_Types"
              , "Order Number_Labours" AS "Order Number_Labours"
              , "Subsidiary_Labours" AS "Subsidiary_Labours"
              , "Invoice Type_Labours" AS "Invoice Type_Labours"
              , "Mechanic No_Labours" AS "Mechanic No_Labours"
              , "AW/Std." AS "AW/Std."
              , "Zeitkategorie" AS "Zeitkategorie"
              , "Zeitkategorie2" AS "Zeitkategorie2"
              , "Employee Number_Employee" AS "Employee Number_Employee"
              , "Name_Employee" AS "Name_Employee"
              , "Monteur" AS "Monteur"
              , "Invoice Date_Invoices" AS "Invoice Date_Invoices"
              , "Datum" AS "Datum"
              , "Duration Minutes_Times" AS "Duration Minutes_Times"
              , "ben. Std." AS "ben. Std."
              , "Invoice Number" AS "Invoice Number"
              , "Is Canceled" AS "Is Canceled"
              , "Cancelation Number" AS "Cancelation Number"
              , "Cancelation Date" AS "Cancelation Date"
              , "Start Time" AS "Start Time"
              , "End Time" AS "End Time"
              , min("Invoice Date_Invoices") OVER ( PARTITION BY "Order Number", "Mechanic No_Labours" ) AS "Min_Invoice_Date"
FROM (SELECT "Order Number"
           , '' AS "Labour Type"
           , '' AS "Code_Labour_Types"
           , "Description_Labour_Types"
           , "Order Number_Labours"
           , "Subsidiary_Labours"
           , 0 AS "Invoice Type_Labours"
           , "Mechanic No_Labours"
           , 10 AS "AW/Std."
           , 'ben. Std.' AS "Zeitkategorie"
           , '' AS "Zeitkategorie2"
           , "Employee Number_Employee"
           , "Name_Employee"
           , "Monteur"
           , "Invoice Date_Invoices"
           , "Invoice Date_Invoices" AS "Datum"
           , "Duration Minutes_Times"
           , "ben. Std."
           , 0 AS "Invoice Number"
           , "Is Canceled"
           , "Cancelation Number"
           , "Cancelation Date"
           , "Start Time"
           , "End Time"
           , min("Invoice Date_Invoices") OVER (PARTITION BY "Order Number", "Mechanic No_Labours") AS [Invoice_Date_Invoices_min]
      FROM (SELECT [T3]."mechanic_no" AS "Mechanic No_Labours"
                 , [T1]."order_number" AS "Order Number"
                 , [T6]."end_time" AS "End Time"
                 , [T6]."start_time" AS "Start Time"
                 , [T5]."cancelation_date" AS "Cancelation Date"
                 , [T5]."cancelation_number" AS "Cancelation Number"
                 , [T5]."is_canceled" AS "Is Canceled"
                 , ((0.0 + [T6]."exact_duration_seconds") / 60) / 60 AS "ben. Std."
                 , (0.0 + [T6]."exact_duration_seconds") / 60 AS "Duration Minutes_Times"
                 , [T5]."invoice_date" AS "Invoice Date_Invoices"
                 , (convert(varchar, ((([T4]."employee_number"))))) + ' - ' + [T4]."name" AS "Monteur"
                 , [T4]."name" AS "Name_Employee"
                 , [T4]."employee_number" AS "Employee Number_Employee"
                 , (convert(varchar, ([T4]."subsidiary"))) AS "Subsidiary_Labours"
                 , [T3]."order_number" AS "Order Number_Labours"
                 , CASE
                       WHEN ([T2]."first_name" IS NULL)
                           THEN ((left(convert(varchar, (([T2]."customer_number"))), 7)) + ' - ' + [T2]."family_name")
                       ELSE ((left(convert(varchar, (([T2]."customer_number"))), 7)) + ' - ' + [T2]."first_name" + ' ' + [T2]."family_name")
                   END AS "Description_Labour_Types"
            FROM [LOCOSOFT]."dbo"."order_positions" [T1]
                     INNER JOIN [LOCOSOFT]."dbo"."labours" [T3] ON [T1]."order_number" = [T3]."order_number"
                     INNER JOIN "LOCOSOFT"."dbo"."times" [T6] ON ([T3]."order_number" = [T6]."order_number") AND ([T3]."mechanic_no" = [T6]."employee_number")
                     INNER JOIN [LOCOSOFT]."dbo"."invoices" [T5] ON ([T3]."invoice_number" = [T5]."invoice_number") AND ([T3]."invoice_type" = [T5]."invoice_type")
                     LEFT JOIN [LOCOSOFT]."dbo"."employees" [T4] ON [T3]."mechanic_no" = [T4]."employee_number"
                     LEFT JOIN "LOCOSOFT"."dbo"."customers_suppliers" [T2] ON [T2]."customer_number" = [T1]."paying_customer"

            WHERE (convert(float, [T3]."time_units") <> 0.0)) [D2]) [D1]
WHERE ("Invoice Date_Invoices" = [Invoice_Date_Invoices_min])
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO
