| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475 |
- 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",
- '' as "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 c26
- from
- (select T2."mechanic_no" as "Mechanic No_Labours",
- T1."order_number" as "Order Number",
- T5."end_time" as "End Time",
- T5."start_time" as "Start Time",
- T4."cancelation_date" as "Cancelation Date",
- T4."cancelation_number" as "Cancelation Number",
- T4."is_canceled" as "Is Canceled",
- (T5."exact_duration_seconds" / 60) / 60 as "ben. Std.",
- T5."exact_duration_seconds" / 60 as "Duration Minutes_Times",
- T4."invoice_date" as "Invoice Date_Invoices",
- (rtrim((((T3."employee_number"))))) + ' - ' + T3."name" as "Monteur",
- T3."name" as "Name_Employee",
- T3."employee_number" as "Employee Number_Employee",
- '0' + (rtrim((((T3."subsidiary"))))) as "Subsidiary_Labours",
- T2."order_number" as "Order Number_Labours"
- from "LOCOSOFT"."dbo"."times" T5,
- ("dbo"."invoices" T4 left outer join (("dbo"."order_positions" T1 left outer join "dbo"."labours" T2 on T1."order_number" = T2."order_number") left outer join "dbo"."employees" T3 on T2."mechanic_no" = T3."employee_number") on (T2."invoice_number" = T4."invoice_number") and (T2."invoice_type" = T4."invoice_type"))
- where ((T2."order_number" = T5."order_number") and (T2."mechanic_no" = T5."employee_number"))
- and (((convert(float, T2."time_units"))) <> 0)
- ) D2
- ) D1
- where (c26 = "Invoice Date_Invoices")
- -- order by "Order Number" asc,"Mechanic No_Labours" asc,"Start Time" asc
|
