Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Interesting SQL challenge - compare differences in cells and rows Expand / Collapse
Author
Message
Posted Monday, March 31, 2014 4:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:21 PM
Points: 4, Visits: 8
I have a table... below is a limited version of what it looks like...

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

1, 10/1/2014, ABC123, 12345, 23456, 34567, 45678, 56789, 67890
2, 15/1/2014, ABC123, 1Z345, 23A56, 34567, 45678, 56789, 67890 // Note tyre 1 and 2 are different
3, 20/1/2014, ABC123, 23456, 34567, 45678, 12345, 56789, 67890 // Note tyre 4 has moved from tyre 1, but is same set as first record
4, 25/1/2014, ABC123, 1234S, Z3456, 345G7, 4S678, 5G789, 67B90 // Note, ALL tyres appear to be different (got letters instead of numbers in some cases)

What I need to find is...

Latest tyre (record 4) and previous tyre (record 3) only, based on the trailer IDs. Also... ONLY if the tyres are different... however, transposed columns do NOT make a tyre different as the set is still the same (i.e. record 3 is technically the same as record 1, and if only those two records were in the set, neither would appear in my result set, as the tyres are identical)

The difference between record 4 and record 3 need only be just one character across all 6 tyre columns, which will make it different, but as mentioned before, purely transposed columns are not a difference.

If the previous record has no tyres, then keep going backwards (just back up to 1/1/2014 as that is when the recording of this data started) and if no previous, I don't need to know about the record.

In my result set, I don't need to see the earlier tyre... just the newest tyre. I will be picking up the earlier later in the process.

I have spent ages trying to work this out. I know it is a challenge and I would be willing to accept ideas which may point me to a solution to making this work. I don't really want this to be heavy on the server, but I fear it might be.

Thank you for reading this far.
Post #1556761
Posted Monday, March 31, 2014 6:21 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 2:45 PM
Points: 1,080, Visits: 3,170
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
Post #1556776
Posted Tuesday, April 1, 2014 12:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:21 PM
Points: 4, Visits: 8
Wow!!!

That is some sql.

I will give it a go and report back. Thank you very much for your help.
Post #1556820
Posted Tuesday, April 1, 2014 2:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:21 PM
Points: 4, Visits: 8
Thank you again for your help... though as yet, I am trying to understand what you have done so that I can better apply to my situation.

I have plugged the query into Management Studio, changed the sample data to a select statement to select my real data and I have the following...
(I have over 2300 records with this)

ID Received TrailerID Tyre1 Tyre2 Tyre3 Tyre4 Tyre5 Tyre6
45347 2014-02-26 13:33:19.463 3774-05
44869 2014-02-11 12:36:41.987 5126 2726L 34685 8296L 8873K O048S o524K
44134 2014-01-22 08:47:24.157 673913 NULL NULL NULL NULL NULL NULL
43541 2014-01-08 10:18:07.740 GE087 NULL NULL NULL NULL NULL NULL
45130 2014-02-19 18:00:36.233 ss76 0038L 5698Z 5O6l8 5O78K 97l65 lO45S
46149 2014-03-21 09:46:19.723 30106-06 00411 00913 01607 03012 04213 04713
45153 2014-02-20 16:21:29.360 41841-13 5ZO7l 717ZZ 7l733 7l736 7l7Z3 8ZO80
44752 2014-02-08 09:07:12.457 41446-10 NULL NULL NULL NULL NULL NULL
44111 2014-01-21 14:19:55.460 3304-05
43349 2014-01-02 10:26:12.763 6664-12 NULL NULL NULL NULL NULL NULL

As you can see, I have some null and some empty tyres. I think I will need to just validate these have an earlier record with data, so if they do, then we might have cracked this.

I don't (yet) understand pivot / unpivot or fully what you have done... so if you have the time to expand on it a little, it would be appreciated.

Thank you.
Post #1557201
Posted Tuesday, April 1, 2014 2:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:21 PM
Points: 4, Visits: 8
Hi,

Sorry, I know it's bad form to keep replying to my own posts...

to validate the data received, I have done a regular call with sql...

select ID, TrailerID, Received, Tyre1, Tyre2, Tyre3, Tyre4, Tyre5, Tyre6
from Trailers
where TrailerID = '5126'
and Received > '1 January 2014'
order by ID desc

I only have one record...
ID TrailerID Received Tyre1 Tyre2 Tyre3 Tyre4 Tyre5 Tyre6
44869 5126 2014-02-11 12:36:41.987 2726L O048S 8873K 34685 8296L o524K

I should only be displaying a record if there is a previous record with something in the tyres.

Thanks again for your help.
Post #1557215
Posted Tuesday, April 1, 2014 3:12 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 2:45 PM
Points: 1,080, Visits: 3,170
sqlservercentral 18091 (4/1/2014)
Thank you again for your help... though as yet, I am trying to understand what you have done so that I can better apply to my situation.

I have plugged the query into Management Studio, changed the sample data to a select statement to select my real data and I have the following...
(I have over 2300 records with this)

ID Received TrailerID Tyre1 Tyre2 Tyre3 Tyre4 Tyre5 Tyre6
45347 2014-02-26 13:33:19.463 3774-05
44869 2014-02-11 12:36:41.987 5126 2726L 34685 8296L 8873K O048S o524K
44134 2014-01-22 08:47:24.157 673913 NULL NULL NULL NULL NULL NULL
43541 2014-01-08 10:18:07.740 GE087 NULL NULL NULL NULL NULL NULL
45130 2014-02-19 18:00:36.233 ss76 0038L 5698Z 5O6l8 5O78K 97l65 lO45S
46149 2014-03-21 09:46:19.723 30106-06 00411 00913 01607 03012 04213 04713
45153 2014-02-20 16:21:29.360 41841-13 5ZO7l 717ZZ 7l733 7l736 7l7Z3 8ZO80
44752 2014-02-08 09:07:12.457 41446-10 NULL NULL NULL NULL NULL NULL
44111 2014-01-21 14:19:55.460 3304-05
43349 2014-01-02 10:26:12.763 6664-12 NULL NULL NULL NULL NULL NULL

As you can see, I have some null and some empty tyres. I think I will need to just validate these have an earlier record with data, so if they do, then we might have cracked this.

I don't (yet) understand pivot / unpivot or fully what you have done... so if you have the time to expand on it a little, it would be appreciated.

Thank you.

On the unpivot and pivot, here's a couple of articles about them: Cross Tabs and Pivots, Part 1, An Alternative (Better?) Method to UNPIVOT.

The reason I unpivoted your data was to allow me to to resort the tyres consistently before pivoting them back. This allowed me to compare the rows easily.
While unpivoting the data I also ranked the trailers based on descending date. This could have been done separately to the unpivot.

The compare part of the CTE checks the trailer tyres against the next row and flags if they are the same or not.

I wasn't sure exactly what result you wanted to see, so I went with show the latest set of tryes for the trailer and the previous set if they differ.
If this wasn't what you want, have a read of this article and post a set of data with expected results that demonstrates each of the criteria.

You could filter out the records by adding the following where clause onto the unpivot CTE.
	WHERE Tyre1 is not null and tyre1 <> ''

Post #1557229
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse