Auto Generated Serial Number using Stored Procedure

Question

I want to create a procedure which would create a serial number using a stored procedure.

I have three tables:

Table 1:

create table ItemTypes
(
    ItemTypeID int not null primary key,
    ItemType varchar(30)
)

Table 2:

create table ItemBatchNumber
(
     ItemBatchNumberID int not null primary key,
     ItemBatchNumber varchar(20),
     ItemType varchar(30),
)

Table 3:

 create table ItemMfg
    (
        ManufactureID int not null primary key,
        ItemBatchNumberID int foreign key references     ItemBatchNumber(ItemBatchNumberID),
SerialNumber varchar(10),
        MfgDate datetime 
    )

For each Item Type there are several Item batch number.

Now, first 3 digit of serial no is xyz. 4 digit of serial no should be Item Type(e.g if Item type is 'b' then serial no should be xyzb).

5 digit of serial no should be like this:

In a day, for first Item batch number of a Item type- 5th digit should be 1 and it will remain 1 for that day.For the next Item batch number it should be 2 and it will remain 2 for that day.

For next day same rule applied.

e.g suppose 'b' Item Type has 3 Item batch number WB1,WB2,WB3. If today someone select WB2(Item batch number) of 'b' Item Type first then Serial No should be xyzb1 and it will remain xyzb1 for today for WB2. Now if someone select WR1 next then Serial No should be xyzb2 for today. Tomorrow which Item batch number of 'b' Item type will be selected first, for that batch number and that type serial no should be xyzb1. Same rule applied for other item type.

I have tried till now:

   create procedure Gen_SerialNumber
    (
    @ManufactureID int,
    @IitemType varchar(30),
    @ItemBatchNumberID int,
    @Date datetime,
     @SerialNumber out,
    @fifthDigit int out
    )
    AS
    Begin 
    set @IitemType=(Select ItemType from ItemBatchNumber where ItemBatchNumber=@ItemBatchNumber)
    Declare @SerialNumber1 varchar(20)
    Set @SerialNumber1= 'xyz'+''+@IitemType+''+CAST( (Select COUNT(distinct ItemBatchNumber)from ItemBatchNumber
    where   ItemType=@IitemType) as varchar (10) )
    Set @fifthDigit=SUBSTRING(@SerialNumber1,5,1)
   IF EXISTS(SELECT SerialNumber FORM ItemMfg WHERE SerialNumber=null or         SerialNumber!=@SerialNumber)
                          SET @fifthDigit=1
        IF EXISTS(SELECT mfgDate,ItemBatchNumberID FROM ItemMfg WHERE mfgDate=@Date and ItemBatchNumberID=@ItemBatchNumberID)
                          SET @fifthDigit=1
                     ELSE 
                          SET @fifthDigit=@fifthDigit+1
    SET @SerialNumber=('xyz'+''+@ItemType+''+cast(@fifthdigit as varchar(2)))
INSERT INTO ItemMfg VALUES(@ItemType,@ItemBatchNumberID,@SerialNumber,@Date)
END

I am new to SQL. 4rth digit of SN is generated correctly from my code. I am facing problem on how to increment the value of fifth digit checking with dates when next different item batch number of a same item type or different item type is used. Please let me know if you have any doubt. Thanks in advance.

Answer 1

A couple of concerns with your question: I am confused by the appearance of the WireBatchNumber column in your stored procedure, as that is not included in the table definition you provided. Secondly, the last @ItemType (right before the end command) is misspelled.

I think the challenge here is that you need your stored procedure increment a variable across batches and to "start over" each day. That would suggest to me that you need the procedure to 1. Track the last time it was run and see if it was today or not. 2. Track how many times it has been run today.

It is not a very beginner level knowledge type of task, but there is a way, apparently: how to declare global variable in SQL Server..?. Using the type of variable mentioned in this link, you could set up some conditional structures that compare a date variable to today using the DateDiff() function, changing the date variable and resetting your counters if they the two dates are on different days, then incrementing a counter for the item batch number and using this counter to provide the fifth digit.

Answer 2

Let's populate some data:

ItemTypes:

ItemTypeID  ItemType
1           a
2           b
3           c

ItemBatchNumber:

ItemBatchNumberID   ItemBatchNumber ItemType
...
11                  WB1             b
22                  WB2             b   !
33                  WB3             b   !!
44                  WB1             c
55                  WB2             c   !
66                  WB3             c   !!
77                  WB3             c   !!
...

ItemMfg:

ManufactureID   ItemBatchNumberID   MfgDate
111             22                  2015-03-01 7:00 -> xyzb1
222             11                  2015-03-01 8:00 -> xyzb1
333             22                  2015-03-01 9:00 -> xyzb2

444             33                  2015-03-02 5:00 -> xyzb1
555             33                  2015-03-02 6:00 -> xyzb2
666             11                  2015-03-02 7:00 -> xyzb1
777             33                  2015-03-02 8:00 -> xyzb3
888             11                  2015-03-02 9:00 -> xyzb2
999             22                  2015-03-02 9:35 -> xyzb1

I see some inappropriate things - it does not necessary means there are mistakes. Sorry, I do not know real business rules - they may explain things differently. I assume the simplest and most usable logic.

1. ItemTypes - is looks like lookup table. But you do not use ItemTypeID, instead you use it's value (a,b,c) as unique code.

So table should looks like this:

ItemType(PK)    ItemTypeDescr
a               Type A
b               Most Usable Type B
c               Free text for type C

2. ItemBatchNumber - match table - define matches between batches and ItemTypes. It may have data as marked with "!", and this is valid.

To avoid 77 need add some unique index or set PK on ItemBatchNumber+ItemType. But in any case sets as 22,55 and 11,44.. are normaly expected. So your SP will fails.

3. Here you will get an error.

Here query may return multipple rows (22, 55):

set @IitemType=(Select ItemType from ItemBatchNumber where ItemBatchNumber=@ItemBatchNumber)

alternatively:

Select @IitemType = ItemType from ItemBatchNumber where ItemBatchNumber=@ItemBatchNumber

in case of multipple rows it will return last one.

But anyway it is not correct - if @ItemBatchNumber = 'WB2' which value is expected 'b' or 'c'?

4. errors:

...

... and ItemTypeID=@IitemType ...

ItemTypeID is int; @IitemType is char ('b') - what do you expect?

...

MfgDate=@Date

Dates '2015-03-02 5:00' and '2015-03-02 7:00' are not the same, but in the same day.

...

...from ItemBatchNumber,ItemMfg
where MfgDate=@Date 
    and ItemBatchNumber=@ItemBatchNumber 
    and ItemTypeID=@IitemType

even if ItemBatchNumber will return one row, and date will not count time, it will return ALL batches from ItemMfg from one day. you need to do proper join.

...

Select COUNT(distinct ItemBatchNumber)

you always will need (count() + 1), and not a distinct

5. I am not sure, when you need to generate SN:

a. at the moment when (before) you ADD new ItemMfg, then need to check for current day.

b. for any existed ItemMfg row (for 555), then you need to exclude from count() all rqws after 555.

---

That's how query may looks:

a. (on adding new ItemMfg-row - pass values used for creation ItemMfg-row)

create procedure AddNewItemMfg    -- ...and generate SN
(
   @ItemBatchNumberID int,
   @MfgDate datetime        -- pass getdate()
) AS Begin 

-- calc 5th digit:
declare @fifthDigit int;
select 
    @fifthDigit = count(*) + 1
from ItemBatchNumber AS bb
inner join ItemMfg ii ON ii.ItemBatchNumberID = bb.ItemBatchNumberID 
where bb.ItemBatchNumberID = @ItemBatchNumberID             -- single ItemBatchNumber-row to get ItemType
    and ii.MfgDate <= @MfgDate                              -- all previous datetimes
    and cast(ii.MfgDate as date) = cast(@MfgDate as date)   -- same day

-- ManufactureID is Identity (i.e. autoincremented)
INSERT INTO ItemMfg (ItemBatchNumberID, MfgDate, SerialNumber)
    SELECT @ItemBatchNumberID
        , @MfgDate
        , 'xyz' + bb.ItemType + cast(@fifthDigit as varchar(5))
    FROM ItemBatchNumber bb
    WHERE bb.ItemBatchNumber = @ItemBatchNumber
;

end

b. for any already existed ItemMfg-row

create procedure GenerateSNforExistedItemMfg
(
   @ManufactureID int
) AS Begin 

-- generate SN - same as previous but in single query
declare @SN varchar(10);
Select @SN = 'xyz'
    + bb.ItemType
    + cast(
        select count(*) + 1
        from ItemMfg mm                         -- single row from mm
        inner join ItemBatchNumber AS bb        -- single row from bb
            ON bb.ItemBatchNumberID = mm.ItemBatchNumberID
        inner join ItemMfg ii                   -- many matched rows
            ON ii.ItemBatchNumberID = bb.ItemBatchNumberID 
        where mm.ManufactureID = @ManufactureID -- single row from mm
            and ii.MfgDate <= mm.MfgDate        -- all previous datetimes
            and cast(ii.MfgDate as date) = cast(mm.MfgDate as date) -- same day

    as varchar(5))
FROM ItemBatchNumber AS bb
INNER JOIN ItemMfg ii ON ii.ItemBatchNumberID = bb.ItemBatchNumberID 
WHERE ii.ManufactureID = @ManufactureID 

-- update SN
UPDATE ItemMfg SET
    SerialNumber = @SN
WHERE ii.ManufactureID = @ManufactureID;

end