Filtering SQL rows using column data-CodePudding

I am trying to filter rows in a single table. My goal is to return all license plates that have "entered" a building but have not "exited" the building prior to 11am on July 28, 2021.

Here is the code I have written which of course returns all rows for that day.

SELECT *
FROM bakery_security_logs
WHERE day = '28' and
      month = '7' and
      year = '2021' and
      hour < '11'
ORDER BY month, day, year;

Here is what this code returns:

CodePudding user response：

Solution 1 - Group and compare how many times entered or exited.

You can group by license_plate first, count how many times the vehicle has entered and exited the building. If the building has entered and exited before 11AM, entranceCount and exitCount should be the same.

However, if the vehicle entered on 27 Nov, and left on 28 Nov, this may not work.

SELECT license_plate
FROM (
  SELECT
    license_plate,
    COUNT(CASE WHEN activity = 'entrance' THEN 1 ELSE NULL END) AS entranceCount,
    COUNT(CASE WHEN activity = 'exit' THEN 1 ELSE NULL END) AS exitCount
  FROM bakery_security_logs
  WHERE day = '28' and
        month = '7' and
        year = '2021' and
        hour < '11'
  GROUP BY license_plate
)
WHERE entranceCount > exitCount
-- WHERE entranceCount != exitCount
   -- if exitCount > entranceCount, may need to be alerted too

Solution 2 - Get the latest record and check activity

Get the latest record of each license_plate. If activity = 'entrance', the vehicle is still in the building by 11 AM.

This is more efficient but the syntax may be different, depending on which SQL server you are using.

Additional suggestion

Use DATETIME to record a datetime, it will be helpful for filtering and sorting the data.