CODEWITHSUNDEEP
sql-server
Mohamedasiq
Mohamedasiq

Reputation: 91

How to get the incremented value in a column based on the other column in sql

I have the below table records. For Every TranNo I have to increment one in the expected result column. Is it possible?

enter image description here

How to get this result in sqlserver?

I am unable to get the appropriate result

 <Expected Result>
1
1
2
3
4
5
6
7
8

I tried: 

SELECT BatchNo,
       TranNo,
       WorkSource
FROM table1 WITH (NOLOCK)
WHERE BatchNo IN ('0000000420', '0000000421', '0000000422')
  AND PROCESSDATE = '20190206'
GROUP BY WorkSource,
         BATCHNO,
         TranNo;

Upvotes: 2

Views: 90

Answers (2)

EzLo
EzLo

Reputation: 14189

Try with this:

;WITH LagTranNo AS
(
    SELECT 
        *, 
        IsDiffTranNo = CASE WHEN TranNo =  LAG(TranNo) OVER (ORDER BY WorkSource, BatchNo, TranNo) THEN 0 ELSE 1 END
    FROM 
        YourTable
)
SELECT
    L.*,
    SUM(L.IsDiffTranNo) OVER (ORDER BY WorkSource, BatchNo, TranNo)
FROM
    LagTranNo AS L

I believe you want to add 1 only when the TranNo changes. Need to be careful with the ordering.

Note: LAG with only work with SQL Server 2012+.

Upvotes: 4

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try this

create table #temp (WorkSource char(3), BatchNo char(3), TranNo int)
insert into #temp values
('012', '001', 1),
('012', '001', 1),
('012', '001', 2),
('012', '001', 3),
('012', '002', 1),
('012', '002', 2),
('012', '003', 3),
('013', '005', 1),
('013', '005', 2)

SELECT *, Dense_Rank() OVER(ORDER BY BatchNo, TranNo) AS Row_Number  
FROM #temp

Upvotes: 4

Related Questions