jdaites jdaites - 5 months ago 18
SQL Question

Last record being omit from formula

I wrote some logic to determine if a kcode (doctor billing) is correct for a patient. I wrote some logic to loop through the records and place in column the ID of the correct CP (care provider) who deserves the kcode. For some reason the last record or last 2 records are not following the logic. This is my logic code (inside a cursor):

OPEN kcode_count;
kcode_loop: LOOP

FETCH kcode_count INTO dbPTNT_ID, dbCP_ID, dbSEEN, holder, dbDATE, holder2;
IF done THEN
LEAVE kcode_loop;
END IF;

IF dbPTNT_ID <> setPTNT_ID THEN
IF setPTNT_ID <> 0 THEN
UPDATE tmp_kcodes
SET SHOULD_GET = setCP_ID
WHERE setPTNT_ID = PTNT_ID;
END IF;
SET setPTNT_ID = dbPTNT_ID;
SET setCP_ID = dbCP_ID;
SET setSEEN = dbSEEN;
SET setDATE = dbDATE;

ELSEIF dbSEEN > setSEEN THEN
SET setCP_ID = dbCP_ID;
SET setSEEN = dbSEEN;
SET setDATE = dbDATE;

ELSEIF dbSEEN = setSEEN THEN
IF dbDATE < setDATE THEN
SET setCP_ID = dbCP_ID;
SET setSEEN = dbSEEN;
SET setDATE = dbDATE;
END IF;
END IF;


END LOOP kcode_loop;
CLOSE kcode_count;


As seen below, in the SHOULD_GET column, the last two records produce NULL. Is there any reason why?

enter image description here

Answer

The issue is that you only update SHOULD_GET column after you processed a certain patient (patient 20092469's data is updated when the loop fetches the 1st 20092491 patient's record). For the last patient this obviously cannot happen, the dbPTNT_ID <> setPTNT_ID condition will never be satisfied.

At first glance I would repeat the

    IF setPTNT_ID <> 0 THEN
        UPDATE tmp_kcodes
        SET SHOULD_GET = setCP_ID
        WHERE setPTNT_ID = PTNT_ID;
    END IF;

block right after the end of the kcode_loop to update the last patient being processed.

Another solution could be to append a "dummy" row at the end of the resultset returned by the cursor using a union, this way the dbPTNT_ID <> setPTNT_ID condition will be triggered for the last patient as well.