How to generate sequence number in a query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to generate sequence number in a query?

Author

Topic

anabelle
Starting Member

19 Posts

Posted - 2009-04-03 : 05:31:21

The scenario presented below was searched tru the net which has similarity on the data and processing i need in the office, if can somebody help me have an sql query script that i could apply also here which is very related to this scenario..please..

I have the following SQL statement:taken from the net as an example
SELECT Instance, Phase, Start_Date,
End_Date, Resource
FROM vw_Info
ORDER BY Instance

It produces results as follows:
Instance Phase Start_Date End_Date Resource
1001-1 Phase 1 1/1/2008 1/31/2008 Jones, Jason
1001-2 Phase 1 1/1/2008 1/31/2008 Jones, Jason
1001-3 Phase 1 1/1/2008 1/31/2008 Garcia, Juan
1001-3 Phase 1 1/1/2008 1/31/2008 Martin, Ray
1001-4 Phase 1 1/1/2008 1/31/2008 Martin, Ray
1001-5 Phase 1 1/1/2008 1/31/2008 Garcia, Juan
1001-5 Phase 1 1/1/2008 1/31/2008 Martin, Ray
1001-5 Phase 1 1/1/2008 1/31/2008 Smith, Jane
1001-6 Phase 1 1/1/2008 1/31/2008 Martin, Ray
1001-7 Phase 1 1/1/2008 1/31/2008 Martin, Ray
1001-7 Phase 1 1/1/2008 1/31/2008 Garcia, Juan
1001-8 Phase 1 1/1/2008 1/31/2008 Garcia, Juan
1001-8 Phase 1 1/1/2008 1/31/2008 Martin, Ray

How do I modify my query so it generates a sequence number that starts over when the Instance changes, but only for those instances where there are multiple records? It needs to produce the following:
Instance Seq Phase Start_Date End_Date Resource
1001-1 NULL Phase 1 1/1/2008 1/31/2008 Jones, Jason
1001-2 NULL Phase 1 1/1/2008 1/31/2008 Jones, Jason
1001-3 1 Phase 1 1/1/2008 1/31/2008 Garcia, Juan
1001-3 2 Phase 1 1/1/2008 1/31/2008 Martin, Ray
1001-4 NULL Phase 1 1/1/2008 1/31/2008 Martin, Ray
1001-5 1 Phase 1 1/1/2008 1/31/2008 Garcia, Juan
1001-5 2 Phase 1 1/1/2008 1/31/2008 Martin, Ray
1001-5 3 Phase 1 1/1/2008 1/31/2008 Smith, Jane
1001-6 NULL Phase 1 1/1/2008 1/31/2008 Martin, Ray
1001-7 1 Phase 1 1/1/2008 1/31/2008 Martin, Ray
1001-7 2 Phase 1 1/1/2008 1/31/2008 Garcia, Juan
1001-8 1 Phase 1 1/1/2008 1/31/2008 Garcia, Juan
1001-8 2 Phase 1 1/1/2008 1/31/2008 Martin, Ray

Please let me know if someone cud give me possible ways how to do it...Thank you so much..

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-03 : 06:08:38

try this once


declare @t table(Instance varchar(32),Phase varchar(32),Start_Date datetime,End_Date datetime, Resource varchar(32))
insert into @t select '1001-1','Phase 1','1/1/2008','1/31/2008','Jones,Jason'
insert into @t select '1001-2','Phase 1','1/1/2008','1/31/2008','Jones,Jason'
insert into @t select '1001-3','Phase 1','1/1/2008','1/31/2008','Garcia,Juan'
insert into @t select '1001-3','Phase 1','1/1/2008','1/31/2008','Martin,Ray'
insert into @t select '1001-4', 'Phase 1', '1/1/2008', '1/31/2008', 'Martin, Ray'
insert into @t select '1001-5', 'Phase 1', '1/1/2008', '1/31/2008', 'Garcia, Juan'
insert into @t select '1001-5', 'Phase 1', '1/1/2008', '1/31/2008', 'Martin, Ray'
insert into @t select '1001-5', 'Phase 1', '1/1/2008', '1/31/2008', 'Smith, Jane'
insert into @t select '1001-6', 'Phase 1', '1/1/2008', '1/31/2008', 'Martin, Ray'
insert into @t select '1001-7', 'Phase 1', '1/1/2008', '1/31/2008', 'Martin, Ray'
insert into @t select '1001-7', 'Phase 1', '1/1/2008', '1/31/2008', 'Garcia, Juan'
insert into @t select '1001-8', 'Phase 1', '1/1/2008', '1/31/2008', 'Garcia, Juan'
insert into @t select '1001-8', 'Phase 1', '1/1/2008' ,'1/31/2008', 'Martin, Ray'

select *,case when count(instance) over (partition by instance) > 1 then row_number()over (partition by instance order by instance) else null end as seq 
from @t

maeenul
Starting Member

20 Posts

Posted - 2009-04-04 : 06:28:19

select Instance,
case count(Instance) over (partition by Instance)
when 1 then null
else Row_Number() over (partition by Instance order by INstance)
end,
Phase,
start_date,
end_date,
Resource
from vw_Info

-----------------------
maeenul
http://www.programmingsolution.net
http://sqlservertipsntricks.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources