I am using SQL Server 2014 and I am working with a table named ReservationStay. It contains the records of all guests with their names, arrival dates and departure dates. An operation has been undertaken that has split the records of hundreds of guests into 2 separate entries, which means that these entries now have the same Guest name but with different arrival dates and departure dates.
An example of an original entry:
Name ArrivalDate DepartureDate
Simon G 2015-06-01 2015-06-08
Here is what happened after that split operation was effected, say, on 2015-06-03:
Name ArrivalDate DepartureDate
Simon G 2015-06-01 2015-06-03
Simon G 2015-06-03 2015-06-08
This split operation was carried out on several days.
I need a filter in my query that will take into account the following:
WHERE Name is a duplicate and the DepartureDate of the first entry = the ArrivalDate of the second entry.
Basically, I want to re-construct the original entry.
How do I write this filter?
You can use
LEAD
,LAG
window functions in order to locate records that have been split:This query will give you back the original version of your table.
Demo here