How to reset sequence number within the batch number?

Question

1.00/5 (1 vote)

See more:

It is necessary to reset the sequence number within the batch number.

Sequence number doesn't reset within the batch number

My Result
========================
BATCH_NO         SEQ_NO
--------------   ------

7038001-000001     1 
7038001-000001     2
7038001-000001     3
7038001-000001     4
7038001-000001     5
7038001-000001     6
7038001-000001     7
7038001-000001     8
7038001-000001     9
7038001-000001     10

7038001-000002     11
7038001-000002     12
7038001-000002     13
7038001-000002     14
7038001-000002     15
7038001-000002     16
7038001-000002     17
7038001-000002     18
7038001-000002     19
7038001-000002     20


Expected result
========================
BATCH_NO         SEQ_NO
--------------   ------

7038001-000001     1 
7038001-000001     2
7038001-000001     3
7038001-000001     4
7038001-000001     5
7038001-000001     6
7038001-000001     7
7038001-000001     8
7038001-000001     9
7038001-000001     10

7038001-000002     1
7038001-000002     2
7038001-000002     3
7038001-000002     4
7038001-000002     5
7038001-000002     6
7038001-000002     7
7038001-000002     8
7038001-000002     9
7038001-000002     10


My Stored procedure
===================


ALTER PROCEDURE [dbo].[IMG_IWD_INWARD_SPLIT_BATCH]
AS

	DECLARE @TRANSACTION_MODE INT
	DECLARE @BATCH_TYPE       INT
	DECLARE @BRANCH_CODE      VARCHAR(3)
	DECLARE @BATCH_NO         VARCHAR(20)
	DECLARE @BATCH_SIZE       INT = 10;
    DECLARE @OFFSET           INT = 0;
	DECLARE @SEQ_NO           INT = 0;
	DECLARE @BatchCount INT
	DECLARE @CurrentBranchCode VARCHAR(3)
    DECLARE @CurrentTranMode   INT
    DECLARE @CurrentBatchType  INT


BEGIN                                                     
   SET NOCOUNT ON;

   		DECLARE CUR_Branch CURSOR FOR 
		SELECT BRANCH_CODE,TRANSACTION_MODE FROM IMG_IWD_INWARD_SPLIT 
		GROUP BY BRANCH_CODE,TRANSACTION_MODE
		
		OPEN CUR_Branch
		FETCH NEXT FROM CUR_Branch INTO @CurrentBranchCode,@CurrentTranMode
		WHILE @@FETCH_STATUS=0  
		BEGIN		 
		
	  SET @OFFSET = 0;

	  WHILE @OFFSET < (SELECT COUNT(*) FROM IMG_IWD_INWARD_SPLIT)
	  BEGIN	
		     SET @BATCH_NO = dbo.GET_MAX_BATCH_NO('001');

		     INSERT INTO IMG_IWD_INWARDCLEARING
		     (	
		     BATCH_NO,	
		     SEQ_NO,
		     PRE_BANK_CODE,
		     PRE_BRANCH_CODE,		
		     CHEQUE_NO,		    
		     BANK_CODE,		   
		     BRANCH_CODE,	
		     ACCOUNT_NO ,		
		     TXN_CODE,				
		     AMOUNT	
		     )
		     SELECT 
             @BATCH_NO AS BATCH_NO			 
             ROW_NUMBER() OVER (PARTITION BY @BATCH_NO ORDER BY (SELECT 1))  AS SEQ_NO,	    
		     PRE_BANK_CODE,
		     PRE_BRANCH_CODE,
		     CHEQUE_NO,	
		     BANK_CODE,		
		     BRANCH_CODE,		    
		     ACCOUNT_NO,		   
		     TXN_CODE,				
		     AMOUNT,
	         FROM IMG_IWD_INWARD_SPLIT T2
             WHERE 1=1
	 		 AND BRANCH_CODE= @CurrentBranchCode
	         AND TRANSACTION_MODE = @CurrentTranMode
	         ORDER BY T2.BANK_CODE,T2.BRANCH_CODE,T2.TRANSACTION_MODE,T2.ACCOUNT_NO,T2.CHEQUE_NO ASC,T2.AMOUNT ASC
             OFFSET @OFFSET ROWS FETCH NEXT @BATCH_SIZE ROWS ONLY;  
             SET @OFFSET += @BATCH_SIZE;
			 	
END	        
		FETCH NEXT FROM CUR_Branch INTO @CurrentBranchCode,@CurrentTranMode
		END
			CLOSE CUR_Branch
			DEALLOCATE CUR_Branch
END

What I have tried:

ALTER PROCEDURE [dbo].[IMG_IWD_INWARD_SPLIT_BATCH_1]
AS

	DECLARE @TRANSACTION_MODE INT
	DECLARE @BATCH_TYPE       INT
	DECLARE @BRANCH_CODE      VARCHAR(3)
	DECLARE @BATCH_NO         VARCHAR(20)
	DECLARE @BATCH_SIZE       INT = 10;
    DECLARE @OFFSET           INT = 0;
	DECLARE @SEQ_NO           INT = 0;
	 DECLARE @BatchCount INT


	DECLARE @CurrentBranchCode VARCHAR(3)
    DECLARE @CurrentTranMode   INT
    DECLARE @CurrentBatchType  INT


