SET @LASTDATE = '2021-08-10 18:30:00';

SELECT * 
FROM
(
SELECT a.registration,a.reading_date AS 'Reading_Date',
  MAX(a.P1) AS 'Wheel1',
  MAX(a.P2) AS 'Wheel2',
  MAX(a.P3) AS 'Wheel3',
  MAX(a.P4) AS 'Wheel4',
  MAX(a.P5) AS 'Wheel5',
  MAX(a.P6) AS 'Wheel6',
  MAX(a.P7) AS 'Wheel7',
  MAX(a.P8) AS 'Wheel8',
  MAX(a.P9) AS 'Wheel9',
  MAX(a.P10) AS 'Wheel10',
  MAX(a.P11) AS 'Wheel11',
  MAX(a.P12) AS 'Wheel12',
  MAX(a.P13) AS 'Wheel13',
  MAX(a.P14) AS 'Wheel14',
  MAX(a.P15) AS 'Wheel15',
  MAX(a.P16) AS 'Wheel16',
  MAX(a.A1) AS 'Axle1',
  MAX(a.A2) AS 'Axle2',
  MAX(a.A3) AS 'Axle3',
  MAX(a.A4) AS 'Axle4',
  a.company_name,
  a.contract_name,
  a.ContractId,
  a.fleet_number,
  a.VehicleId,
  a.ConfigurationId,
  a.ConfigurationName,
  a.ConfigurationCode
  from
(SELECT vd.registration,tvth.*,
  CASE WHEN tvth.axle=1 AND tvth.wheel=1 THEN tpb.name ELSE NULL END AS 'P1',
  CASE WHEN tvth.axle=1 AND tvth.wheel=2 THEN tpb.name ELSE NULL END AS 'P2',
  CASE WHEN tvth.axle=1 AND tvth.wheel=3 THEN tpb.name ELSE NULL END AS 'P3',
  CASE WHEN tvth.axle=1 AND tvth.wheel=4 THEN tpb.name ELSE NULL END AS 'P4',
  CASE WHEN tvth.axle=2 AND tvth.wheel=1 THEN tpb.name ELSE NULL END AS 'P5',
  CASE WHEN tvth.axle=2 AND tvth.wheel=2 THEN tpb.name ELSE NULL END AS 'P6',
  CASE WHEN tvth.axle=2 AND tvth.wheel=3 THEN tpb.name ELSE NULL END AS 'P7',
  CASE WHEN tvth.axle=2 AND tvth.wheel=4 THEN tpb.name ELSE NULL END AS 'P8',
  CASE WHEN tvth.axle=3 AND tvth.wheel=1 THEN tpb.name ELSE NULL END AS 'P9',
  CASE WHEN tvth.axle=3 AND tvth.wheel=2 THEN tpb.name ELSE NULL END AS 'P10',
  CASE WHEN tvth.axle=3 AND tvth.wheel=3 THEN tpb.name ELSE NULL END AS 'P11',
  CASE WHEN tvth.axle=3 AND tvth.wheel=4 THEN tpb.name ELSE NULL END AS 'P12',
  CASE WHEN tvth.axle=4 AND tvth.wheel=1 THEN tpb.name ELSE NULL END AS 'P13',
  CASE WHEN tvth.axle=4 AND tvth.wheel=2 THEN tpb.name ELSE NULL END AS 'P14',
  CASE WHEN tvth.axle=4 AND tvth.wheel=3 THEN tpb.name ELSE NULL END AS 'P15',
  CASE WHEN tvth.axle=4 AND tvth.wheel=4 THEN tpb.name ELSE NULL END AS 'P16',
  CASE WHEN tvth.Axle=1 AND tvth.ttmTyreSizeDesc IS NOT NULL THEN tvth.ttmTyreSizeDesc ELSE NULL END AS 'A1',
  CASE WHEN tvth.Axle=2 AND tvth.ttmTyreSizeDesc IS NOT NULL THEN tvth.ttmTyreSizeDesc ELSE NULL END AS 'A2',
  CASE WHEN tvth.Axle=3 AND tvth.ttmTyreSizeDesc IS NOT NULL THEN tvth.ttmTyreSizeDesc ELSE NULL END AS 'A3',
  CASE WHEN tvth.Axle=4 AND tvth.ttmTyreSizeDesc IS NOT NULL THEN tvth.ttmTyreSizeDesc ELSE NULL END AS 'A4',
  tcom.company_name,
  tc.contract_name,
  tc.id AS 'ContractId',
  vd.fleet_number,
  vd.id AS 'VehicleId',
  twc.id AS 'ConfigurationId',
  twc.name AS 'ConfigurationName',
  twc.code AS 'ConfigurationCode'
  FROM tbl_vehicle_tyre_histories tvth
  JOIN tbl_vehicle_details vd ON tvth.vehicle_id=vd.id
  JOIN tbl_product_brands tpb ON tvth.product_brand_id = tpb.id
  JOIN tbl_contracts tc ON vd.contract_id = tc.id
  JOIN tbl_companies tcom ON vd.company_id = tcom.id
  JOIN tbl_wheel_configurations twc ON vd.wheel_configuration_id = twc.id
  WHERE tvth.creation_date > @LASTDATE
  AND vd.contract_id IN (3581,3582,3583,3606,3784,3977,4059,4081,4082,4085,4086,4217,4222,4223,4229,4299,4300,5117,5133,5142,5145,5156,5161,5164,5167,5175,5177,5178,5181,5228,5233,5234,5247,5248,5250,5274,5280,5323,5341,5343,5344,5349,5350,5368,5372,5375,5382,5412,5413,5419,5438,5443,5444,5445,5448,5449,5456,5457,5474,5476,5477,5486,5487,5498,5734,5738,5739,5740,5741,5743,5823,5828,5834,5842,5844,5845,5846,5848,5859,5860,5861,5900,5936,5940,5956,5968,5992,5997,6002,6003,6028,6031,6033,6039,6056,6059,6062,6068,6072,6078,6099,6100,6101,6104,6113,6141,6144,6160,6161,6165,6171,6173,6181,6182,6186,6188,6189,6262,6284,6297,6302,6338,6364,6365,6367,6376,6389,6391,6394,6399,6427,6429,6433,6439,6445,6446,6528,6535,6536,6538,6539,6540,6557,6558,6562,6563,6564,6582,6625,6626,6627,6642,6663,6664,6685,6712,6714,6715,6716,6717,6718,6719,6733,6734) 
  AND tvth.vehicle_tyre_intervention_type=4
  AND tvth.organisation_id=5 ) a
  GROUP BY a.registration
UNION
SELECT twi.Registration, twi.LastInspection, 
twi.Wheel1,twi.Wheel2,twi.Wheel3,twi.Wheel4,twi.Wheel5,twi.Wheel6,twi.Wheel7,twi.Wheel8,twi.Wheel9,twi.Wheel10,twi.Wheel11,twi.Wheel12,twi.Wheel13,twi.Wheel14,twi.Wheel15,twi.Wheel16,twi.Axle1,twi.Axle2,twi.Axle3,twi.Axle4,
twi.CustomerName, twi.ContractName, twi.ContractId,twi.FleetNumber, twi.VehicleId, twi.ConfigurationId, twi.ConfigurationName, twi.ConfigurationCode
FROM tbl_wheel_inspections twi
where twi.ContractId IN (3581,3582,3583,3606,3784,3977,4059,4081,4082,4085,4086,4217,4222,4223,4229,4299,4300,5117,5133,5142,5145,5156,5161,5164,5167,5175,5177,5178,5181,5228,5233,5234,5247,5248,5250,5274,5280,5323,5341,5343,5344,5349,5350,5368,5372,5375,5382,5412,5413,5419,5438,5443,5444,5445,5448,5449,5456,5457,5474,5476,5477,5486,5487,5498,5734,5738,5739,5740,5741,5743,5823,5828,5834,5842,5844,5845,5846,5848,5859,5860,5861,5900,5936,5940,5956,5968,5992,5997,6002,6003,6028,6031,6033,6039,6056,6059,6062,6068,6072,6078,6099,6100,6101,6104,6113,6141,6144,6160,6161,6165,6171,6173,6181,6182,6186,6188,6189,6262,6284,6297,6302,6338,6364,6365,6367,6376,6389,6391,6394,6399,6427,6429,6433,6439,6445,6446,6528,6535,6536,6538,6539,6540,6557,6558,6562,6563,6564,6582,6625,6626,6627,6642,6663,6664,6685,6712,6714,6715,6716,6717,6718,6719,6733,6734)) Z
GROUP BY Z.registration
HAVING MAX(Z.Reading_Date);



If user has can report all contracts flagged you will need to select every record.  To do this change the where statement to not use ContractId IN