Skip to content
Advertisement

Query: generated start_date and end_date where it isn’t exist in the table

i want to ask something, so i have a table A that have start_date and end_date column to store the data when there is a failure happened, it look like this

rows start_date end_date
1 “2021-08-01 00:04:00” “2021-08-01 02:54:00”
2 “2021-08-01 04:52:00” “2021-08-01 05:32:00”

And what i want to do is to have a query so that the response will come out like this (for example i want to get the data of 1 August 2021), it fill a row between start_date and end_date that doesn’t exist in table and fill type as normal and otherwise fill it as failure

rows start_date end_date type
1 “2021-08-01 00:00:00” “2021-08-01 00:03:00” normal
2 “2021-08-01 00:04:00” “2021-08-01 02:54:00” failure
3 “2021-08-01 02:55:00” “2021-08-01 04:51:00” normal
4 “2021-08-01 04:52:00” “2021-08-01 05:32:00” failure
5 “2021-08-01 05:53:00” “2021-08-01 23:59:00” normal

Thanks in advance!

Advertisement

Answer

We create dates between and unpivot them using union all. Then we add the last line and do the same.

rows start_date end_date type
1 2021-08-01 00:00:00 2021-08-01 00:03:00 normal
2 2021-08-01 00:04:00 2021-08-01 02:54:00 failure
3 2021-08-01 02:55:00 2021-08-01 04:51:00 normal
4 2021-08-01 04:52:00 2021-08-01 05:32:00 failure
5 2021-08-01 05:33:00 2021-08-01 23:59:00 normal

Fiddle

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement