Link to home
Create AccountLog in
Avatar of wsturdev
wsturdevFlag for United States of America

asked on

How to generate a sequence number in a query?

I have the following SQL statement:
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
SOLUTION
Avatar of Daniel Wilson
Daniel Wilson
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of wsturdev

ASKER

Added a comma after line 4, and changed "Q1iNSTANCE = Q1.Instance" in line 8 to "Q1iNSTANCE = Q3.Instance".

I get the following error:
Msg 8120, Level 16, State 1, Line 1
Column 'vw_Info.Instance' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT   Q1.Instance, 
Seq = Case when count(Q2.Instance) = 1 and Q3.Instance is NULL then NULL
  Else count(Q2.Instance) 
END,
Q1.Phase, Q1.Start_Date, Q1.End_Date, Q1.Resource
FROM         vw_Info Q1 Left Join 
   vw_Info Q2 On Q1.Instance = Q2.Instance And Q1.Resource >= Q2.Resource Left Join
   Vw_Info Q3 On Q1.iNSTANCE = Q3.Instance and Q1.Resource < Q3.Resource
Group By Q1.Instance, Q1.Resource
ORDER BY Q1.Instance, Q1.Resource

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account