BEGIN                                                     
   SET NOCOUNT ON;

   		DECLARE CUR_Branch CURSOR FOR 
		SELECT BRANCH_CODE,TRANSACTION_MODE FROM IMG_IWD_INWARD_SPLIT 
		GROUP BY BRANCH_CODE,TRANSACTION_MODE
		
		OPEN CUR_Branch
		FETCH NEXT FROM CUR_Branch INTO @CurrentBranchCode,@CurrentTranMode
		WHILE @@FETCH_STATUS=0  
		BEGIN		 
		
	  SET @OFFSET = 0;

	  WHILE @OFFSET < (SELECT COUNT(*) FROM IMG_IWD_INWARD_SPLIT)
	  BEGIN	
	  		
		    SET @BATCH_NO = dbo.GET_MAX_BATCH_NO('001');


		     INSERT INTO IMG_IWD_INWARDCLEARING
		     (
		     PROCESS_DATE,
		     CLEARING_DATE,
		     VALUE_DATE,
		     CLEARING_CYCLE,
		     TRANSACTION_TYPE,
		     TRANSACTION_MODE,
		     ORG_BANK_CODE,
		     ORG_BRANCH_CODE,
		     ORG_BRANCH_EXTENSION,
		     BATCH_NO,
		     BUNDLE_ID,				
		     FILE_NO,
		     SEQ_NO,
		     CUST_NUM,
		     CUST_SEG_CODE,
		     PRE_BANK_CODE,
		     PRE_BRANCH_CODE,
		     INT_CHEQUE_NO,
		     OCR_CHEQUE_NO,
		     CHEQUE_NO,
		     INT_BANK_CODE,
		     OCR_BANK_CODE,
		     BANK_CODE,
		     INT_BRANCH_CODE,
		     OCR_BRANCH_CODE,
		     BRANCH_CODE,				
		     INT_ACCOUNT_NO,
		     OCR_ACCOUNT_NO,
		     ACCOUNT_NO ,
		     INT_TXN_CODE,
		     OCR_TXN_CODE,
		     TXN_CODE,				
		     AMOUNT,
		     CURRENCY_CODE,
		     UNIQUE_IDENTIFIER,
		     PREVIOUS_UNIQUE_IDENTIFIER
		     )
		     SELECT 
		     PROCESS_DATE,	
		     CLEARING_DATE,
		     PROCESS_DATE AS VALUE_DATE, 
		     CLEARING_CYCLE ,
		     3 AS TRANSACTION_TYPE,
		     TRANSACTION_MODE,
		     BANK_CODE AS ORG_BANK_CODE,
		     BRANCH_CODE AS BRANCH_CODE,	
		     '00' AS ORG_BRANCH_EXTENSION,
		     @BATCH_NO as BATCH_NO,
		     NULL AS BUNDLE_ID,	
		     FILE_NO,
              ROW_NUMBER() OVER (PARTITION BY @BATCH_NO ORDER BY (SELECT 1)) AS SEQ_NO, 
	         '0' + ACCOUNT_NO AS CUST_NUM,
		     '01' AS CUST_SEG_CODE,
		     PRE_BANK_CODE,
		     PRE_BRANCH_CODE,
		     CHEQUE_NO AS INT_CHEQUE_NO,
		     CHEQUE_NO AS OCR_CHEQUE_NO,
		     CHEQUE_NO,
		     BANK_CODE AS INT_BANK_CODE,
		     BANK_CODE AS OCR_BANK_CODE,
		     BANK_CODE,
		     BRANCH_CODE AS INT_BRANCH_CODE,
		     BRANCH_CODE AS OCR_BRANCH_CODE,
		     BRANCH_CODE,
		     ACCOUNT_NO AS INT_ACCOUNT_NO,
		     ACCOUNT_NO AS OCR_ACCOUNT_NO,
		     ACCOUNT_NO AS ACCOUNT_NO ,
		     TXN_CODE AS INT_TXN_CODE,
		     TXN_CODE AS	OCR_TXN_CODE,
		     TXN_CODE,				
		     AMOUNT,
		     'LKR' AS CURRENCY_CODE,
		     UNIQUE_IDENTIFIER,
		     UNIQUE_IDENTIFIER AS PREVIOUS_UNIQUE_IDENTIFIER
	         FROM IMG_IWD_INWARD_SPLIT T2
             WHERE 1=1
	 		 AND BRANCH_CODE= @CurrentBranchCode
	         AND TRANSACTION_MODE = @CurrentTranMode
	         ORDER BY T2.BANK_CODE,T2.BRANCH_CODE,T2.TRANSACTION_MODE,T2.ACCOUNT_NO,T2.CHEQUE_NO ASC,T2.AMOUNT ASC
             OFFSET @OFFSET ROWS FETCH NEXT @BATCH_SIZE ROWS ONLY;  
             SET @OFFSET += @BATCH_SIZE;
			 	
END	        
		FETCH NEXT FROM CUR_Branch INTO @CurrentBranchCode,@CurrentTranMode
		END
			CLOSE CUR_Branch
			DEALLOCATE CUR_Branch
END

Posted 12-Jul-24 19:25pm

chamindat

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2024-07-12T19:40:00

As far as I understand the example, you're partitioning the row number using a variable. The value of the variable never changes within a single execution of the select statement so all rows within that select belong to the same partition. This causes the situation that your partition does not change and the row numbering does not reset.

You didn't specify the logic behind the code but one possibility is that you do the operation in two runs. First add the rows into the table, second apply the numbering to the inserted rows using only a single SQL statement. This would allow partitioning to work correctly.

Obviously another possibility is to use variables and reset the numbering in each iteration of the loop but since this is SQL it would be more efficient to try to work with sets instead of using cursors and loops.

How to reset sequence number within the batch number?

1 solution

Solution 1

Add your solution here

Preview 0

How to reset sequence number within the batch number?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us