| 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 c31 as "Order Number",
- '' as "Labour Type",
- '' as "Code_Labour_Types",
- '' as "Description_Labour_Types",
- c44 as "Order Number_Labours",
- c43 as "Subsidiary_Labours",
- 0 as "Invoice Type_Labours",
- c30 as "Mechanic No_Labours",
- 10 as "AW/Std.",
- 'ben. Std.' as "Zeitkategorie",
- '' as "Zeitkategorie2",
- c42 as "Employee Number_Employee",
- c41 as "Name_Employee",
- c40 as "Monteur",
- c39 as "Invoice Date_Invoices",
- c39 as "Datum",
- c38 as "Duration Minutes_Times",
- c37 as "ben. Std.",
- 0 as "Invoice Number",
- c36 as "Is Canceled",
- c35 as "Cancelation Number",
- c34 as "Cancelation Date",
- c33 as "Start Time",
- c32 as "End Time",
- MIN(c39) OVER (partition by c31,c30) as c26
- from
- (select T2."mechanic_no" as c30,
- T1."order_number" as c31,
- T5."end_time" as c32,
- T5."start_time" as c33,
- T4."cancelation_date" as c34,
- T4."cancelation_number" as c35,
- T4."is_canceled" as c36,
- (T5."exact_duration_seconds" / 60) / 60 as c37,
- T5."exact_duration_seconds" / 60 as c38,
- T4."invoice_date" as c39,
- (rtrim((convert(varchar(50), T3."employee_number")))) + ' - ' + T3."name" as c40,
- T3."name" as c41,
- T3."employee_number" as c42,
- '0' + (rtrim((convert(varchar(50), T3."subsidiary")))) as c43,
- T2."order_number" as c44
- 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
|
