jeudi 5 août 2021

SQL: QUERY, IF Statement

I've got the following table with 3 columns

date_actual vehicle_ID Parked
2021-06-27 1234 0
2021-06-28 1234 0
2021-06-29 1234 0
2021-06-30 1234 1
2021-07-01 1234 1
2021-07-02 1234 1
2021-07-03 1234 1
2021-07-04 1234 1
2021-07-05 1234 0
2021-07-06 1234 0
2021-07-07 1234 0
2021-06-27 5555 0
2021-06-28 5555 1

Basically a table containing dates, vehicle_ID's and a third column that tells whether or not a car was in a compound (Parked). I need create a fourth column to this table called: Inflow. Inflow needs to give me the following output:

If a car is parked on date x, but was not parked on date x - 1, it should have inflow = 1 If the first date of a car that is available in the table already starts with a 1, inflow should be 0. Hope you guys can help me out.

I tried:

select t.*,
(case when coalesce(lag(Parked) over (partition by vehicle_ID order by date_actual), parked) = parked - 1
then 'true' else 'false'
end) as inflow from t;

With kind regards,

Lazzanova

Aucun commentaire:

Enregistrer un commentaire