CODEWITHSUNDEEP
sqlsql-server
AlphaNERD
AlphaNERD

Reputation: 385

SQL WHERE statement with IF clause

I am creating a table that stores cars entering the parking lot and also leaving the parking lot. I'm working on a software that handles these cars and removes them from the list once it's done.

My software interacts with cameras that have license plate recognition on board, so it's possible that I don't get a pair of datasets with the car entering and leaving the parking lot. I want to create a view for these datasets.

The view has to list rows of cars that have entered the parking lot (Fahrtrichtung = 0) but didn't leave it afterwards and it also has to lists rows of cars that left the parking lot (Fahrtrichtung = 1) but (according to the database) didn't enter the parking lot previously. I also want the script to only return datasets that are from the previous day and older to make sure that I only get the cars that my software couldn't match and not just cars that are really just still on the parking lot.

This is my sample data set: List of all cars that entered or left the parking lot

And this is my desired result data set: Desired results

And this is the current script for the view:

SELECT id, ParkplatzId, Kennzeichen, Zeitpunkt, EingebuchtInMietservice, Foto, Fahrtrichtung, IstValidiert
FROM dbo.Fahrzeuge AS CurrentTable
WHERE (NOT EXISTS
    (    SELECT id, ParkplatzId, Kennzeichen, Zeitpunkt, EingebuchtInMietservice, Foto, Fahrtrichtung, IstValidiert
        FROM dbo.Fahrzeuge AS PreviousTable
        WHERE   (CurrentTable.Kennzeichen = Kennzeichen) AND (CurrentTable.Fahrtrichtung = 1) AND (Zeitpunkt > CurrentTable.Zeitpunkt)
    OR
                (CurrentTable.Kennzeichen = Kennzeichen) AND (CurrentTable.Fahrtrichtung = 0) AND (CurrentTable.Zeitpunkt > Zeitpunkt)))

This is the only script i came up with that SQL didn't complain about. I noticed that I forgot to add the date limitation. However it does not as intended anyway and I hope that you can help me make that script work.

Upvotes: 0

Views: 67

Answers (1)

SQLpro
SQLpro

Reputation: 5161

I think that this query will give you all the differences :

WITH
T1 AS
(
SELECT *, LAG(Zeitpunkt) OVER(PARTITION BY Kennzeichen ORDER BY Zeitpunkt) AS LAST_MOVMENT 
FROM   T
)
SELECT * 
FROM   T1 
       LEFT OUTER JOIN T1 AS T2 
          ON T1.Kennzeichen = T2.Kennzeichen 
          AND T2.LAST_MOVMENT = T1.Zeitpunkt
          AND T1.Fahrtrichtung = T2.Fahrtrichtung

Upvotes: 0

Related Questions