Solved

Incrementing the value in column in Sql server

Posted on 2014-03-20
3
189 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 41

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

617 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