Bahan 6 - AutoNumber

Field AutoNumber

Definisi Autonumber

Autonumber  adalah field yang isinya akan secara otomatis terisi menurut urutan tertentu.

Field Autonumber dapat dibuat dengan 2 cara :

1.    Menggunakan field Identity (Autonumber).  Field ini harus bertipe Int atau Numeric, kemudian tentukan Seed (Nilai dimulai dari berapa), Increment (Pertambahan Nilai)

2.    Menggunakan StoreProcedure / Trigger / Function.  Field ini akan dibuat oleh program dengan algoritma sbb :

1.    Cari Nomor Akhir (Nomor terakhir yang ada di tabel)

2.    Buat Nomor Baru (Nomor Akhir ditambah 1, dengan asumsi incrementnya 1)

3.    Insert data dengan pada Field Autonumber dengan Nomor Baru

Field Autonumber Menggunakan Field Identity

Sebagai contoh kita akan membuat urutan sebagai berikut :

Autonumber  adalah field yang isinya akan secara otomatis terisi menurut urutan tertentu.

LATIHAN AUTONUMBER PAKAI FIELD INDENTITY

1. NIP : field int dengan identity seed 1000, increment 1,   NAMA : varchar(50)

NIP	NAMA
1000	A
1001	B
1002	C

SQL : select NIP, NAMA FROM TABEL1

2.    NIP1 : field char(2), default value 'P-'; NIP2 : field int dengan identity seed 1000, increment 1;   NAMA : varchar(50)

NIP	NAMA
P-1000	A
P-1001	B
P-1002	C

SQL : select NIP1+CONVERT(VARCHAR,NIP2) AS NIP, NAMA FROM TABEL2

3.    NIP1 : field char(2), default value 'P-'; NIP2 : field int dengan identity seed 1000, increment 1, NIP3 : field char(4), default value datepart(yyyy,getdate());   NAMA : varchar(50)

NIP	NAMA
P-1000/2008	A
P-1001/2008	B
P-1002/2008	C

SQL : select NIP1+CONVERT(VARCHAR,NIP2)+'/'+convert(varchar,NIP3) AS NIP, NAMA FROM TABEL3

4. NIP1 : field char(4), default value datepart(yyyy,getdate()) ; NIP2 : field int dengan identity seed 100, increment 1; TGLLAHIR : smalldatetime  NAMA : varchar(50)

NIP	TGLLAHIR	NAMA
2008-100-198003	18/03/1980	A
2008-101-197406	30/06/1974	B
2008-102-198104	01/04/1981	C

SQL : select rtrim(NIP1)+'-'+CONVERT(VARCHAR,NIP2)+'-'+

convert(varchar, datepart(yyyy,TGLLAHIR))+

convert(varchar, datepart(mm,TGLLAHIR)) AS NIP, NAMA,TGLLAHIR FROM TABEL4

AUTONUMBER MENGGUNAKAN STOREPROCEDURE

Buat tabel TblPeg dengan struktur berikut

 NIP : CHAR(4),   NAMA : varchar(50)

NIP	NAMA
1000	A
1001	B
1002	C

SQL : select NIP, NAMA FROM TblPeg

CREATE PROCEDURE SP_TambahPeg

  @Nama varchar(50)

AS

BEGIN

DECLARE @NOAKHIR INT

DECLARE @NOBaru INT

--1.Cari Nomor Akhir

select @NoAkhir = convert(int, Max(NIP))

from tblPeg

--2.Cari Nomor Baru

if @NoAkhir is null

  Set @Nobaru=1

else

  Set @NoBaru = @NoAkhir + 1

--3.Insert Data baru

Insert TblPeg (NIP, Nama) Values (Convert(char(4), @NoBaru), @Nama)

END

Untuk menambahkan data di tabel TblPeg, jalankan Store Procedure SP_TambahPeg sbb :

--menambahkan data dengan nama A

EXEC dbo.SP_TambahPeg 'A'

select * from tblpeg

--menambahkan data dengan nama B

EXEC dbo.SP_TambahPeg 'B'

select * from tblpeg

Latihan :

1.    Diketahui Tabel Pelanggan sbb : KodePelanggan NVarchar(10), NamaPelanggan NVarchar(50).  Penomoran KodePelanggan adalah C-00001, C-00002, C-00003, dst. Buat SP_TambahPelanggan dengan Parameter NamaPelanggan, sedangkan KodePelanggan dibuat secara otomatis di dalam SPnya.

Jawaban :

CREATE PROCEDURE SP_TambahPelanggan

  @NamaPelanggan varchar(50)

AS

BEGIN

DECLARE @NOAKHIR INT

DECLARE @NOBaru INT

DECLARE @KodePelanggan varchar(10)

--1.Cari Nomor Akhir

select @NoAkhir = convert(int, Max(right(KodePelanggan,5)))

from Pelanggan

--2.Cari Nomor Baru

if @NoAkhir is null

  Set @Nobaru=1

else

  Set @NoBaru = @NoAkhir + 1

Set @KodePelanggan = ‘C-‘ + RIGHT(‘00000’+Convert(varchar, @NoBaru), 5)

--3.Insert Data baru

Insert Pelanggan (KodePelanggan, NamaPelanggan) Values (@KodePelanggan, @NamaPelanggan)

END

Execute SP_TambahPelanggan sbb :

--menambahkan data dengan nama Teddy

EXEC dbo.SP_TambahPelanggan 'Teddy'

select * from Pelanggan order by KodePelanggan DESC

AUTONUMBER RESET PERIODIK

Autonumber dapat pula digunakan untuk pencatatan nomor dokumen transaksi seperti Nomor Dokumen Permintaan Barang, Nomor Pesanan Barang, Nomor Penerimaan Barang, Nomor Pengeluaran Barang, Nomor Pembelian, Nomor Kontra Bon, dsb.  Selalu menggunakan penomoran yang berurutan.  Penomoran ini memiliki pola yang khas. Contoh :

• Penomoran Dokumen Permintaan Barang : SPP-Tahun-NomorUrut
• Penomoran Dokumen Pesanan Barang : PO-Tahun-NomorUrut

Penomoran ini tergantung dari kebijakan perusahaan setempat.  Namun pada prinsipnya Nomor Urut dapat dibagi menjadi 2 kelompok, yaitu :

1)    Nomor Urut berlanjut terus (tanpa reset ke semula)

2)    Nomor Urut Reset Periodik, reset biasanya mengambil pola sbb :

·         Nomor Urut dengan reset Harian

·         Nomor Urut dengan reset Bulanan

·         Nomor Urut dengan reset Tahunan

Store Procedure Insert dengan Nomor Urut Reset Tahunan

Untuk Pembuatan Store Procedure Insert pada table TbPO sbb :

CREATE PROCEDURE SP_TambahTBPO

  @TglPO datetime, @KodeSupplier varchar(10)

AS

BEGIN

DECLARE @NOAKHIR INT

DECLARE @NOBaru INT

DECLARE @NomorPO varchar(13)

--1.Cari Nomor Akhir

select @NoAkhir = convert(int, Max(right(NomorPO,5)))

from tbPO

where substring(NomorPO,4,4) = convert(char(4), Datepart(yyyy, @TglPO)))

--2.Cari Nomor Baru

if @NoAkhir is null

  Set @Nobaru=1

else

  Set @NoBaru = @NoAkhir + 1

Set @NomorPO = ‘PO-‘ + convert(char(4), Datepart(yyyy, @TglPO )) + ‘-‘ +

RIGHT(‘00000’+Convert(varchar, @NoBaru), 5)

--3.Insert Data baru

Insert tbPO (NomorPO, TglPO, KodeSupplier) Values (@NomorPO, @TglPO, @KodeSupplier)

END

Store Procedure Insert dengan Nomor Urut Reset Bulanan

Untuk Pembuatan Store Procedure Insert pada table TbPOBulanan sbb :

CREATE PROCEDURE SP_TambahTBPOBulanan

  @TglPO datetime, @KodeSupplier varchar(10)

AS

BEGIN

DECLARE @NOAKHIR INT

DECLARE @NOBaru INT

DECLARE @NomorPO varchar(15)

--1.Cari Nomor Akhir

