?
Solved

Incrementing the value in column in Sql server

Posted on 2014-03-20
3
Medium Priority
?
190 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 2000 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

719 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