Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Unanswered: Unique report number that resets each Jan1

    I have to create a correspondence log that contains the year correspondence is written an a unique number that starts with [1] beginning on January1. This unique number needs to reset each january so that it begins with [1]. Any help would be appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    create the table using an ordinary autonumber
    Code:
    create table correspondence
    ( id counter not null constraint cpk primary key
    , txt varchar(10)
    )
    insert the first row, a dummy
    Code:
    insert into correspondence ( id, txt ) values ( 20040000, 'foo' )
    delete the row you just inserted
    Code:
    delete from correspondence where txt = 'foo'
    now you are ready to start adding correspondence
    Code:
    insert into correspondence ( txt ) values ( 'bar' )
    insert into correspondence ( txt ) values ( 'qux' )
    insert into correspondence ( txt ) values ( 'fap' )
    now run the following query as the very last thing you do in december
    Code:
    insert into correspondence ( id, txt ) values ( 20050000, 'foo' )
    delete the row you just inserted
    Code:
    delete from correspondence where txt = 'foo'
    now you are ready to start adding correspondence for the next year
    Code:
    insert into correspondence ( txt ) values ( 'bar' )
    want to see what all of the above has produced?
    Code:
    select * from correspondence order by id
    
    id          txt
    20040001 bar
    20040002 qux
    20040003 fap
    20050001 bar
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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