Member Avatar for moone009

create table #TempRouting( id int, routetype char(4), routesequence int)
insert into #TempRouting (id,routetype) values (1,'MON')
insert into #TempRouting (id,routetype) values (1,'MON')
insert into #TempRouting (id,routetype) values (2,'MON')
insert into #TempRouting (id,routetype) values (3,'MON')
insert into #TempRouting (id,routetype) values (4,'MON')
insert into #TempRouting (id,routetype) values (5,'MON')
insert into #TempRouting (id,routetype) values (5,'MON')
insert into #TempRouting (id,routetype) values (6,'MON')


DECLARE @id INT
SET @id = 1
IF exists (select id from #temprouting group by id having count(id) > 1)
begin
UPDATE #TempRouting
SET @id = routesequence = @id
END
ELSE
begin
UPDATE #TempRouting
Set @id = routesequence = @id + 1
end
select * from #TempRouting

This is my current result.

id routetype routesequence
1 MON 1
1 MON 1
2 MON 1
3 MON 1
4 MON 1
5 MON 1
5 MON 1
6 MON 1


This is what I want:

id routetype routesequence
1 MON 1
1 MON 1
2 MON 2
3 MON 3
4 MON 4
5 MON 5
5 MON 5
6 MON 6


Any Ideas? Basically if the ID is the same it should not increase the routesequence

Edited by moone009 because: n/a
  • 3 Contributors
  • 5 Replies
  • 183 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by moone009
Member Avatar for debasisdas

So, you want

id = routesequence ?

Member Avatar for buddylee17

What is the point of the routesequence?? You're just storing the same value twice...

Also, each row should be unique, whether it be an identity on the id or a composite value. Rows 1 & 2 are duplicates, as are 6 & 7. This design violates first normal form, will be slow to query, and a nightmare to maintain.

Edited by buddylee17 because: n/a
Member Avatar for moone009

The actual table is somewhat unique based upon the actual product being delivered. I need this to handle how many requests per location.

If they see 12 Smith St. has 2 producttypes being delivered the routesequence will not increase until they reach a new address(id).


create table #TempRouting( id int,ProductType varchar(36), routetype char(4), routesequence int)

id ProductType routetype routesequence
1 3 MON 1
1 4 MON 1
2 3 MON 2
3 6 MON 3
4 3 MON 4
5 3 MON 5
5 2 MON 5
6 3 MON 6

Member Avatar for debasisdas

still then

id remains equals to routesequence, right ?

Member Avatar for moone009

Well no because my ID is actually a guid. I was just using that as an example

Edited by moone009 because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.