USE DZINDZIO_TRUCKS_MANAGEMENT_ENGLISH
GO
BEGIN TRANSACTION
INSERT INTO TRUCK_MILLEAGE (TruckId, TruckMilleageDate, TruckMilleage)
SELECT (SELECT TruckId FROM Truck WHERE TruckNumber = '2540'), ('2018-12-01'), (579284)
INSERT INTO MAINTENANCE_REPAIRS (TruckId, TruckMilleageId, MaintenanceRepairTypeId, ShopId, Details, LaborCost)
SELECT (SELECT TruckId FROM Truck WHERE TruckNumber = '2540')
, (SELECT TruckMilleageId FROM Truck_MILLEAGE WHERE TruckMilleage = 579284 AND TruckMilleageDate = '2018-12-01')
, (SELECT MaintenanceRepairsTypeId FROM MAINTENANCE_REPAIRS_TYPE WHERE MaintenanceRepairsType = 'Brakes')
, (SELECT ShopId FROM SHOP_LIST WHERE ShopName = 'Camion Lague Dorval')
, (SELECT Details = 'Brake Adjustment')
, (SELECT LaborCost = 48.95)
INSERT INTO TRUCK_MILLEAGE (TruckId, TruckMilleageDate, TruckMilleage)
SELECT (SELECT TruckId FROM Truck WHERE TruckNumber = '2532'), ('2018-12-01'), (98098)
INSERT INTO MAINTENANCE_REPAIRS (TruckId, TruckMilleageId, MaintenanceRepairTypeId, ShopId, Details, LaborCost)
SELECT (SELECT TruckId FROM Truck WHERE TruckNumber = '2532')
, (SELECT TruckMilleageId FROM Truck_MILLEAGE WHERE TruckMilleage = 98098 AND TruckMilleageDate = '2018-12-01')
, (SELECT MaintenanceRepairsTypeId FROM MAINTENANCE_REPAIRS_TYPE WHERE MaintenanceRepairsType = 'Brakes')
, (SELECT ShopId FROM SHOP_LIST WHERE ShopName = 'GloboCam Boucherville')
, (SELECT Details = 'Brake Adjustment')
, (SELECT LaborCost = 48.95)
INSERT INTO TRUCK_MILLEAGE (TruckId, TruckMilleageDate, TruckMilleage)
SELECT (SELECT TruckId FROM Truck WHERE TruckNumber = '25467'), ('2018-12-08'), (563894)
INSERT INTO MAINTENANCE_REPAIRS (TruckId, TruckMilleageId, MaintenanceRepairTypeId, ShopId, Details, LaborCost)
SELECT (SELECT TruckId FROM Truck WHERE TruckNumber = '25467')
, (SELECT TruckMilleageId FROM Truck_MILLEAGE WHERE TruckMilleage = 563894 AND TruckMilleageDate = '2018-12-08')
, (SELECT MaintenanceRepairsTypeId FROM MAINTENANCE_REPAIRS_TYPE WHERE MaintenanceRepairsType = 'Brakes')
, (SELECT ShopId FROM SHOP_LIST WHERE ShopName = 'Dzindzio Shop')
, (SELECT Details = 'Brake Adjustment')
, (SELECT LaborCost = 48.95)
INSERT INTO TRUCK_MILLEAGE (TruckId, TruckMilleageDate, TruckMilleage)
SELECT (SELECT TruckId FROM Truck WHERE TruckNumber = '2540'), ('2018-12-12'), (585029)
INSERT INTO MAINTENANCE_REPAIRS (TruckId, TruckMilleageId, MaintenanceRepairTypeId, ShopId, Details, LaborCost)
SELECT (SELECT TruckId FROM Truck WHERE TruckNumber = '2540')
, (SELECT TruckMilleageId FROM Truck_MILLEAGE WHERE TruckMilleage = 585029 AND TruckMilleageDate = '2018-12-12')
, (SELECT MaintenanceRepairsTypeId FROM MAINTENANCE_REPAIRS_TYPE WHERE MaintenanceRepairsType = 'Frame And Fiftwheel')
, (SELECT ShopId FROM SHOP_LIST WHERE ShopName = 'GloboCam Anjou')
, (SELECT Details = 'Grease')
, (SELECT LaborCost = 97.38)
INSERT INTO MAINTENANCE_REPAIRS (TruckId, TruckMilleageId, MaintenanceRepairTypeId, ShopId, Details, LaborCost)
SELECT (SELECT TruckId FROM Truck WHERE TruckNumber = '2540')
, (SELECT TruckMilleageId FROM Truck_MILLEAGE WHERE TruckMilleage = 585029 AND TruckMilleageDate = '2018-12-12')
, (SELECT MaintenanceRepairsTypeId FROM MAINTENANCE_REPAIRS_TYPE WHERE MaintenanceRepairsType = 'Tires And Budwheels')
, (SELECT ShopId FROM SHOP_LIST WHERE ShopName = 'GloboCam Anjou')
, (SELECT Details = 'Check Tires Wear')
, (SELECT LaborCost = 24.68)
INSERT INTO TRUCK_MILLEAGE (TruckId, TruckMilleageDate, TruckMilleage)
SELECT (SELECT TruckId FROM Truck WHERE TruckNumber = '2532'), ('2018-12-08'), (99004)
INSERT INTO MAINTENANCE_REPAIRS (TruckId, TruckMilleageId, MaintenanceRepairTypeId, ShopId, Details, LaborCost)
SELECT (SELECT TruckId FROM Truck WHERE TruckNumber = '2532')
, (SELECT TruckMilleageId FROM Truck_MILLEAGE WHERE TruckMilleage = 99004 AND TruckMilleageDate = '2018-12-08')
, (SELECT MaintenanceRepairsTypeId FROM MAINTENANCE_REPAIRS_TYPE WHERE MaintenanceRepairsType = 'Brakes')
, (SELECT ShopId FROM SHOP_LIST WHERE ShopName = 'Camion Lague Anjou')
, (SELECT Details = 'Brake Adjustment')
, (SELECT LaborCost = 48.95)
SELECT COUNT(*) AS [Brake Ajustment Qty]
FROM MAINTENANCE_REPAIRS
WHERE TruckId = (SELECT TruckId FROM TRUCK WHERE TruckNumber = '2540')
--- AND TruckMilleageId = (SELECT TruckMilleageId FROM TRUCK_MILLEAGE WHERE TruckMilleage = 579284)
AND Details LIKE 'Brake%'
SELECT *
FROM MAINTENANCE_REPAIRS
WHERE Details LIKE 'Brake%'
AND TruckId = (SELECT TruckId FROM TRUCK WHERE TruckNumber = '2540')
;
SELECT *
FROM MAINTENANCE_REPAIRS
WHERE Details LIKE 'Brake%'
--AND TruckId = (SELECT TruckId FROM TRUCK WHERE TruckNumber = '2540')
;
SELECT v.TruckNumber
, x.TruckMilleageDate AS Date
, x.TruckMilleage AS Milleage
, y.MaintenanceRepairsType AS [Maintenance Type]
, w.Details
, w.Note
, w.LaborCost AS [Labor Cost]
, z.ShopName AS [Shop Name]
FROM
dbo.MAINTENANCE_REPAIRS AS w
INNER JOIN dbo.TRUCK AS v ON w.TruckId = v.TruckId
INNER JOIN dbo.SHOP_LIST AS z ON w.ShopId = z.ShopId
INNER JOIN dbo.MAINTENANCE_REPAIRS_TYPE AS y ON w.MaintenanceRepairTypeId = y.MaintenanceRepairsTypeId
INNER JOIN dbo.TRUCK_MILLEAGE AS x ON w.TruckId = x.TruckId
AND w.TruckMilleageId = x.TruckMilleageId
WHERE Details LIKE 'Brake%'
;
SELECT * FROM TRUCK_MAINTENANCE_REPAIRS_V ;
ROLLBACK
Partager