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