Is there a way to bulk update records in SQL based on business requirements?

Question

Attach is an image of a hard case that I am trying to figure out how to update admission date and discharge date Example records.

BEN_ID	CLAIM_ID	DTE_ADMISSION	DTE_DISCHARGE
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

Here is the business requirements:

An inpatient discharge on June 1, followed by an admission to another inpatient setting on June 1, is a direct transfer.

An inpatient discharge on June 1, followed by an admission to an inpatient setting on June 2, is a direct transfer.

An inpatient discharge on June 1, followed by an admission to another inpatient setting on June 3, is not a direct transfer; these are two distinct inpatient stays.

If a claim meets the business requirements, then the first claim admission date will be "Index Admission Date", and the last claim discharge date will be "Index Discharge Date"

Based on the picture and business requirements, I want to update admission date of row 2 and 3 to July 10, 2018 as Index Admission Date and discharge date of row 1 and row 2 to August 2, 2018. The same logic applies to row 4 and 5. This is an example but the DB consists multiple record like this so I tried to use cursor but it didn't work and update the wrong date when there are more than 3 claims as row 1 to 3. Open to other suggestions as I am relatively new to SQL. The DB is highly denormalized so there is no primary keys. After running the cursor I run the second code sample to update.

DECLARE @Id NUMERIC(18,0) -- database name
DECLARE @BEN_ID uniqueidentifier -- database name
DECLARE @CLAIM_ID uniqueidentifier -- path for backup files
DECLARE @DTE_ADMISSION datetime -- filename for backup
DECLARE @DTE_DISCHARGE datetime -- filename for backup

DECLARE db_cursor CURSOR FOR

SELECT a.Id, a.BEN_ID, a.CLAIM_ID, a.DTE_ADMISSION, a.DTE_DISCHARGE
FROM MOCK_DATA_CLAIM a
ORDER BY a.DTE_ADMISSION ASC

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @Id, @BEN_ID, @CLAIM_ID, @DTE_ADMISSION, @DTE_DISCHARGE  

WHILE @@FETCH_STATUS = 0  
BEGIN  

 DECLARE @COUNT INT
 SET @COUNT = (SELECT COUNT(*) FROM MOCK_DATA_CLAIM d
 WHERE d.BEN_ID = @BEN_ID
 AND d.CLAIM_ID <> @CLAIM_ID
 AND ABS( DATEDIFF(DAY, d.DTE_ADMISSION, @DTE_DISCHARGE) ) < 2 )
 IF (@COUNT > 0)
 BEGIN

UPDATE MOCK_DATA_CLAIM
SET MOCK_DATA_CLAIM.DTE_DISCHARGE_UPDATE = b.DTE_DISCHARGE, MOCK_DATA_CLAIM.ID_UPDATE_DTE_DIS = b.Id
FROM MOCK_DATA_CLAIM
INNER JOIN MOCK_DATA_CLAIM b ON b.BEN_ID = @BEN_ID
AND ABS( DATEDIFF(DAY, MOCK_DATA_CLAIM.DTE_DISCHARGE, b.DTE_ADMISSION) ) < 2
WHERE MOCK_DATA_CLAIM.BEN_ID = @BEN_ID

UPDATE MOCK_DATA_CLAIM
SET MOCK_DATA_CLAIM.DTE_ADMISSION_UPDATE = @DTE_ADMISSION, MOCK_DATA_CLAIM.ID_UPDATE_DTE_ADD = @Id
WHERE MOCK_DATA_CLAIM.BEN_ID = @BEN_ID
AND ABS( DATEDIFF(DAY, @DTE_DISCHARGE, MOCK_DATA_CLAIM.DTE_ADMISSION) ) < 2

 END

      FETCH NEXT FROM db_cursor INTO @Id, @BEN_ID, @CLAIM_ID, @DTE_ADMISSION, @DTE_DISCHARGE
END

CLOSE db_cursor  
DEALLOCATE db_cursor

-- second code

DROP TABLE IF EXISTS MOCK_DATA_CLAIM_FINAL
SELECT a.BEN_ID, A.CLAIM_ID,
IIF(
    a.DTE_ADMISSION <> a.DTE_DISCHARGE,
    IIF(a.DTE_ADMISSION_UPDATE is not null, a.DTE_ADMISSION_UPDATE, a.DTE_ADMISSION),
    IIF(a.DTE_ADMISSION_UPDATE < a.DTE_DISCHARGE_UPDATE, a.DTE_ADMISSION_UPDATE, a.DTE_DISCHARGE_UPDATE)
) as DTE_ADMISSION,
IIF(
    a.DTE_ADMISSION <> a.DTE_DISCHARGE,
    IIF(a.DTE_DISCHARGE_UPDATE is not null, a.DTE_DISCHARGE_UPDATE, a.DTE_DISCHARGE),
    IIF( a.DTE_ADMISSION_UPDATE  < a.DTE_DISCHARGE_UPDATE or a.DTE_ADMISSION < a.DTE_DISCHARGE_UPDATE , a.DTE_DISCHARGE_UPDATE, a.DTE_ADMISSION)
) as DTE_DISCHARGE
INTO MOCK_DATA_CLAIM_FINAL
FROM MOCK_DATA_CLAIM a
order by a.BEN_ID, IIF(a.DTE_ADMISSION_UPDATE is not null, a.DTE_ADMISSION_UPDATE, a.DTE_ADMISSION)

user_14868997 · Answer

This is a classic gaps-and-islands problem. There are a number of solutions to such a problem, I have provided one below.

You use LAG to get the previous row's value, then use a conditional windowed COUNT to number the groups, then use a windowed MIN and MAX to get the values across the group.

You can use an updatable CTE to actually make the update. Note that there are no self-joins, the table is only scanned once and there are no cursors.

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;

db<>fiddle

Is there a way to bulk update records in SQL based on business requirements?

1 Answers