# Thread: Sequence number generating with a twist

1. Registered User
Join Date
Sep 2006
Posts
12

## Unanswered: Sequence number generating with a twist

I'm having a brain not functioning day - well who am I kidding - more like a year

I need some help with some sequence numbering and cannot even get my head around the logic I want to use, let alone the actual code.

I have a dataset with 3 fields:

Area
ID
RefNo

This table contains a list of employee ID's by Area and each employee has a RefNo (counter) in each area.

The data comes from 2 different sources and is combined in this table. Some employees had no RefNo already assigned to them so I have entered their RefNo as 10000 in order to ensure they are sorted at the bottom of the list.

The ID's that have RefNo's have to keep the one they have. Therefore, I need to create RefNo's for the ones that currently have RefNo 10000.

These numbers I create have to follow on from the highest RefNo for the Area.

For example:

Area ID RefNo
A Z 1
A Y 2
A X 3
A W 10000
A V 10000
B N 1
B O 10000
B P 10000

So, for Area A, ID's W and V would have to be assigned RefNo 4 and 5, and for Area B, ID's O and P would have to be assigned RefNo 2 and 3.

Hope this makes sense to all.

BTW, am using SQL 2000 at the moment.

Thanks in advance for any help!

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579

## Nzdf

I'm just annoyed that 2005 won't let me use a CASE to do it in a single pass!
Code:
```--  ptp  20080409  See http://www.dbforums.com/showthread.php?t=1629185

IF Object_ID('t1629185', 'U') IS NOT NULL
DROP TABLE t1629185
GO

CREATE TABLE t1629185 (
[Area]		CHAR(1)
,  [Id]			CHAR(1)
,  [Refno]		BIGINT
)

INSERT INTO t1629185 (
[Area], [Id], [Refno]
)  SELECT 'A', 'Z', 1
UNION SELECT 'A', 'Y', 2
UNION SELECT 'A', 'X', 3
UNION SELECT 'A', 'W', 10000
UNION SELECT 'A', 'V', 10000
UNION SELECT 'B', 'N', 1
UNION SELECT 'B', 'O', 10000
UNION SELECT 'B', 'P', 10000

SELECT * FROM t1629185 ORDER BY [Area], [Refno]

UPDATE t1629185
SET [Refno] = 1
+     (SELECT Max(z1.[Refno])
FROM t1629185 AS z1
WHERE  z1.[Area] = t1629185.[Area]
AND z1.[Refno] < t1629185.[Refno])
+     (Select Count(*)
FROM t1629185 AS z2
WHERE  z2.[Area] = t1629185.[Area]
AND z2.[Refno] = t1629185.[Refno]
AND z2.[Id] < t1629185.[Id])
WHERE  10000 = t1629185.[Refno]

SELECT * FROM t1629185 ORDER BY [Area], [Refno]```
-PatP

3. Registered User
Join Date
Sep 2006
Posts
12
Thanks for this Pat, it works fine to replace the 10000. unfortunately, I'm getting it replacing them with null values instead of numbers.

Any idea why this might happen?

To help me work out the logic of what I was trying to do, I put the data into excel and wrote out a little macro.

Here's what I ended up with, not sure if it helps to explain a little better:

Sub test()
init_Area = ""
init_Ref = 0

For i = 2 To int_rows - 1
If Sheet1.Range("C" & i).Value = 10000 Then
If Sheet1.Range("A" & i).Value = init_Area Then
Sheet1.Range("C" & i).Value = init_Ref + 1
Else
Sheet1.Range("C" & i).Value = 1
End If
End If

init_Area = Sheet1.Range("A" & i).Value
init_Ref = Sheet1.Range("C" & i).Value
Next i
End Sub
Last edited by daisywheel; 04-10-08 at 05:56.

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
So you ran my script and didn't get:
Code:
```(8 row(s) affected)

Area Id   Refno
---- ---- --------------------
A    Z    1
A    Y    2
A    X    3
A    V    10000
A    W    10000
B    N    1
B    O    10000
B    P    10000

(8 row(s) affected)

(4 row(s) affected)

Area Id   Refno
---- ---- --------------------
A    Z    1
A    Y    2
A    X    3
A    V    4
A    W    5
B    N    1
B    O    2
B    P    3

(8 row(s) affected)```
-PatP

5. Registered User
Join Date
Sep 2006
Posts
12
I ran the update bit against my actual table - substituting the correct fieldnames where required.

I'll try it with your actual table now and see if I can find the answer. Sorry, juggling a few things and didn't think of that.

6. Registered User
Join Date
Sep 2006
Posts
12
............

7. Registered User
Join Date
Sep 2006
Posts
12
OK, ran your text and messed about with the letters a bit to test if the problem was related to the data order and it all worked fine. When applied to my actual data though, it replaces with null instead of furthering the sequence...

8. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Ok, minor twist to deal with a special case.
Code:
```--  ptp  20080409  See http://www.dbforums.com/showthread.php?t=1629185

IF Object_ID('t1629185', 'U') IS NOT NULL
DROP TABLE t1629185
GO

CREATE TABLE t1629185 (
[Area]		CHAR(1)
,  [Id]			CHAR(1)
,  [Refno]		BIGINT
)

INSERT INTO t1629185 (
[Area], [Id], [Refno]
)  SELECT       'A', 'Z', 1
UNION SELECT 'A', 'Y', 2
UNION SELECT 'A', 'X', 3
UNION SELECT 'A', 'W', 10000
UNION SELECT 'A', 'V', 10000
UNION SELECT 'B', 'N', 1
UNION SELECT 'B', 'O', 10000
UNION SELECT 'B', 'P', 10000

SELECT * FROM t1629185 ORDER BY [Area], [Refno]

UPDATE t1629185
SET [Refno] = 1
+     (SELECT Coalesce(Max(z1.[Refno]), 0)
FROM t1629185 AS z1
WHERE  z1.[Area] = t1629185.[Area]
AND z1.[Refno] < t1629185.[Refno])
+     (Select Count(*)
FROM t1629185 AS z2
WHERE  z2.[Area] = t1629185.[Area]
AND z2.[Refno] = t1629185.[Refno]
AND z2.[Id] < t1629185.[Id])
WHERE  10000 = t1629185.[Refno]

SELECT * FROM t1629185 ORDER BY [Area], [Refno]```
-PatP

9. Registered User
Join Date
Sep 2006
Posts
12
Thanks for this Pat, Unfortunately, I'm still having the same problem. I shall throw some code your way with a sample of the data I'm working with in it and see if that helps at all.

10. Registered User
Join Date
Sep 2006
Posts
12
Here's the code I put together to show you where it fails:

Code:
```if exists (select * from sysobjects where id = object_id(N'[dbo].[tbl_CR]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_CR]
GO

CREATE TABLE  [tbl_CR] (
[Budget_Filename] [varchar] (255) COLLATE SQL_Latin1_General_CP850_CI_AI NULL ,
[RefNo] [int] NULL ,
[SAP_Pers_Num] [varchar] (255) COLLATE SQL_Latin1_General_CP850_CI_AI NULL
)

INSERT INTO [tbl_CR] ( [Budget_Filename], [RefNo], [SAP_Pers_Num]) SELECT '100 - PC',10000, '00000000  Unallocated Unallocated' UNION SELECT '100 - PC',10000, '10000000  Unallocated Unallocated' UNION SELECT '100 - PC',10000, '20000000  Unallocated Unallocated' UNION SELECT '100 - PC',10000, '30000000  Unallocated Unallocated' UNION SELECT '100 - PC',10000, '40000000  Unallocated Unallocated' UNION SELECT '101 - MT EN',10000, '50000000  Unallocated Unallocated' UNION SELECT '101 - MT EN',10000, '60000000  Unallocated Unallocated' UNION SELECT '101 - MT EN',10000, '70000000  Unallocated Unallocated' UNION SELECT '101 - MT EN',1, '80000000  Unallocated Unallocated' UNION SELECT '101 - MT EN',2, '90000000  Unallocated Unallocated' UNION SELECT '101 - MT EN',3, '11000000 Unallocated Unallocated' UNION SELECT '102 - MT AA',1, '12000000 Unallocated Unallocated' UNION SELECT '102 - MT AA',2, '13000000 Unallocated Unallocated' UNION SELECT '102 - MT AA',3, '14000000 Unallocated Unallocated' UNION SELECT '102 - MT AA',4, '15000000 Unallocated Unallocated' UNION SELECT '102 - MT AA',9, '16000000 Unallocated Unallocated' UNION SELECT '102 - MT AA',10, '17000000 Unallocated Unallocated' UNION SELECT '102 - MT AA',10, '17000000 Unallocated Unallocated' UNION SELECT '102 - MT AA',11, '18000000 Unallocated Unallocated' UNION SELECT '102 - MT AA',12, '19000000 Unallocated Unallocated' UNION SELECT '102 - MT AA',14, '21000000 Unallocated Unallocated' UNION SELECT '102 - MT AA',15, '22000000 Unallocated Unallocated' UNION SELECT '102 - MT AA',10000, '23000000 Unallocated Unallocated' UNION SELECT '102 - MT AA',10000, '24000000 Unallocated Unallocated' UNION SELECT '102 - MT AA',10000, '25000000 Unallocated Unallocated' UNION SELECT '102 - MT AA',10000, '26000000 Unallocated Unallocated' UNION SELECT '102 - MT AA',10000, '27000000 Unallocated Unallocated' UNION SELECT '102 - MT AA',10000, '28000000 Unallocated Unallocated'

SELECT * FROM [tbl_CR] ORDER BY [Budget_Filename], [RefNo]

UPDATE tbl_CR
SET [RefNo] = 1
+     (SELECT Coalesce(Max(z1.[RefNo]),0)
FROM tbl_CR AS z1
WHERE  z1.[Budget_Filename] = tbl_CR.[Budget_Filename]
AND z1.[RefNo] < tbl_CR.[RefNo])
+     (Select Count(*)
FROM tbl_CR AS z2
WHERE  z2.[Budget_Filename] = tbl_CR.[Budget_Filename]
AND z2.[RefNo] = tbl_CR.[RefNo]
AND z2.[SAP_Pers_Num] < tbl_CR.[SAP_Pers_Num])
WHERE  10000 = tbl_CR.[RefNo];

SELECT * FROM tbl_CR ORDER BY [Budget_Filename], [RefNo];```
Unbelievably, this works fine though, yet on my actual data, I get nulls VERY frustrating.

Will play around and see if I can get it working. If you have any ideas what might be going wrong with my data, then I'd love to hear it

Either way, Thanks so much for your help, it's got me much closer to a solution.
Last edited by daisywheel; 04-11-08 at 06:53.

11. Registered User
Join Date
Sep 2006
Posts
12
Would you believe it, I've managed to get it to work! I had 1000000 in my table rather than 10000. I did alter your code to take that into account before. So, this time instead, I altered the 1000000 to 10000 at it all works fine.

Thanks so much for this help. I owe you a drink or several for this!

12. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579