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 |
|
tariqali
Starting Member
7 Posts |
 Posted - 2005-03-31 : 08:33:40
|
| I have been serching through the net for a solution to this problem and haven't been successfull yet. I finally came across this forum and I have good feeling that I might find an answer here. Basically I have a table with duplicate numbers in [UNIQ_TRB] field. I need to create a count for each SET of duplicates to start from 0 or 1. These are the fields in question: UNIQ_TRB -- nvarchar SUBSEQ -- int RECDT-- datetime This is how he records look like: UNIQ_TRB...........SUBSEQ..........RECDT 000-000-0000.......................01/01/2005 08:00:00am 000-000-0000.......................01/01/2005 08:15:00am 000-000-0000.......................01/02/2005 09:10:00am 000-000-0000.......................01/03/2005 07:00:00am 111-111-1111.......................01/01/2005 06:00:00am 111-111-1111.......................01/03/2005 09:00:00am 111-111-1111.......................01/09/2005 09:20:00am 222-222-2222.......................01/09/2005 10:15:00am 222-222-2222.......................01/10/2005 06:00:00am 333-333-3333.......................01/01/2005 06:00:00am This is how I wanted the counts to be after running the update query: UNIQ_TRB........SUBSEQ........RECDT 000-000-0000.....0...........01/01/2005 08:00:00am 000-000-0000.....1...........01/01/2005 08:15:00am 000-000-0000.....2...........01/02/2005 09:10:00am 000-000-0000.....3...........01/03/2005 07:00:00am 111-111-1111.....0...........01/01/2005 06:00:00am 111-111-1111.....1...........01/03/2005 09:00:00am 111-111-1111.....2...........01/09/2005 09:20:00am 222-222-2222.....0...........01/09/2005 10:15:00am 222-222-2222.....1...........01/10/2005 06:00:00am 333-333-3333.....0...........01/01/2005 06:00:00am Notice how the numbering start with 0 based on the ealiest RECDT date and time. This table used to be in Access database and I used a module to accomplish this. Now we are moving to MS SQL and I am not sure how to do this there. Any idea! Any help would be TRULY appreciated. Thanks. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-03-31 : 09:18:47
|
| Try thisSelect UNIQ_TRB, (select count(*)-1 from YourTable where UNIQ_TRB =T.UNIQ_TRB and RECDT<=T.RECDT),RECDT from yourTable TMadhivananFailing to plan is Planning to fail |
 |
|
|
tariqali
Starting Member
7 Posts |
Posted - 2005-03-31 : 09:34:44
|
| This will only give me a total count. That is not what I am looking for.In the example in my first post, the second table, notice how the SUBSEQ field is generated. For each set of duplicates, it start with 0 for the one with the ealiest RECDT date and time, then 1 for the second record with the ealiest RECDT date and time and so on...Then it start all over again with 0 with the next set of duplicates. If the UNIQ_TRB is a uniqe record, then it will only have 0 in the SUBSEQ field.Any idea???thanks. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-03-31 : 23:39:06
|
| Look at thisdeclare @t table(i varchar(10),d datetime)insert into @t values('111111',getdate()-0.005)insert into @t values('111111',getdate()-0.004)insert into @t values('111111',getdate()-0.003)insert into @t values('111166',getdate()-0.002)insert into @t values('111166',getdate()-0.001)insert into @t values('111166',getdate()-0.0044)insert into @t values('111166',getdate()-0.0045)insert into @t values('111166',getdate()-0.0046)insert into @t values('111117',getdate()-0.0047)insert into @t values('111117',getdate()-0.0048)insert into @t values('111117',getdate()-0.0049)select i,(select count(*)-1 from @t where i=T.i and d<=T.d) as Sno ,d from @t torder by i,dMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Duplicate Records Count Loop