SQL – a member can see a doctor only once a year

I have a table named Claim.

I have built the following query except the last column(Reimburse). How do I populate Reimburse column like this table?

So the idea is that company pays a member to see a doctor only if it’s been more than a year since his last “reimbursed” visit(reimburse = 1). ClaimID C2 is denied because 10/10/2017 is less than 12 months from his last visit in 3/20/2017. ClaimID C3 is approved because DOS is more than 12 months from his last APPROVED visit(C1) – even though it’s been less than 12 months from his last (unapproved) visit (c2).

SELECT 
    MemberID,
    DOS,
    LAG(DOS,1,NULL) OVER (ORDER BY DOS) AS Last_DOS
FROM Claim 

Sorry, if my question is confusing.

DOS = date of service

Answer

Try this below logic-

WITH CTE(claimid,memberid,dos)
AS
(
    SELECT 'C1', 7655,'20170320' UNION ALL
    SELECT 'C2', 7655,'20171010' UNION ALL
    SELECT 'C3', 7655,'20180430' UNION ALL
    SELECT 'C4', 7655,'20181121' UNION ALL
    SELECT 'C5', 7655,'20190531'
)

SELECT *,
CASE 
    WHEN LAG(dos) OVER (PARTITION BY memberid ORDER BY claimid)  > DATEADD(YY,-1,dos) THEN 0 
    ELSE 1 
END AS Reimburse
FROM CTE

Solution using CURSOR-

DECLARE 
@claimid VARCHAR(20),
@memberid INT,
@dos Date,
@reimbursed_dos_prev DATE = NULL,
@memberid_prev INT = NULL;


DECLARE @your_table TABLE(
    claimid VARCHAR(20),
    memberid INT,
    dos Date
);


INSERT INTO @your_table
SELECT * FROM 
(
    SELECT 'C1' claimid, 7655 memberid,'20170320' dos UNION ALL
    SELECT 'C2', 7655,'20171010' UNION ALL
    SELECT 'C3', 7655,'20180430' UNION ALL
    SELECT 'C4', 7655,'20181121' UNION ALL
    SELECT 'C5', 7655,'20190531'
)A

DECLARE @your_table_2 TABLE(
    claimid VARCHAR(20),
    memberid INT,
    dos Date,
    Reimburse INT
);


DECLARE cursor_product CURSOR
FOR 
SELECT claimid,memberid,dos 
FROM @your_table
ORDER BY memberid,claimid;

OPEN cursor_product;

FETCH NEXT FROM cursor_product INTO @claimid,@memberid,@dos

WHILE @@FETCH_STATUS = 0
    BEGIN

        IF @reimbursed_dos_prev IS NULL AND @memberid_prev IS NULL
        BEGIN
            INSERT INTO @your_table_2(claimid,memberid,dos,Reimburse)
            VALUES(@claimid,@memberid,@dos,1)

            SET @reimbursed_dos_prev = @dos
            SET @memberid_prev = @memberid
        END

        ELSE IF @memberid_prev <> @memberid
        BEGIN
            INSERT INTO @your_table_2(claimid,memberid,dos,Reimburse)
            VALUES(@claimid,@memberid,@dos,1)

            SET @reimbursed_dos_prev = @dos
            SET @memberid_prev = @memberid
        END

        ELSE IF @dos < DATEADD(YY,1,@reimbursed_dos_prev)
        BEGIN
            INSERT INTO @your_table_2(claimid,memberid,dos,Reimburse)
            VALUES(@claimid,@memberid,@dos,0)

            SET @memberid_prev = @memberid
        END

        ELSE 
        BEGIN
            INSERT INTO @your_table_2(claimid,memberid,dos,Reimburse)
            VALUES(@claimid,@memberid,@dos,1)

            SET @reimbursed_dos_prev = @dos
            SET @memberid_prev = @memberid
        END

        FETCH NEXT FROM cursor_product INTO @claimid,@memberid,@dos
    END;

CLOSE cursor_product;

DEALLOCATE cursor_product;

SELECT * FROM @your_table_2;
DELETE FROM @your_table_2;