Query produces incorrect results

  • Hello
    I am working on a complex problem
    There is a problem with the query

    DECLARE @AMTTOMARK DECIMAL(38,2),@CVIDDR VARCHAR(11),@CVIDCR VARCHAR(11),@MCVID VARCHAR(11),@CURRMARKAMT DECIMAL(38,2),@skipdr INT,@skipcr INT
    DECLARE @Balamtdr DECIMAL(38,2)
    DECLARE @amtdr DECIMAL(38,2)
    DECLARE @balamtcr DECIMAL(38,2)
    DECLARE @amtcr DECIMAL(38,2)
    DECLARE @CVID VARCHAR(11)
    ;
    IF EXISTS (SELECT * FROM sys.tables WHERE name= 'Cashvchr') BEGIN
    DROP TABLE Cashvchr
    END
    ;

    CREATE TABLE [dbo].[Cashvchr](
    [TRANSACTIONID] INT NOT NULL,
    [VCHRDATE] [datetime] NULL,
    [CustomerID] [varchar](5) NOT NULL,
    [DRCR] [varchar](1) NOT NULL,
    [Amount] DECIMAL(38,2) NOT NULL
    ) ON [PRIMARY]
    ;

    INSERT INTO [dbo].[Cashvchr](TRANSACTIONID,VCHRDATE,CustomerID,DRCR,Amount)
    SELECT '1','2018-01-01 00:00:00.000','RAS12','D','2000' UNION ALL
    SELECT '2','2018-01-02 00:00:00.000','RAS12','D','3000' UNION ALL
    SELECT '3','2018-01-03 00:00:00.000','RAS12','C','4000' UNION ALL
    SELECT '4','2018-01-04 00:00:00.000','RAS12','D','5000' UNION ALL
    SELECT '5','2018-01-05 00:00:00.000','RAS12','C','1000' UNION ALL
    SELECT '6','2018-01-06 00:00:00.000','RAS12','C','6000' UNION ALL
    SELECT '7','2018-01-07 00:00:00.000','RAS12','D','3000'
    ;

    IF NOT EXISTS (SELECT * FROM sys.tables WHERE name= 'TargetTable') BEGIN
    CREATE TABLE TargetTable (CASHVCHRID INT,AMOUNT DECIMAL(38,2),MARKEDCASHVCHRID INT)
    END
    ;
    declare @ıd varchar(15)
    DECLARE custerr CURSOR READ_ONLY FORWARD_ONLY STATIC FOR

    select cv.CustomerID    
     From Cashvchr cv    
    Group by cv.CustomerID
    having sum(case when cv.drcr='D' then cv.Amount else 0 end) <> 0
      and sum(case when cv.drcr='C' then cv.Amount else 0 end) <> 0
    OPEN custerr
    FETCH NEXT FROM custerr
    INTO  @ıd
    WHILE @@FETCH_STATUS=0
    BEGIN

     DECLARE CVDR CURSOR READ_ONLY FORWARD_ONLY STATIC FOR         
        select TRANSACTIONID, cv.Amount            
        From Cashvchr cv    
        where cv.CustomerID=@ıd
        AND CV.DRCR = 'D'
        ORDER BY VCHRDATE,TRANSACTIONID
    OPEN CVDR

    FETCH NEXT FROM CVDR
    INTO     @CVIDDR, @AMTDR

    DECLARE CVCR CURSOR READ_ONLY FORWARD_ONLY STATIC FOR         
        select TRANSACTIONID, cv.Amount            
        From Cashvchr cv    
        where cv.CustomerID=@ıd
        AND CV.DRCR = 'C'
        ORDER BY VCHRDATE,TRANSACTIONID
    OPEN CVCR

    FETCH NEXT FROM CVCR
    INTO @CVIDCR, @AMTCR 

    BEGIN

       set @Balamtdr = @amtdr
       set @balamtcr = @amtcr

    --  WHILE @AMTTOMARK > 0
      --   BEGIN      

          SET @CVID = @CVIDDR
          SET @MCVID = @CVIDCR
          SET @CURRMARKAMT=0
          set @skipdr = 0
          set @skipcr = 0
          IF @BALAMTDR > @BALAMTCR -- i.e. balance debit amount to be marked is bigger from bal.credit amt THEN skip CREDIT
           begin
            SET @CURRMARKAMT = @balAMTCR            
            set @skipCr = 1
            set @balamtdr = @balamtdr - @balamtcr
           end
          ELSE IF @balAMTDR < @balAMTCR -- i.e. balance Credit amount is bigger THEN skip Debit
           begin
            SET @CURRMARKAMT = @balAMTDR 
            set @skipdr = 1
            set @balamtcr = @balamtCr - @balamtdr
           end
          ELSE -- i.e. balance Credit & Debit amount is same, mark and skip both
           begin
            SET @CURRMARKAMT = @balamtdr
            set @balamtcr = @balamtCr - @balamtdr
            set @skipdr = 1
            set @skipcr = 1
           end
          ----
          INSERT INTO TargetTable
           (CASHVCHRID,AMOUNT,MARKEDCASHVCHRID)       
    VALUES
           (@CVIDDR,@CURRMARKAMT,@CVIDCR)

          set @amttomark = @amttomark - @currmarkamt

          if @skipdr = 1 and @amttomark > 0
           begin
            FETCH next from CVDR INTO @CVIDDR, @AMTDR
            if @@fetch_status <> 0
              begin
               set @amttomark = 0
              end
            else
              begin
               set @balamtdr = @amtdr
              end
            -- end if
           end
          -- end if @skipdr = 1
          if @skipcr = 1 and @amttomark > 0
           begin
            FETCH next from CVCR INTO @CVIDCR, @AMTCR 
            if @@fetch_status <> 0
              begin
               set @amttomark = 0
              end
            else
              begin
               set @balamtcr = @amtcr
              end
            -- end if

           end
     
        END

    CLOSE CVCR
    DEALLOCATE CVCR
    CLOSE CVDR
    DEALLOCATE CVDR

    FETCH NEXT FROM custerr
    INTO  @ıd
    end

    CLOSE custerr
    DEALLOCATE custerr

            ----WHILE @AMTTOMARK > 0 FOR DR/CR skip FOR ONE CUSTOMER
            ---END

            SELECT * FROM TargetTable

  • meryemkurs072 - Thursday, March 14, 2019 3:06 PM

    Hello
    I am working on a complex problem
    There is a problem with the query

    DECLARE @AMTTOMARK DECIMAL(38,2),@CVIDDR VARCHAR(11),@CVIDCR VARCHAR(11),@MCVID VARCHAR(11),@CURRMARKAMT DECIMAL(38,2),@skipdr INT,@skipcr INT
    DECLARE @Balamtdr DECIMAL(38,2)
    DECLARE @amtdr DECIMAL(38,2)
    DECLARE @balamtcr DECIMAL(38,2)
    DECLARE @amtcr DECIMAL(38,2)
    DECLARE @CVID VARCHAR(11)
    ;
    IF EXISTS (SELECT * FROM sys.tables WHERE name= 'Cashvchr') BEGIN
    DROP TABLE Cashvchr
    END
    ;

    CREATE TABLE [dbo].[Cashvchr](
    [TRANSACTIONID] INT NOT NULL,
    [VCHRDATE] [datetime] NULL,
    [CustomerID] [varchar](5) NOT NULL,
    [DRCR] [varchar](1) NOT NULL,
    [Amount] DECIMAL(38,2) NOT NULL
    ) ON [PRIMARY]
    ;

    INSERT INTO [dbo].[Cashvchr](TRANSACTIONID,VCHRDATE,CustomerID,DRCR,Amount)
    SELECT '1','2018-01-01 00:00:00.000','RAS12','D','2000' UNION ALL
    SELECT '2','2018-01-02 00:00:00.000','RAS12','D','3000' UNION ALL
    SELECT '3','2018-01-03 00:00:00.000','RAS12','C','4000' UNION ALL
    SELECT '4','2018-01-04 00:00:00.000','RAS12','D','5000' UNION ALL
    SELECT '5','2018-01-05 00:00:00.000','RAS12','C','1000' UNION ALL
    SELECT '6','2018-01-06 00:00:00.000','RAS12','C','6000' UNION ALL
    SELECT '7','2018-01-07 00:00:00.000','RAS12','D','3000'
    ;

    IF NOT EXISTS (SELECT * FROM sys.tables WHERE name= 'TargetTable') BEGIN
    CREATE TABLE TargetTable (CASHVCHRID INT,AMOUNT DECIMAL(38,2),MARKEDCASHVCHRID INT)
    END

    ;

    TRUNCATE TABLE TargetTable

    ;
    declare @ıd varchar(15)

     DECLARE custerr CURSOR READ_ONLY FORWARD_ONLY STATIC FOR  

    select cv.CustomerID    
     From Cashvchr cv    
    Group by cv.CustomerID
    having sum(case when cv.drcr='D' then cv.Amount else 0 end) <> 0
      and sum(case when cv.drcr='C' then cv.Amount else 0 end) <> 0
    OPEN custerr

    FETCH NEXT FROM custerr
    INTO    @ıd

    while @@FETCH_STATUS=0
    BEGIN
     DECLARE CVDR CURSOR READ_ONLY FORWARD_ONLY STATIC FOR         
        select TRANSACTIONID, cv.Amount            
        From Cashvchr cv    
        where cv.CustomerID=@ıd
        AND CV.DRCR = 'D'
        ORDER BY VCHRDATE,TRANSACTIONID
    OPEN CVDR

    FETCH NEXT FROM CVDR
    INTO     @CVIDDR, @AMTDR

    DECLARE CVCR CURSOR READ_ONLY FORWARD_ONLY STATIC FOR         
        select TRANSACTIONID, cv.Amount            
        From Cashvchr cv    
        where cv.CustomerID=@ıd
        AND CV.DRCR = 'C'
        ORDER BY VCHRDATE,TRANSACTIONID
    OPEN CVCR

    FETCH NEXT FROM CVCR
    INTO @CVIDCR, @AMTCR 
    while @@FETCH_STATUS=0
    BEGIN

       set @Balamtdr = @amtdr
       set @balamtcr = @amtcr

           

          SET @CVID = @CVIDDR
          SET @MCVID = @CVIDCR
          SET @CURRMARKAMT=0
          set @skipdr = 0
          set @skipcr = 0
          IF @BALAMTDR > @BALAMTCR -- i.e. balance debit amount to be marked is bigger from bal.credit amt THEN skip CREDIT
           begin
            SET @CURRMARKAMT = @balAMTCR            
            set @skipCr = 1
            set @balamtdr = @balamtdr - @balamtcr

           end
          ELSE IF @balAMTDR < @balAMTCR -- i.e. balance Credit amount is bigger THEN skip Debit
           begin
            SET @CURRMARKAMT = @balAMTDR 
            set @skipdr = 1
            set @balamtcr = @balamtCr - @balamtdr
           end
          ELSE -- i.e. balance Credit & Debit amount is same, mark and skip both
           begin
            SET @CURRMARKAMT = @balamtdr
            set @balamtcr = @balamtCr - @balamtdr
            set @skipdr = 1
            set @skipcr = 1
           END
          ----
          INSERT INTO TargetTable
           (CASHVCHRID,AMOUNT,MARKEDCASHVCHRID)       
    VALUES
           (@CVIDDR,@CURRMARKAMT,@CVIDCR)

       ---   set @amttomark = @amttomark - @currmarkamt

       
         

          if @skipdr = 1 and @currmarkamt > 0
           begin
            FETCH next from CVDR INTO @CVIDDR, @AMTDR
            if @@fetch_status <> 0
              begin
               set @currmarkamt = 0
              end
            else
              begin
               set @balamtdr = @amtdr
              end
            -- end if
           end
          -- end if @skipdr = 1
          if @skipcr = 1 and @currmarkamt > 0
           begin
            FETCH next from CVCR INTO @CVIDCR, @AMTCR 
            if @@fetch_status <> 0
              begin
               set @currmarkamt = 0
              end
            else
              begin
               set @balamtcr = @amtcr
              end
            -- end if

           end
          -- end if @skipcr = 1
        -- END
       --- END
            ----WHILE @AMTTOMARK > 0 FOR DR/CR skip FOR ONE CUSTOMER
            ---END
    end
    FETCH next from custerr INTO @ıd
    end

    SELECT * FROM TargetTable

    You should add a couple of comments to the spreadsheet to identify what is expected and what is not.  At least identify how the run results of the code you posted aren't what you want and provide a hint as to what the code is supposed to do in your currently uncomented code.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I am working on a complex problem - Bill Marking for Ageing Analysis. The data is like this :

    Source Table :

    TrxnID, Date, CustomerID, DebitCredit, Amount
    -----------------------------------------------
    1  01-Apr, RAS12,   D,   2000
    2  01-Apr, RAS12,   D,   3000
    3  02-Apr, RAS12,   C,   4000
    4  03-Apr, RAS12,   D,   5000
    5  04-Apr, RAS12,   C,   1000
    6  10-Apr, RAS12,   C,   6000
    7  25-Apr, RAS12,   D,   3000

    So, Total Debit : 13,000 and Total Credit : 11,000 and Total Balance is 2,000 Debit.

    So the objective is to mark all Credit records with the respective debit records based on the date (FIFO), and store the marking scheme for each record in a separate table as shown below.

    The target table has marking detail of each record

    Target Table

    TrxnID, MarkedTrxnID, MarkedAmount
    ------------------------------------ 
    1,c3,2000 **Rem : 1 balance=0, 3 balance= 1000
    2,c3,2000 **Rem : 2 balance=1000, 3 balance= 0
    2,c5,1000 **Rem : 2 balance=0, 5 balance= 0
    4,c6,5000 **Rem : 4 balance=0, 6 balance= 1000
    7,c6,1000 **Rem : 7 balance=2000, 6 balance= 0
    I have been using old fashioned cursor based approach and that too becomes quite complex, but believe that there must be a set based mechanism to handle this.

    Any help would be greatly appreciated. We are using SQL Server 2008 R2.

    Thanks

  • Based on what you've posted, I'm confused as to the nature of the marking scheme.
    Two consecutive debits appear, putting the balance at what I would consider a negative 5000.
    Then you see a credit for just 4000 leaving a negative 1000 balance.   This continues with yet
    another debit for 5000, leaving a negative 6000 balance, then there's a credit for 1000, followed
    by a credit for 6000, leaving balances of negative 5000 and positive 1000, respectively.   Finally,
    there's a debit for 3000, leaving a final balance of negative 2000.   The idea that you can tie any
    given debit record to only certain specific credit records doesn't really make any logical sense
    whatsoever, nor does it likely provide any benefit of any kind for a business to do such a thing,
    unless there's something in play that we're not privy to.   Please explain what the benefit of this
    is, because I can't come up with one that uniquely makes this more beneficial than just having
    the record of all the transactions and the initial balance.  If you want a balance after each transaction,
    or even a balance for every given day, that I get.  But tying one transaction to any other?  Really?
    What is it that we don't know?

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Thank you for your interest.
    The result of the query will be as follows. I want to close the debt.

    TrxnID MarkedAmount                 MarkedTrxnID
    1                2000.00                           3
    2                2000.00                           3
    2                1000.00                           5
    4                5000.00                           6
    7                1000.00                           6

  • You keep using the term "close the debt", as if it were something you could make mean whatever it needed to, when the well-known and generally accepted meaning is for a debt to be paid in full (a 0 balance).  This data doesn't bring the debt to closure, and you have not explained any benefit of any kind to what you are doing.  Clearly, each transaction that pays down the debt may well do so against more than one of the debt increases, so again, I fail to see the benefit of any of this.   Unless you have some kind of FIFO or LIFO queue to deal with here, I'm not going anywhere with this until it's fully explained.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply