Say I have a table name CustomerBooking which contains Customers, checkInDate and checkOutDate as follows. I would like to achieve the outcome as shown in condition 1 and condition 2 with update statement.
I have tried some solutions online but to no avail that it does not include NULL as infinite date in overlapping (for condition 1) and/or only takes the max date of overlapping date into account instead of taking max date from last row of overlapping dates. (for condition 2).
Do take in mind that condition 1 and condition 2 are two independent separate outcome that should be achieved with update statement based on the first table.
ID CustomerID CheckInDate CheckOutDate
1 1 2015-04-02 2015-04-05
2 2 2015-03-04 2015-05-02
3 1 2015-04-01 NULL(which represents infinite date)
4 1 2015-03-04 2015-05-03
5 1 2015-01-03 2015-02-03
- Condition 1: Merge the overlapping date of same member ID (take earliest start date and max end date of overlapping dates) and update the table.
Note: Row with ID 1, ID 3 and ID 4 has overlapped dates. Therefore we combine these 3 rows to CheckInDate(2015-03-04) and CheckOutDate(NULL) by taking the min date and max date of overlapping dates. Only row with ID 1 is updated with the new dates and remaining ID 3 and ID 4 date is set to null. Rows with ID 2 and ID 5 do not have overlapping dates of same member ID therefore it remains as it is.
Final result is as shown below:
ID CustomerID CheckInDate CheckOutDate
1 1 2015-03-04 NULL
2 2 2015-03-04 2015-05-02
3 1 NULL NULL
4 1 NULL NULL
5 1 2015-01-03 2015-02-03
- Condition 2: Merge the overlapping date of same member ID (take earliest start date and last end date(largest ID) of overlapping dates) and update the table.
Similar as condition 1, but this time we take the min date of overlapping dates (2015-03-04) and the check out date of the last row (row with ID 4) of overlappings date (which if 2015-05-03).
Final result is as shown below:
ID CustomerID CheckInDate CheckOutDate
1 1 2015-03-04 2015-05-03
2 2 2015-03-04 2015-05-02
3 1 NULL NULL
4 1 NULL NULL
5 1 2015-01-03 2015-02-03
2015-03-14
come from?