Changes between Version 9 and Version 10 of UseCaseModel/ShipmentDetails

Timestamp:

12/28/24 21:11:08 (3 weeks ago)

Author:

153093

Comment:

--

UseCaseModel/ShipmentDetails

@@ -19 +19 @@
 {{{
 SELECT 
-    s.shipment_id,
-    p.payment_status,
-    CONCAT(e.employee_name, ' ', e.employee_surname) AS employee_full_name,
-    CONCAT(v.make, ' ', v.model, ' (', v.registration, ')') AS vehicle_info,
-    s.shipment_date,
-    Count(distinct sl.customer_id) as no_of_customers,
-    SUM(sl.wine_quantity) as wine_quantity_shipped,
-    SUM(sl.wine_base_price * sl.wine_quantity) AS shipment_base_price,
-    SUM(sl.wine_agreed_price * sl.wine_quantity) AS shipment_agreed_price,
-    COALESCE(ex.shipment_expenses, 0) as shipment_expenses,
-    (SUM(sl.wine_agreed_price * sl.wine_quantity) - SUM(sl.wine_base_price * sl.wine_quantity) - COALESCE(ex.shipment_expenses, 0)) as shipment_profit
+    s.shipment_id AS ShipmentId,
+    p.payment_status AS PaymentStatus,
+    CONCAT(e.employee_name, ' ', e.employee_surname) AS EmployeeFullName,
+    CONCAT(v.make, ' ', v.model, ' (', v.registration, ')') AS VehicleInfo,
+    TO_CHAR(s.shipment_date, 'DD-MM-YYYY') AS ShipmentDate,
+    COUNT(DISTINCT sl.customer_id) AS NumberOfCustomers,
+    SUM(sl.wine_quantity) AS WineQuantityShipped,
+    SUM(sl.wine_base_price * sl.wine_quantity) AS ShipmentBasePrice,
+    SUM(sl.wine_agreed_price * sl.wine_quantity) AS ShipmentAgreedPrice,
+    COALESCE(ex.shipment_expenses, 0) AS ShipmentExpenses,
+    (SUM(sl.wine_agreed_price * sl.wine_quantity) - SUM(sl.wine_base_price * sl.wine_quantity) - COALESCE(ex.shipment_expenses, 0)) AS ShipmentProfit
 FROM shipment s
 JOIN payment p ON s.payment_id = p.payment_id
@@ -35 +35 @@
 JOIN vehicle v ON s.vehicle_id = v.vehicle_id
 JOIN shipment_load sl ON s.shipment_id = sl.shipment_id
-JOIN ( SELECT shipment_id, SUM(amount) AS shipment_expenses FROM shipment_has_expense_of_expense_type GROUP BY shipment_id ) ex ON s.shipment_id = ex.shipment_id
-WHERE s.shipment_id = 5
+LEFT JOIN (SELECT shipment_id, SUM(amount) AS shipment_expenses FROM shipment_has_expense_of_expense_type GROUP BY shipment_id) ex ON s.shipment_id = ex.shipment_id
+WHERE s.shipment_id = 8
 GROUP BY 
     s.shipment_id, 
@@ -45 +45 @@
     v.model, 
     v.registration, 
-    s.shipment_date,
+    s.shipment_date, 
     ex.shipment_expenses;
 }}}