CODEWITHSUNDEEP
sqlpivotpivot-table
Tommy
Tommy

Reputation: 465

SQL DYNAMIC PIVOT - WITH CONDITIONS

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.

Upvotes: 3

Views: 73

Answers (2)

GarethD
GarethD

Reputation: 69769

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

Upvotes: 2

valex
valex

Reputation: 24144

If I've got it right you need the last (MAXIMUM) time for both 1 and 2 cameras:

    SELECT 
         REG,
         MAX(CASE WHEN CameraID=1 THEN DATESEEN END) as [c1],
         MAX(CASE WHEN CameraID=2 THEN DATESEEN END) as [c2]
    FROM PLATE_READ
    GROUP BY REG
    HAVING MAX(CASE WHEN CameraID=1 THEN DATESEEN END)
           <
           MAX(CASE WHEN CameraID=2 THEN DATESEEN END)
   ORDER BY [c1] DESC

SQLFiddle demo

Upvotes: 0

Related Questions