SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE MOCK_DATA_CLAIM  (
  "BEN_ID" uniqueidentifier,
  "CLAIM_ID" uniqueidentifier,
  "DTE_ADMISSION" date,
  "DTE_DISCHARGE" date
);

SET DATEFORMAT MDY;

INSERT INTO MOCK_DATA_CLAIM 
  ("BEN_ID", "CLAIM_ID", "DTE_ADMISSION", "DTE_DISCHARGE")
VALUES
  ('C0369B1C-0197-4C43-B048-1E299767C2E8', '5685638D-412D-4D2E-9BEF-AF4C2A18F885', '7/10/18', '7/24/18'),
  ('C0369B1C-0197-4C43-B048-1E299767C2E8', 'E65085AF-DAD9-4AC4-9AA6-A887D3606571', '7/24/2018', '7/24/2018'),
  ('C0369B1C-0197-4C43-B048-1E299767C2E8', 'E8859A25-11D0-416F-BC1C-048451F14B4C', '7/24/2018', '8/2/2018'),
  ('C0369B1C-0197-4C43-B048-1E299767C2E8', '6608CF05-B135-40A2-9DBE-7CD3CF4F804E', '9/27/2018', '10/4/2018'),
  ('C0369B1C-0197-4C43-B048-1E299767C2E8', '4A380669-F5F6-4A62-8A44-8F53C2A6068D', '10/4/2018', '10/8/2018');
  
5 rows affected

WITH Prev AS (
    SELECT *,
      PrevDischarge = LAG(DTE_DISCHARGE, 1, '19000101') OVER (PARTITION BY BEN_ID ORDER BY DTE_ADMISSION)
    FROM MOCK_DATA_CLAIM
),
Counts AS (
    SELECT *,
      GroupId = COUNT(CASE WHEN PrevDischarge < DATEADD(day, -2, DTE_ADMISSION) THEN 1 END) OVER (PARTITION BY BEN_ID ORDER BY DTE_ADMISSION ROWS UNBOUNDED PRECEDING)
    FROM Prev
),
MinMax AS (
    SELECT *,
      Min_ADMISSION = MIN(DTE_ADMISSION) OVER (PARTITION BY GroupId),
      Max_DISCHARGE = MAX(DTE_DISCHARGE) OVER (PARTITION BY GroupId)
    FROM Counts
)
SELECT *
FROM MinMax;


BEN_ID
CLAIM_ID
DTE_ADMISSION
DTE_DISCHARGE
PrevDischarge
GroupId
Min_ADMISSION
Max_DISCHARGE


c0369b1c-0197-4c43-b048-1e299767c2e8
5685638d-412d-4d2e-9bef-af4c2a18f885
2018-07-10
2018-07-24
1900-01-01
1
2018-07-10
2018-08-02

c0369b1c-0197-4c43-b048-1e299767c2e8
e65085af-dad9-4ac4-9aa6-a887d3606571
2018-07-24
2018-07-24
2018-07-24
1
2018-07-10
2018-08-02

c0369b1c-0197-4c43-b048-1e299767c2e8
e8859a25-11d0-416f-bc1c-048451f14b4c
2018-07-24
2018-08-02
2018-07-24
1
2018-07-10
2018-08-02

c0369b1c-0197-4c43-b048-1e299767c2e8
6608cf05-b135-40a2-9dbe-7cd3cf4f804e
2018-09-27
2018-10-04
2018-08-02
2
2018-09-27
2018-10-08

c0369b1c-0197-4c43-b048-1e299767c2e8
4a380669-f5f6-4a62-8a44-8f53c2a6068d
2018-10-04
2018-10-08
2018-10-04
2
2018-09-27
2018-10-08


Warning: Null value is eliminated by an aggregate or other SET operation.


WITH Prev AS (
    SELECT *,
      PrevDischarge = LAG(DTE_DISCHARGE, 1, '19000101') OVER (PARTITION BY BEN_ID ORDER BY DTE_ADMISSION)
    FROM MOCK_DATA_CLAIM
),
Counts AS (
    SELECT *,
      GroupId = COUNT(CASE WHEN PrevDischarge < DATEADD(day, -2, DTE_ADMISSION) THEN 1 END) OVER (PARTITION BY BEN_ID ORDER BY DTE_ADMISSION ROWS UNBOUNDED PRECEDING)
    FROM Prev
),
MinMax AS (
    SELECT *,
      Min_ADMISSION = MIN(DTE_ADMISSION) OVER (PARTITION BY GroupId),
      Max_DISCHARGE = MAX(DTE_DISCHARGE) OVER (PARTITION BY GroupId)
    FROM Counts
)
UPDATE MinMax
SET DTE_ADMISSION = Min_ADMISSION,
    DTE_DISCHARGE = Max_DISCHARGE;
Warning: Null value is eliminated by an aggregate or other SET operation.
5 rows affected

SELECT * FROM MOCK_DATA_CLAIM


BEN_ID
CLAIM_ID
DTE_ADMISSION
DTE_DISCHARGE


c0369b1c-0197-4c43-b048-1e299767c2e8
5685638d-412d-4d2e-9bef-af4c2a18f885
2018-07-10
2018-08-02

c0369b1c-0197-4c43-b048-1e299767c2e8
e65085af-dad9-4ac4-9aa6-a887d3606571
2018-07-10
2018-08-02

c0369b1c-0197-4c43-b048-1e299767c2e8
e8859a25-11d0-416f-bc1c-048451f14b4c
2018-07-10
2018-08-02

c0369b1c-0197-4c43-b048-1e299767c2e8
6608cf05-b135-40a2-9dbe-7cd3cf4f804e
2018-09-27
2018-10-08

c0369b1c-0197-4c43-b048-1e299767c2e8
4a380669-f5f6-4a62-8a44-8f53c2a6068d
2018-09-27
2018-10-08

BEN_ID	CLAIM_ID	DTE_ADMISSION	DTE_DISCHARGE	PrevDischarge	GroupId	Min_ADMISSION	Max_DISCHARGE
c0369b1c-0197-4c43-b048-1e299767c2e8	5685638d-412d-4d2e-9bef-af4c2a18f885	2018-07-10	2018-07-24	1900-01-01	1	2018-07-10	2018-08-02
c0369b1c-0197-4c43-b048-1e299767c2e8	e65085af-dad9-4ac4-9aa6-a887d3606571	2018-07-24	2018-07-24	2018-07-24	1	2018-07-10	2018-08-02
c0369b1c-0197-4c43-b048-1e299767c2e8	e8859a25-11d0-416f-bc1c-048451f14b4c	2018-07-24	2018-08-02	2018-07-24	1	2018-07-10	2018-08-02
c0369b1c-0197-4c43-b048-1e299767c2e8	6608cf05-b135-40a2-9dbe-7cd3cf4f804e	2018-09-27	2018-10-04	2018-08-02	2	2018-09-27	2018-10-08
c0369b1c-0197-4c43-b048-1e299767c2e8	4a380669-f5f6-4a62-8a44-8f53c2a6068d	2018-10-04	2018-10-08	2018-10-04	2	2018-09-27	2018-10-08