I have following table:
| person | arrival | departure |
|---|---|---|
| John | 01/01/2022 | 04/01/2022 |
| Ana | 03/01/2022 | 04/01/2022 |
| Ronald | 04/01/2022 | NULL |
And I should get something like:
| date | new_arrived | new_departured |
|---|---|---|
| 01/01/2022 | 1 | 0 |
| 03/01/2022 | 1 | 0 |
| 04/01/2022 | 1 | 2 |
Could anyone help me? Thank you in advance!
My idea would be:
with temp1 as(
SELECT arrival FROM original_table
WHERE arrival is not null
UNION ALL
SELECT departure FROM original_table
WHERE departure is not null)
SELECT temp1.arrival as date,
(SELECT Count(arrival) FROM original_table WHERE arrival=date),
( SELECT Count(departure) FROM original_table WHERE departure=date)
FROM temp1
GROUP BY temp1.arrival;