select @NoAkhir = convert(int, Max(right(NomorPO,5)))

from tbPOBulanan

where substring(NomorPO,4,4) = convert(char(4), Datepart(yyyy, @TglPO)) AND

      convert(int, substring(NomorPO,8,2)) = Datepart(mm, @TglPO)

--2.Cari Nomor Baru

if @NoAkhir is null

  Set @Nobaru=1

else

  Set @NoBaru = @NoAkhir + 1

Set @NomorPO = 'PO-' + convert(char(4), Datepart(yyyy, @TglPO )) +

right('0'+convert(varchar, datepart(mm,@TglPO)),2) + '-' +

RIGHT('00000'+Convert(varchar, @NoBaru), 5)

--3.Insert Data baru

Insert tbPOBulanan (NomorPO, TglPO, KodeSupplier) Values (@NomorPO, @TglPO, @KodeSupplier)

END

Berikut pola penomoran  reset per tahun, setiap gudang punya nomor urut sendiri (Gudang G1 dan G2)

TbPOKasus1

NomorPO	TGLPO	GUDANG	KODESUPPLIER
PO-2008-G1-00001	16/09/2008	G1	S-00001
PO-2008-G1-00002	16/09/2008	G1	S-00001
PO-2008-G2-00001	17/09/2008	G2	S-00002
PO-2008-G2-00002	17/10/2008	G2	S-00002
PO-2009-G1-00001	17/10/2009	G1	S-00002
PO-2009-G2-00001	17/10/2009	G2	S-00002

alter PROCEDURE SPGudang

  @TglPO datetime, @Gudang char(2), @KodeSupplier varchar(10)

AS

BEGIN

DECLARE @NOAKHIR INT

DECLARE @NOBaru INT

DECLARE @NoPO varchar(20)

--1.Cari Nomor Akhir

select @NoAkhir = convert(int, Max(right(NoPO,5)))

from dbo.Table_1

where substring(NoPO,4,4) = convert(char(4), Datepart(yyyy, @TglPO))

and substring (NoPO, 9, 2) = @Gudang

--2.Cari Nomor Baru

if @NoAkhir is null

  Set @Nobaru = 1

else

  Set @NoBaru = @NoAkhir + 1

Set @NoPO = 'PO-' + convert(char(4), Datepart(yyyy, @TglPO )) + '-'+ convert(varchar(2), @Gudang) + '-' + RIGHT('00000'+ Convert(varchar(5), @NoBaru), 5)

--3.Insert Data baru

Insert into dbo.Table_1 (NoPO, TglPO, Gudang, KodeSupplier) Values (@NoPO, @TglPO, @Gudang, @KodeSupplier)

END

exec dbo.SPGudang '9/16/2008', 'G1', 'S-00001'

select * from dbo.Table_1

exec dbo.SPGudang '9/17/2008', 'G2', 'S-00002'

select * from dbo.Table_1

exec dbo.SPGudang '10/17/2008', 'G2', 'S-00002'

select * from dbo.Table_1

exec dbo.SPGudang '10/17/2009', 'G1', 'S-00002'

select * from dbo.Table_1

exec dbo.SPGudang '10/17/2009', 'G2', 'S-00002'

select * from dbo.Table_1

Berikut pola penomoran  reset per Awalan Nama Barang, Untuk Awalan Barang A nomornya adalah A001 sd A999, Awalan Barang B adalah B001 sd/ B999, dst untuk awalan lainnya.

TbPOKasus1

Nama Barang	Kode Barang	Stok
Meja Biro	M001	5
Meja Makan	M002	3
Kursi Tamu	K001	2
Kursi Goyang	K002	4
Karpet	K003	3
Mantel	M003	4
Bath Tube Toto 234	B001	2

alter PROCEDURE SPstok

  @NamaBarang varchar(20), @Stok int

AS

BEGIN

DECLARE @NOAKHIR INT

DECLARE @NOBaru INT

DECLARE @KodeBarang varchar(20)

--1.Cari Nomor Akhir

select @NoAkhir = convert(int, Max(right(KodeBarang,3)))

from dbo.Table_2

where substring(KodeBarang, 1, 1) = convert(varchar, left(@NamaBarang, 1))

--2.Cari Nomor Baru

if @NoAkhir is null

  Set @Nobaru = 1

else

  Set @NoBaru = @NoAkhir + 1

Set @KodeBarang = left(@NamaBarang, 1) + left('00' + convert(varchar(4),@NoBaru), 3)

--3.Insert Data baru

INSERT INTO [SIPP].[dbo].[Table_2]

           ([NamaBarang]

           ,[KodeBarang]

           ,[Stok])

     VALUES

           (@NamaBarang

           ,@KodeBarang

           ,@Stok)

END

exec dbo.SPstok 'Meja Biro', 5

exec dbo.SPstok 'Meja Makan', 3

exec dbo.SPstok 'Kursi Tamu', 2

exec dbo.SPstok 'Kursi Goyang', 4

exec dbo.SPstok 'Karpet', 3

exec dbo.SPstok 'Mantel', 4

exec dbo.SPstok 'Bath tube Toto 234', 2

select * from dbo.Table_2

alter function NoPOTerakhir

  (@TglPO datetime)

returns varchar(13)

AS

BEGIN

DECLARE @NOAKHIR INT

--DECLARE @NOBaru INT

DECLARE @NoPO varchar(13)

--1.Cari Nomor Akhir

select @NoAkhir = convert(int, Max(right(NoPO,5)))

from dbo.Table_1

where substring(NoPO,4,4) = convert(char(4), Datepart(yyyy, @TglPO))

--2.Cari Nomor Baru

if @NoAkhir is null

  Set @NoAkhir=0

--else

 -- Set @NoBaru = @NoAkhir + 1

Set @NoPO = 'PO-' + convert(char(4), Datepart(yyyy, @TglPO )) + '-'+ RIGHT('00000'+ Convert(varchar(5), @NoAkhir), 5)

--3.Insert Data baru

--Insert dbo.Table_1 (NoPO, TglPO, KodeSupplier) Values (@NoPO, @TglPO, @KodeSupplier)

return @NoPO

END

select dbo.NoPOTerakhir('2008')

alter function NoPOBaru

  (@TglPO datetime)

returns varchar(13)

AS

BEGIN

DECLARE @NOAKHIR INT

DECLARE @NOBaru INT

DECLARE @NoPO varchar(13)

--1.Cari Nomor Akhir

select @NoAkhir = convert(int, Max(right(NoPO,5)))

from dbo.Table_1

where substring(NoPO,4,4) = convert(char(4), Datepart(yyyy, @TglPO))

--2.Cari Nomor Baru

if @NoAkhir is null

  Set @NoBaru = 1

else

          Set @NoBaru = @NoAkhir + 1

Set @NoPO = 'PO-' + convert(char(4), Datepart(yyyy, @TglPO )) + '-'+ RIGHT('00000'+ Convert(varchar(5), @NoBaru), 5)

--3.Insert Data baru

--Insert dbo.Table_1 (NoPO, TglPO, KodeSupplier) Values (@NoPO, @TglPO, @KodeSupplier)

return @NoPO

END

select dbo.NoPOBaru ('2009')

create  PROCEDURE SP_No2

  @ThnMasuk datetime,

  @Nama varchar(50)

AS

BEGIN

DECLARE @NOAKHIR INT

DECLARE @NOBaru INT

DECLARE @NoPegawai varchar(20)

--1.Cari Nomor Akhir

select @NoAkhir = CONVERT(int, left(NoPegawai,5))

from dbo.Table_3

--2.Cari Nomor Baru

if @NoAkhir is null

  Set @Nobaru=1

else

  Set @NoBaru = @NoAkhir + 1

Set @NoPegawai = Right('0000' + CONVERT(varchar(5), @NoBaru), 5)+ '-' + (CONVERT(varchar(4), DATEPART(yy, @ThnMasuk)))+ '-' + (convert(varchar(1), Left(@Nama, 1)))

--3.Insert Data baru

INSERT INTO [SIPP].[dbo].[Table_3]

           ([NoPegawai]

           ,[ThnMasuk]

           ,[Nama])

     VALUES

           (@NoPegawai

           ,@ThnMasuk

           ,@Nama)          

