I have a following table (view)
+-------+-------------+-------------+
| Data | Date | Time |
+-------+-------------+-------------+
| Data1 | 2020-08-19 | 13:00:00 |
| Data1 | 2020-08-19 | 13:30:00 |
| Data1 | 2020-08-19 | 14:00:00 |
| Data1 | 2020-08-21 | 07:00:00 |
| Data1 | 2020-08-21 | 07:30:00 |
| Data2 | 2020-08-20 | 08:00:00 |
| Data2 | 2020-08-20 | 08:30:00 |
+-------+-------------+-------------+
I am trying to find out a way to create an SQL statement that would do the following: merge data with consecutive date and time (30 mins interval)
From my basic to early intermediate competence in SQL, I fail to use correctly the GROUP BY. Looking forward for answers or directions. Many thanks again
The result should be:
+-------+-------------+-------------+
| Data | Date | Time |
+-------+-------------+-------------+
| Data1 | 2020-08-19 | 13:00:00 |
| Data1 | 2020-08-21 | 07:00:00 |
| Data2 | 2020-08-20 | 08:00:00 |
+-------+-------------+-------------+
With LAG()
window function:
select Data, Date, Time
from (
select *,
concat(Date, ' ', Time) - interval 30 minute <=
lag(concat(Date, ' ', Time)) over (partition by Data order by Date, Time) flag
from tablename
) t
where coalesce(flag, 0) = 0
order by Data, Date, Time
See the demo.
Results:
> Data | Date | Time
> :---- | :--------- | :-------
> Data1 | 2020-08-19 | 13:00:00
> Data1 | 2020-08-21 | 07:00:00
> Data2 | 2020-08-20 | 08:00:00