Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2006
    Posts
    12

    Red face 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. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54

    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. #3
    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()
    int_rows = Range(Sheet1.Range("A1").CurrentRegion.Address).Ro ws.Count
    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 06:56.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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. #5
    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. #6
    Join Date
    Sep 2006
    Posts
    12
    ............

  7. #7
    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. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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. #9
    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. #10
    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 07:53.

  11. #11
    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. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Dang those frisky zeros! I just hate it when they drop a hammer on a solution!

    Ahhh SAP. I'm currently up to my eyebrows in PeopleSoft, but I see SAP coming over the horizion. Kind of like when you see a light at the end of a tunnel, then the light starts to honk at you...

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •