Post reply

Query to create batch numbers?

  • January 10, 2007 at 7:34 pm

    #118501

    Hello,

    I have a work table that gets populated everyday.  Currently, there are three hundred records.  The schema is:

    create table myWorktbl

    (

    workID  int identity(1,1),

    workDesc varchar(20),

    workDate  datetime)

    I need to copy the table into another table with same schema except for an additional field called "Batch Number".  Here's how the data looks:

    workID     workDesc   workDate

    1             table         1/7/07

    2             chair          1/7/07

    3             sofa           1/7/07

    I need to create batch numbers:

    workID     workDesc   workDate   Batch_Num

    1             table         1/7/07       us001

    2             chair          1/7/07      us001

    3             sofa           1/7/07      us001

    after 100 records

    101          lamp           1/7/07     us002

    Any ideas on how to accomplish this?  The Batch_Num will always begin with "us".

    Thanks in advance!

    Tony

    Things will work out.  Get back up, change some parameters and recode.

  • January 10, 2007 at 7:44 pm

    #682596

    it looks like your batch number is arbityary...can it be based on the workID?

    select 'us' + RIGHT('000' + (convert(varchar,(workID / 100) + 1)),3)

     

    select 'us' + RIGHT('000' + (convert(varchar,(1 / 100) + 1)),3)  = us001

    select 'us' + RIGHT('000' + (convert(varchar,(2 / 100) + 1)),3)  = us001

    select 'us' + RIGHT('000' + (convert(varchar,(3 / 100) + 1)),3)  = us001

    select 'us' + RIGHT('000' + (convert(varchar,(101 / 100) + 1)),3)=us002

    select 'us' + RIGHT('000' + (convert(varchar,(205 / 100) + 1)),3)=us003

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 10, 2007 at 7:46 pm

    #682597

    Try this.  This formula is based on the recordid and not the number of records in the "batch".  Tell me if this is not exactly what you need.

     

    ALTER TABLE dbo.myWorktbl

    ADD Batch_Num AS 'US' + RIGHT('00' + CONVERT(VARCHAR(3), (WorkID - 1) / 100 + 1), 3)

  • January 11, 2007 at 9:10 am

    #682715

    Thanks Lowell for your help.  My batch numbers aren't arbitrary, but rather sequential.

    Ninja - you were right on the money.

    Thanks both of you!

    Things will work out.  Get back up, change some parameters and recode.

  • January 11, 2007 at 9:23 am

    #682720

    NP.

     

    The only small problem I can see is that you can only have 999 batches.  Maybe I'm just paranoid but maybe I'd use 5 numbers so that I could eventually have access to 99 999 bacthes (almost 10M rows). I don't know how you intend to use that system but this is something to think about now so you don't have something extra special to do later on.

  • January 13, 2007 at 4:11 pm

    #683100

    Excellent point and that is what I did.  I add some more zeros after "us" as part of the formula.  I now have "us" plus up to 900,000 batches.

    Thanks for pointing me in the right direction.

     

    Tony

    Things will work out.  Get back up, change some parameters and recode.

  • January 13, 2007 at 8:18 pm

    #683109

    Happy to help.

     

    Good luck with that project.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply