SQL DYNAMIC PIVOT - WITH CONDITIONS

Question

I have the following query that returns a car reg number together with the date it was seen and the camera that saw it:

SELECT PLATE_READ.RegNumber, PLATE_READ.DateSeen, PLATE_READ.CameraId FROM PLATE_READ

REG         DATESEEN               CAMERAID
===         ========               ========
5897HHS     20/12/2013 12:10:57    2
5897HHS     20/12/2013 12:05:03    1
G2412       02/07/2014 13:22:01    1
G2412       02/07/2014 13:24:05    2
G6981V      02/07/2014 14:25:08    1

I would like to be able to query and have 1 row returned per registration number stating the dates it has been seen per camera with the following format:

REG         1                    2        
===         ===================  ===================
G2412       02/07/2014 13:22:01  02/07/2014 13:24:05   
5897HHS     20/12/2013 12:05:03  20/12/2013 12:10:57 

The result should be ordered by descending date where cameraid is 1. And the dateseen of cameraid 1 must be less than cameraid 2.

Note that the same plate could have been seen by camera 1 and camera 2 many times, however the last entries must be used only for both.

I have tried several threads but no luck.

Answer 1

As I understand it the criteria is:

• Reg must have been seen by both cameras
• Camera 2 time must be after camera 1 time.

I am also assuming based on previous questions you are using SQL Server.

The way I would approach this may slightly counter intuitive, but I would find the latest sighting by camera 2 for each reg, then find the sighting by camera to 1 prior to that:

SELECT  C2.Reg, C1.[1], C2.[2]
FROM    (   SELECT  Reg, [2] = MAX(DateSeen)
            FROM    T
            WHERE   CameraID = 2
            GROUP BY T.Reg
        ) AS C2
        CROSS APPLY
        (   SELECT  TOP 1 [1] = DateSeen
            FROM    T
            WHERE   T.Reg = C2.Reg
            AND     T.DateSeen < C2.[2]
            ORDER BY T.DateSeen DESC
        ) AS C1
ORDER BY C1.[1] DESC;

Example on SQL Fiddle

Although I have potentially over complicated this, this will still show a vehicle if it was seen by camera 1 last, but display the time it was seen by camera 1 previously, i.e. with this data

REG         DATESEEN               CAMERAID
===         ========               ========
5897HHS     20/12/2013 12:15:03    1
5897HHS     20/12/2013 12:10:57    2
5897HHS     20/12/2013 12:05:03    1

the result would be:

REG         1                       2
===         ========               ========
5897HHS     20/12/2013 12:05:03     20/12/2013 12:10:57

So ignoring the latest row for camera 1 because it was not seen by camera 2 after. If you want to just completely remove this vehicle from the results then you can use a much simpler PIVOT query:

SELECT  pvt.Reg, pvt.[1], pvt.[2]
FROM    T
        PIVOT 
        (   MAX(DateSeen)
            FOR CameraID IN ([1], [2])
        ) AS pvt
WHERE   pvt.[2] > pvt.[1]
ORDER BY pvt.[1] DESC;

Example on SQL Fiddle