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)
IDReceivedTrailerIDTyre1Tyre2Tyre3Tyre4Tyre5Tyre6
453472014-02-26 13:33:19.4633774-05
448692014-02-11 12:36:41.98751262726L346858296L8873KO048So524K
441342014-01-22 08:47:24.157673913NULLNULLNULLNULLNULLNULL
435412014-01-08 10:18:07.740GE087NULLNULLNULLNULLNULLNULL
451302014-02-19 18:00:36.233ss760038L5698Z5O6l85O78K97l65lO45S
461492014-03-21 09:46:19.72330106-06004110091301607030120421304713
451532014-02-20 16:21:29.36041841-135ZO7l717ZZ7l7337l7367l7Z38ZO80
447522014-02-08 09:07:12.45741446-10NULLNULLNULLNULLNULLNULL
441112014-01-21 14:19:55.4603304-05
433492014-01-02 10:26:12.7636664-12NULLNULLNULLNULLNULLNULL
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[/url], An Alternative (Better?) Method to UNPIVOT[/url].
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 [/url] 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 <> ''