Generating a Sequence [message #332599] |
Wed, 09 July 2008 02:28  |
bholaimmu
Messages: 35 Registered: October 2007
|
Member |
|
|
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? It needs to produce the following:
Instance Seq Phase Start_Date End_Date Resource
1001-1 1 Phase 1 1/1/2008 1/31/2008 Jones, Jason
1001-2 1 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 1 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 1 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
Its very urgent…
Thx N Regs,
|
|
|
|
|