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.
| Author |
Topic |
|
anabelle
Starting Member
19 Posts |
 Posted - 2009-04-03 : 01:04:18
|
| 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 exampleSELECT Instance, Phase, Start_Date, End_Date, ResourceFROM vw_InfoORDER BY InstanceIt produces results as follows:Instance Phase Start_Date End_Date Resource1001-1 Phase 1 1/1/2008 1/31/2008 Jones, Jason1001-2 Phase 1 1/1/2008 1/31/2008 Jones, Jason1001-3 Phase 1 1/1/2008 1/31/2008 Garcia, Juan1001-3 Phase 1 1/1/2008 1/31/2008 Martin, Ray1001-4 Phase 1 1/1/2008 1/31/2008 Martin, Ray1001-5 Phase 1 1/1/2008 1/31/2008 Garcia, Juan1001-5 Phase 1 1/1/2008 1/31/2008 Martin, Ray1001-5 Phase 1 1/1/2008 1/31/2008 Smith, Jane1001-6 Phase 1 1/1/2008 1/31/2008 Martin, Ray1001-7 Phase 1 1/1/2008 1/31/2008 Martin, Ray1001-7 Phase 1 1/1/2008 1/31/2008 Garcia, Juan1001-8 Phase 1 1/1/2008 1/31/2008 Garcia, Juan1001-8 Phase 1 1/1/2008 1/31/2008 Martin, RayHow 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 Resource1001-1 NULL Phase 1 1/1/2008 1/31/2008 Jones, Jason1001-2 NULL Phase 1 1/1/2008 1/31/2008 Jones, Jason1001-3 1 Phase 1 1/1/2008 1/31/2008 Garcia, Juan1001-3 2 Phase 1 1/1/2008 1/31/2008 Martin, Ray1001-4 NULL Phase 1 1/1/2008 1/31/2008 Martin, Ray1001-5 1 Phase 1 1/1/2008 1/31/2008 Garcia, Juan1001-5 2 Phase 1 1/1/2008 1/31/2008 Martin, Ray1001-5 3 Phase 1 1/1/2008 1/31/2008 Smith, Jane1001-6 NULL Phase 1 1/1/2008 1/31/2008 Martin, Ray1001-7 1 Phase 1 1/1/2008 1/31/2008 Martin, Ray1001-7 2 Phase 1 1/1/2008 1/31/2008 Garcia, Juan1001-8 1 Phase 1 1/1/2008 1/31/2008 Garcia, Juan1001-8 2 Phase 1 1/1/2008 1/31/2008 Martin, RayPlease let me know if someone cud give me possible ways how to do it...Thank you so much.. |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
|
|
|
|
|
|
|
How to generate sequence number in a querr?