Solved

Incrementing the value in column in Sql server

Posted on 2014-03-20
3
186 Views
Last Modified: 2014-04-01
I have  the following requirement.

I have the table with following structure

TranType, episodeid, Adviseserialno


I want  to update AdviseSerialno  in such a way that the next number is incremented by 1 from previous number. This sequence number should get reset (I,e should start from 1 )
for each change of Episodeid.

How I can achieve this using SQL . DB is Sql 2008R2

Thanks in advance
0
Comment
Question by:venkataramanaiahsr
3 Comments
 
LVL 16

Expert Comment

by:DcpKing
ID: 39942093
To start with, you'll know TranType and EpisodeID so let's say that the new values are in  @TranType and @EpisodeID.

Declare an int (I'm assuming AdviseSerialNo is an int) @intAdviseSerialNo and do this:

declare @intAdviseSerialNo int

select @intAdviseSerialNo = max(Adviseserialno) + 1
from TableName 
where EpisodeID = @EpisodeID

if @intAdviseSerialNo is null set @intAdviseSerialNo = 1

insert into TableName (TranType, EpisodeID, AdviseSerialNo) 
   select @TranType, @EpisodeID, @intAdviseSerialNo

Open in new window


Hope this helps

Mike
0
 
LVL 15

Accepted Solution

by:
gplana earned 500 total points
ID: 39942118
Try something like this (I have not tested it, but you can get the idea):

DECLARE @episId NUMBER;
DECLARE c CURSOR FOR SELECT DISTINCT EpisodeId FROM your_table;
open c;
FETCH NEXT FROM c INTO @episId;
WHILE @@FETCH_STATUS = 0 
BEGIN
    SELECT @nextId = MAX(AdviseSerialNo)+1 FROM your_table WHERE EpisodeId=@episId;
    UPDATE TOP(1) your_table SET AdviseSerialNo=@nextId WHERE EpisodeId=@episId AND AdviseSerialNo IS NULL;
    FETCH NEXT FROM c INTO @episId;
END
close c;

Open in new window


Hope it helps. Regards.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39942505
How do you order the records? Do you have any date column or ID column to get the next record?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question