How to update times of using the service of Laboratory for each patient in SQL Server?-CodePudding

I have a table named "lab_master(visit_rank,pid)". visit_rank: is the times of visit pid: is the patient id

I want to update the column "visit_rank" with the value of number of visit start from number 1 for each patient. I have tried like below SQL code but it's not correct.

   DECLARE cursor_visit_rank CURSOR FOR
SELECT pid FROM lab_master
DECLARE @visit_rank INT
DECLARE @pid VARCHAR(50)
OPEN cursor_visit_rank
FETCH NEXT FROM cursor_visit_rank INTO @pid
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @visit_rank = 0
        UPDATE lab_master SET visit_rank = @visit_rank   1 WHERE pid=@pid
        FETCH NEXT FROM cursor_visit_rank INTO @pid
    END
CLOSE cursor_visit_rank
DEALLOCATE cursor_visit_rank

CodePudding user response：

You want to count how many visits per person and store that? Try this:

DECLARE cursor_visit_rank CURSOR FOR
SELECT pid FROM lab_master order by pid

DECLARE @visit_rank INT
declare @thisid int = 0
DECLARE @pid VARCHAR(50)
OPEN cursor_visit_rank
FETCH NEXT FROM cursor_visit_rank INTO @pid
WHILE @@FETCH_STATUS = 0
    BEGIN
        if @thisid <> @pid
           set @visit_rank = 0

        set @thisid = @pid

        SET @visit_rank  = 1

        UPDATE lab_master SET visit_rank = @visit_rank where pid = @pid

        FETCH NEXT FROM cursor_visit_rank INTO @pid

    END
CLOSE cursor_visit_rank
DEALLOCATE cursor_visit_rank

CodePudding user response：

Any time you find yourself writing a cursor, you should question your assumptions. There is almost no instance when you actually need a cursor, which would be very slow.

Instead you can do this in a set-based fashion, using a window function.

;
WITH cte AS (
    SELECT *,
      count = COUNT(*) OVER (PARTITION BY lm.pid)
    FROM lab_master lm
)
UPDATE cte
SET visit_rank = count;

Alternatively you can do a self-join

UPDATE lm
SET visit_rank = lm2.pid
FROM lab_master lm
CROSS APPLY (
    SELECT
      count = COUNT(*)
    FROM lab_master lm2
    WHERE lm2.pid = lm.pid
) lm2;