'SQL- Return less than 24 hour or 1 day difference between 2 dates values using self join

Logic: If an admission date is not reported on all claims, combine claims for the same beneficiary and provider that have less than a one-day break between the end date of the first claim and the start date of the next claim. For example, if the end date of the first claim is December 18 and the start date of the next claim is December 19, then combine the claims as a single stay. However, if the second claim has a start date of December 20 or later, then do not combine the claims.

I am working with MS SQL server and since it want to combine claims for less than one day break, I prefer to delete all but one record with the same beneficiaries.

The database is denormalized with one table and 52 fields so I prefer not to left or right join because it would double record. I watched this Youtube video https://www.youtube.com/watch?v=Ip6Ty2qmQXg and it shows how to self-join without doubling the records.

Given that, this is what I have came up so far. Since the question said to combine claims I want to delete the "duplicate" records but I do not know how to delete after self join. See the code below

-- self join 
SELECT * 
FROM #Metric2InpatientNoDischargeNoAdmission BEN1, #Metric2InpatientNoDischargeNoAdmission BEN2
WHERE BEN1.BEN_ID = BEN2.BEN_ID
AND BEN1.PERF_PROV_KEY = BEN2.PERF_PROV_KEY
AND BEN1.DTE_FIRST_SVC < BEN2.DTE_FIRST_SVC
AND DATEDIFF(day, BEN1.DTE_LAST_SVC, BEN2.DTE_FIRST_SVC) > 1 )

Edit: I ran the above code and it returned more rows than the original table so this code is obviously not right. Help!!! :(



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source