END

exec dbo.SP_No2 '01/01/2008', 'Amir'

select * from dbo.Table_3

CREATE PROCEDURE SP_No1

  @Nama varchar(50),

  @LP char(1),

  @TglDaftar datetime,

  @Jurusan varchar(2)

AS

BEGIN

DECLARE @NOAKHIR INT

DECLARE @NOBaru INT

DECLARE @NoDaftar varchar(20)

--1.Cari Nomor Akhir

select @NoAkhir = CONVERT(int, MAX(SUBSTRING(NoDaftar, 8, 4)))

from dbo.Table_2

--2.Cari Nomor Baru

if @NoAkhir is null

  Set @Nobaru=1

else

  Set @NoBaru = @NoAkhir + 1

Set @NoDaftar = (CONVERT(varchar(4), DATEPART(yyyy, @TglDaftar))) + '/' + @LP+ '-' + RIGHT('000' + CONVERT(varchar(4), @NoBaru), 4) + '/' + @Jurusan

--3.Insert Data baru

INSERT INTO [SIPP].[dbo].[Table_2]

           ([NoDaftar]

           ,[Nama]

           ,[LP]

           ,[TglDaftar]

           ,[Jurusan])

     VALUES

           (@NoDaftar

           ,@Nama

           ,@LP

           ,@TglDaftar

           ,@Jurusan)

          

END

exec dbo.SP_No1 'Meow', 'P', '01/01/2011', 'IT'

select * from dbo.Table_2

SELECT     CONVERT(varchar(4), DATEPART(yyyy, GETDATE())) + '/' + CONVERT(varchar(4), DATEPART(mm, GETDATE())) + '/' + CONVERT(varchar(1), LP)

                      + '/' + CONVERT(varchar(6), DATEPART(dd, TglLahir)) + CONVERT(varchar(6), DATEPART(mm, TglLahir)) + RIGHT(CONVERT(varchar(6), DATEPART(yy,

                      TglLahir)), 2) + '/' + CONVERT(varchar(6), RIGHT(No, 6)) AS Expr1

FROM         dbo.Table_1

create function NoFJTerakhir()

returns varchar(12)

as

begin

      declare @NoAkhir int

      declare @result varchar(12)

      select @NoAkhir = CONVERT(int, right(NoFJ,7))

      from dbo.FJ

      if @NoAkhir is null

            Set @NoAkhir=0

     

           

      set @result = 'FJ-' + right('000' + convert(varchar(5), @NoAkhir),8)

      return @result

end

select dbo.NoFJTerakhir()

alter function NoFJTerbaru()

returns varchar(12)

as

begin

      declare @NoAkhir int

      declare @NoBaru int

      declare @result varchar(12)

      select @NoAkhir = CONVERT(int, right(NoFJ,7))

      from dbo.FJ

      if @NoAkhir is null

            Set @NoAkhir = 1

      else

  Set @NoBaru = @NoAkhir + 1

           

      set @result = 'FJ-' + right('000' + convert(varchar(5), @NoBaru),8)

      return @result

end

select dbo.NoFJTerbaru()

create function NoKPTrakhir()

returns varchar(12)

as

begin

      declare @NoAkhir int

      declare @result varchar(12)

      select @NoAkhir = CONVERT(int, right(KodePelanggan,5))

      from Pelanggan

      if @NoAkhir is null

            Set @NoAkhir=0

     

           

      set @result = 'FJ-' + right('000' + convert(varchar(5), @NoAkhir),8)

      return @result

end

select dbo.NoKPTrakhir()

create function NoKPTerbaru()

returns varchar(12)

as

begin

      declare @NoAkhir int

      declare @NoBaru int

      declare @result varchar(12)

      select @NoAkhir = CONVERT(int, right(KodePelanggan,5))

      from Pelanggan

      if @NoAkhir is null

            Set @NoBaru = 1

      else

            Set @NoBaru = @NoAkhir + 1

           

      set @result = 'P-' + right('00000' + convert(varchar(5), @NoBaru),5)

      return @result

end

select dbo.NoKPTerbaru()