Loop and Update a Field

[pator]

Folks
I have an access database to convert to convert into SQL 2000, Most of the queries are fine to convert as they are select type queries and easy to follow. There are some which are more difficult such as the following problem.

I have this table called TblCombine
Sample data

Ldg , Bnk , Combine , Dupe,
BQ 55.23 BQ55.23 0
BQ 65.23 BQ65.23 0
BQ 75.23 BQ75.23 0
BQ 85.23 BQ85.23 0
BQ 85.23 BQ85.23 0
BQ 85.23 BQ85.23 0
Int 95.23 Int95.23 0
Int 105.23 Int105.23 0
Int 95.23 Int95.23 0
Int 95.23 Int95.23 0

I join the Ldg and Bnk fields into the Combine field.
My problem is I need a process to Order the Combine field.
Then loop through the combine field and if it's not equal to the combine field above it the Dupe field is set to 1, if it is equal the field above it I set to 2 and so on,


Expected Result
Ldg , Bnk , Combine , Dupe,
BQ 55.23 BQ55.23 1
BQ 65.23 BQ65.23 1
BQ 75.23 BQ75.23 1
BQ 85.23 BQ85.23 1
BQ 85.23 BQ85.23 2
BQ 85.23 BQ85.23 3
Int 95.23 Int95.23 1
Int 105.23 Int105.23 1
Int 95.23 Int95.23 1
Int 95.23 Int95.23 2


I have done this in VB where in access as I can follow the process but I am lost at the moment in SQL.
Regards & Thanks
Pator

 

[gmmastros]

May I ask... Why are you converting to SQL 2000? I mean... I understand converting away from Access, but why 2000. SQL 2005 has been out for a very long time, and even SQL 2008 has been out for a while now.

The reason I ask is.... it's easy to do this with SQL2005 or SQL2008. Less easy with SQL2000.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

DECLARE @Test TABLE (Ldg CHAR(2) , 
                     Bnk  numeric(6,2),
                     Combine varchar(10),
                     Dupe int IDENTITY(1,1))
INSERT INTO @Test (Ldg, Bnk, Combine)
SELECT Ldg, Bnk, Ldg+CAST(Bnk as varchar(20)) 
FROM YourTable
ORDER BY Ldg, Bnk, Combine


SELECT Ldg, Bnk, Combine,
       Dupe - Tbl1.Dupe + 1 AS Dupe
FROM @Test Test
INNER JOIN (SELECT Ldg, Bnk, MIN(Dupe) AS Dupe
                   FROM @Test
            GROUP BY Ldg, Bnk) Tbl1
       ON Test.Ldg = Tbl1.Ldg
          Test.Bnk = Tbl1.Bnk

NOT TESTED!!!

And I'm agree with George. WHY?



Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[pator]

I suppose For the same reason I still use Office 97 , Unfortunately I have no control over what version I use, I'm piggy backing on another guy's server for SQL test purposes , So I just have to live with at the moment.

Thanks for your reply will check it out this morning.
Regards
Pator

 

[pator]

bborissov

Thanks for your help and the code to sort my problem.
I used your solution with some tiny tweaking and the results are below.
Regards
Pator

DECLARE @Test table (Ldg CHAR(3) , 
                     Bnk  numeric(6,2),
                     Combine varchar(50),
                     Dupe int IDENTITY(1,1))

INSERT INTO @Test (Ldg, Bnk, Combine)

SELECT Ldg, Bnk, Ldg+CAST(Bnk as varchar(25)) 

FROM TblCombine
ORDER BY  TblCombine.LDG , TblCombine.BNK , TblCombine.COMBINE

SELECT TblCombine.LDG, TblCombine.BNK,   TblCombine.LDG + cast(TblCombine.BNK as varchar(25)) as Combine ,
      test.Dupe - TblCombine.Dupe + 1 AS Dupe
FROM @Test test


INNER JOIN (SELECT Ldg, Bnk, MIN(Dupe) AS Dupe
            FROM @Test
            GROUP BY Ldg, Bnk)Tblcombine
   on Test.Ldg = TblCombine.Ldg
   where  test.Bnk = TblCombine.Bnk
[/code Used]
Results
BQ 	55.23	BQ 55.23	1
BQ 	65.23	BQ 65.23	1
BQ 	75.23	BQ 75.23	1
BQ 	85.23	BQ 85.23	1
BQ 	85.23	BQ 85.23	2
BQ 	85.23	BQ 85.23	3
BQ 	95.23	BQ 95.23	1
INT	95.23	INT95.23	1
INT	95.23	INT95.23	2
INT	95.23	INT95.23	3
INT    105.23  INT105.23	1

 

[pator]

bborissov
Thanks for your previous reply to the above, I assumed (wrongly I think ) that I could use the variables in your reply to update the Dupe field with the value in the variable. It runs fine for a select query.

My question is how can I use these correct results from the select query and update the TblCombine.Dupe field with the correct values from the select query.
Or do I need a different strategy.

I realise now I should have mentioned the update part in my initial question. That stems from my limited experience in SQL.
Regards & Thanks
Pator