Solved

update sequence field in Sql

Posted on 2013-06-26
4
438 Views
Last Modified: 2013-06-26
So i have a table with lets say four fields on table1

Date
Group
Status
Sequence_number

i need to update the sequence grouping the fields by the other three fields.

so if there are 4 records where Date, Group and Status are the same,  the Sequence_number would start at 101, 102, 103, 104 then start again at 101 for the next group of records where these three fields are the same.

thanks in advance
0
Comment
Question by:damixa
[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
4 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39278044
Here's the SELECT, with some supporting testing code.  Copy-paste this into your SSMS, verify it works, then we'll have to figure out how to UPDATE with the final set.

CREATE TABLE #tmp (date datetime, the_group varchar(10), status varchar(10), sequence_number int) 

INSERT INTO #tmp (date, the_group, status)
VALUES 
	('1-1-2012', 'Group1', 'Ready'), 
	('1-1-2012', 'Group1', 'Ready'), 
	('1-1-2012', 'Group1', 'Ready'), 
	('1-1-2012', 'Group1', 'Not Ready'), 	
	('1-1-2012', 'Group2', 'Ready'), 
	('1-1-2012', 'Group2', 'Not Ready'), 
	('1-1-2012', 'Group2', 'Not Ready'), 
	('7-1-2012', 'Group1', 'Ready'), 
	('7-1-2012', 'Group1', 'Ready'), 
	('7-1-2012', 'Group1', 'Ready'), 
	('7-1-2012', 'Group1', 'Not Ready'), 	
	('7-1-2012', 'Group2', 'Ready'), 
	('7-1-2012', 'Group2', 'Not Ready'), 
	('7-1-2012', 'Group2', 'Not Ready')
	
SELECT * FROM #tmp

SELECT date, the_group, status, sequence_number = ROW_NUMBER() OVER (PARTITION BY date, the_group, status ORDER BY date ) + 100
FROM #tmp
ORDER BY date, the_group, status

Open in new window

0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39278106
If there's a PK on your table, then this works...

DROP TABLE #tmp

CREATE TABLE #tmp (id int identity(1,1), date datetime, the_group varchar(10), status varchar(10), sequence_number int) 

INSERT INTO #tmp (date, the_group, status)
VALUES 
	('1-1-2012', 'Group1', 'Ready'), 
	('1-1-2012', 'Group1', 'Ready'), 
	('1-1-2012', 'Group1', 'Ready'), 
	('1-1-2012', 'Group1', 'Not Ready'), 	
	('1-1-2012', 'Group2', 'Ready'), 
	('1-1-2012', 'Group2', 'Not Ready'), 
	('1-1-2012', 'Group2', 'Not Ready'), 
	('7-1-2012', 'Group1', 'Ready'), 
	('7-1-2012', 'Group1', 'Ready'), 
	('7-1-2012', 'Group1', 'Ready'), 
	('7-1-2012', 'Group1', 'Not Ready'), 	
	('7-1-2012', 'Group2', 'Ready'), 
	('7-1-2012', 'Group2', 'Not Ready'), 
	('7-1-2012', 'Group2', 'Not Ready')
	
SELECT * FROM #tmp ORDER BY id

UPDATE #tmp
SET sequence_number = rn.seq
FROM #tmp
  JOIN (
	SELECT id, ROW_NUMBER() OVER (PARTITION BY date, the_group, status ORDER BY date ) + 100 as seq
	FROM #tmp) rn ON #tmp.id = rn.id

SELECT * FROM #tmp ORDER BY id




 

Open in new window

0
 

Author Closing Comment

by:damixa
ID: 39278853
perfect, thanks
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39279010
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

726 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