Hi

I'm not sure that I have your requirements right, but this may help you with your problem. Basically I am unpivoting your data so I can reorder the tyres in another pivot. This can be used to compare the tyre sets to the next row. I think that there is enough here to get the results that you require?

WITH trailertyredata AS (

SELECT *

FROM (VALUES

(1, CAST('20140110' AS DATETIME), 'ABC123', '12345', '23456', '34567', '45678', '56789', '67890'),

(2, CAST('20140115' AS DATETIME), 'ABC123', '1Z345', '23A56', '34567', '45678', '56789', '67890'),

(3, CAST('20140120' AS DATETIME), 'ABC123', '23456', '34567', '45678', '12345', '56789', '67890'),

(4, CAST('20140125' AS DATETIME), 'ABC123', '1234S', 'Z3456', '345G7', '4S678', '5G789', '67B90'),

(5, CAST('20140110' AS DATETIME), 'XYZ456', '12345', '23456', '34567', '45678', '56789', '67890'),

(6, CAST('20140115' AS DATETIME), 'XYZ456', '1Z345', '23A56', '34567', '45678', '56789', '67890'),

(7, CAST('20140120' AS DATETIME), 'XYZ456', 'Z3456', '1234S', '345G7', '4S678', '56789', '5G789'),

(8, CAST('20140125' AS DATETIME), 'XYZ456', '1234S', 'Z3456', '345G7', '4S678', '5G789', '56789')

) a (ID, Received, TrailerID, Tyre1, Tyre2, Tyre3, Tyre4, Tyre5, Tyre6)

),

-- Unpivot and resort the tyres the data

unpivottyre AS (

SELECT ID, Received, TrailerID, a.Tyre,

ROW_NUMBER() OVER (PARTITION BY ID, TrailerID ORDER BY a.Tyre) TyreRank,

DENSE_RANK() OVER (PARTITION BY TrailerID ORDER BY Received dESC) ReceivedRank

FROM trailertyredata t

CROSS APPLY (SELECT tyre FROM (VALUES(t.Tyre1), (t.tyre2), (t.tyre3), (t.tyre4), (t.tyre5), (t.tyre6)) x(tyre)) a

),

-- pivot the data again with sorted tyres

repivottyre AS (

SELECT ID, Received, TrailerID,

MIN(CASE WHEN TyreRank = 1 THEN Tyre END) Tyre1,

MIN(CASE WHEN TyreRank = 2 THEN Tyre END) Tyre2,

MIN(CASE WHEN TyreRank = 3 THEN Tyre END) Tyre3,

MIN(CASE WHEN TyreRank = 4 THEN Tyre END) Tyre4,

MIN(CASE WHEN TyreRank = 5 THEN Tyre END) Tyre5,

MIN(CASE WHEN TyreRank = 6 THEN Tyre END) Tyre6,

MIN(ReceivedRank) ReceivedRank

FROM unpivottyre

GROUP BY ID, Received, TrailerID

),

-- compare to the next set of tyres based on date

comparetyres AS(

SELECT a.ID, a.Received, a.TrailerID, a.Tyre1, a.Tyre2, a.Tyre3, a.Tyre4, a.Tyre5, a.Tyre6, a.ReceivedRank,

CASE WHEN a.tyre1 = b.tyre1 and a.tyre2 = b.tyre2 and a.tyre3 = b.tyre3 and a.tyre4 = b.tyre4 and a.tyre5 = b.tyre5 and a.tyre6 = b.tyre6 THEN

1

ELSE

0

END SameAsNext

FROM repivottyre a

LEFT OUTER JOIN repivottyre b ON a.ReceivedRank = b.ReceivedRank + 1 AND a.TrailerID = b.TrailerID

)

select ID, Received, TrailerID, Tyre1, Tyre2, Tyre3, Tyre4, Tyre5, Tyre6

from comparetyres

where ReceivedRank = 1 or

(ReceivedRank = 2 and SameAsNext = 0);Edit: Added some more data