Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: Help with cursor (logic)

    I have a Table that has Ref_No and Acct_No
    The ref_no is unique but the Acct_No have duplicates

    The data looks like:
    Ref_No Acct_No
    1 100
    2 100
    3 100
    4 101
    5 101

    I want to make an audit trail table showing that all acctNo
    Have been moved to the 1st Ref_No

    I want the end result to look like:
    (original) (new)
    Ref_No MOVED_TO Acct_No
    1 100
    2 1 100
    3 1 100
    4 101
    5 4 101


    So I am trying:
    CREATE TABLE Tbl_REF_No_AuditTrail(
    [Ref_No] [int] NOT NULL,
    [MOVED_TO] [int] ,
    [Acct_No] [char](20) NOT NULL)

    -- Don't show rowcounts in the results
    SET NOCOUNT ON

    DECLARE @RefNo int
    , @First_Ref int
    , @AcctNo varchar(20)
    , @First_AcctNo varchar(20)


    -- Declare the cursor
    DECLARE myCursor CURSOR
    FORWARD_ONLY READ_ONLY
    FOR
    SELECT L.REF_No
    , L.Acct_No
    FROM TblAcc L
    INNER JOIN TblManual_Acct_No R
    ON L.Acct_No = r. Acct_No
    ORDER BY L. Acct_No, L.REF_No


    -- Open the cursor
    OPEN myCursor

    -- Retrieve one row at a time from the cursor
    FETCH NEXT
    FROM myCursor
    INTO @RefNo
    , @AcctNo

    SELECT @First_Acct =@AcctNo
    , @First_Ref =@RefNo

    INSERT INTO Tbl_REF_No_AuditTrail([Ref_No], [MOVED_TO], [Acct_No] )
    VALUES(@RefNo ,null,@AcctNo )

    -- Keep retrieving rows while the cursor has them
    WHILE @@FETCH_STATUS = 0
    BEGIN


    -- Grab the next row
    FETCH NEXT
    FROM myCursor
    INTO @RefNo
    , @AcctNo


    HERE IS WHERE I GET CONFUSED
    With the logic

    IF @First_AcctNo = @AcctNo
    BEGIN
    INSERT INTO Tbl_REF_No_AuditTrail
    VALUES(
    END
    ELSE
    BEGIN
    INSERT INTO Tbl_REF_No_AuditTrail
    VALUES(
    END
    END

    -- Close the cursor
    CLOSE myCursor

    -- Deallocate the cursor
    DEALLOCATE myCursor



    Thanks allot for the help !!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE @meh table (
       ref_no  int
     , acct_no int
    )
    
    INSERT INTO @meh (ref_no, acct_no)
          SELECT 1, 100
    UNION SELECT 2, 100
    UNION SELECT 3, 100
    UNION SELECT 4, 101
    UNION SELECT 5, 101
    
    SELECT a.ref_no
         , b.ref_no As moved_to
         , a.acct_no
    FROM   @meh As a
     LEFT
      JOIN @meh As b
        ON a.acct_no = b.acct_no
       AND a.ref_no - 1 = b.ref_no
    Is this correct?
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Also posted here: Help with cursor (logic)
    George
    Home | Blog

  4. #4
    Join Date
    Oct 2003
    Posts
    233
    Quote Originally Posted by gvee
    Code:
    DECLARE @meh table (
       ref_no  int
     , acct_no int
    )
    
    INSERT INTO @meh (ref_no, acct_no)
          SELECT 1, 100
    UNION SELECT 2, 100
    UNION SELECT 3, 100
    UNION SELECT 4, 101
    UNION SELECT 5, 101
    
    SELECT a.ref_no
         , b.ref_no As moved_to
         , a.acct_no
    FROM   @meh As a
     LEFT
      JOIN @meh As b
        ON a.acct_no = b.acct_no
       AND a.ref_no - 1 = b.ref_no
    Is this correct?

    That gave me :

    ref_no movedTo acct_No
    3379 NULL AHC691004152027
    3380 3379 AHC691004152027
    3381 3380 AHC691004152027 <--Should be 3379
    3468 NULL ASC631004281017
    3469 3468 ASC631004281017

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •