SQL Server - Function Autonumber Kembali Ke Satu Saat Ganti Tanggal

Assalamu'alaikum wr.wb.
Pada kesempatan kali ini, saya akan memberikan tutorial tentang cara membuat function autonumber kembali ke angka satu saat tanggal berganti. Jadi ketika kita membuat autonumber untuk sebuah id dalam suatu tabel, lalu hari, bulan, ataupun tahun berganti maka autonumber tersebut akan kembali ke satu. Misal Ketika kita membuat autonumber 2018.01.0001, 2018.02.0002, Lalu ketika tahun berganti akan menjadi 2019.02.0001. Begitulah kira - kira gambaran besar dari artikel yang satu ini.

Sebenarnya tutorial ini tidaklah berbeda dengan tutorial sebelumnya yaitu tentang Function membuat autonumber kombinasi tanggal dan angka urut SQL Server. Dimana susunan autonumber dan tabel yang digunakan sama. Jadi saya menggunakan tabel yang digunakan pada artikel tersebut.



Baca Juga

• PYCHARM Professional Edition. IDLE Bahasa Pemrograman Python
• Stored Procedure, Trigger, Dan Function MySQL
• Lebih Lanjut Tentang SELECT Di MySQL

1. Membuat Database Dan Tabel Dengan ID Tipe Data Char(12)

Disini kita perlu membuat database dan tabel dengan tipe data char(12) untuk id tabel. Sebelum membuatnya, kita perlu membuat satu tabel dengan id int identity saja. Karena id dari tabel kedua ini digunakan untuk primary key tabel pertama.

Create database Contoh

Kita menggunakan program diatas untuk membuat databas. Dengan begitu, maka settingan database ditentukan secara default. Apabila anda ingin mengubah settingan database, silahkan kunjungi artikel saya tentang cara membuat database dengan SQL Server.

use Contoh

Script diatas digunakan untuk memilih database yang akan kita pilih. Disini saya menggunakan database contoh.

create table jurusan(
id_jurusan int identity primary key not null,
nama_jurusan varchar(50) not null
)

Script diatas adalah script untuk membuat tabel jurusan. Pada tabel ini, kita cukup menjadikan id sebagai integer saja. Hal ini bertujuan untuk kemudahan. Lalu setelah id kita beri kolom nama jurusan.

insert into jurusan(nama_jurusan) 
values('Sistem Informatika'),('Tekhnik Informatika'),('Manajemen Informatika')

Pada script diatas, kita menginputkan data pada tabel jurusan. Disini saya menambahkan tiga buah data. Lihatlah yang saya input adalah nama_jurusan saja. Hal ini disebabkan id pada tabel jurusan sudah di setting identiti atau autoincrement. Dengan begitu id akan terus bertambah secara otomatis.

Lalu kita execute dan lihat hasilnya.

Gambar diatas adalah hasil dari pembuatan dan penginputan data pada tabel jurusan. Kita bisa melihat data - data yang sebelumnya kita coba inputkan telah masuk pada table jurusan. Artinya proses pembuatan database dan insert telah berhasil.

create table mahasiswa(
id_mahasiswa char(12) primary key not null,
nama_mahasiswa varchar(50) not null,
jkel char(1) not null,
alamat varchar(100) not null,
tgl_lahir date not null,
id_jurusan int foreign key references jurusan(id_jurusan) not null
)

Setelah kita selesai dengan tabel jurusan, selanjutnya adalah membuat tabel mahasiswa dimana tabel tersebut berisi id_mahasiswa dengan tipe data char(12). Pada tabel inilah kita menggunakan autonumber dengna format tanggal, kode, dan urutan. Untuk kolom lain, anda bisa menambahnya ataupun mengurangi sesuai dengan selera anda. Karena disini hanya id yang diutamakan.

2. Membuat Function Untuk Autonumber Dengan Kombinasi Tahun, Kode Jurusan, Dan Nomer Urut (2018.01.0001)

Sekarang kita mulai untuk membuat function yang mana function itu berfungsi untuk menyusun autonumber sedemikian rupa hingga memperoleh hasil yang diinginkan. Disini saya membuat function denga format autonumber tahun.kode jurusan.nomer urut (2018.02.0001)

create function auto_tgl_jurusan_year(@jurusan int)

Program diatas digunakan untuk membuat function. Disini kita buat auto_tgl_jurusan sebagai nama function dengan @jurusan sebagai parameter. Artinya tiap kali kita memanggil function tersebut, kita juga harus melibatkan parameter didalamnya.

returns char(12)
as begin

Nilai yang dikembaikan adalah char(12). Id yang kita buat seluruhnya memiliki jumlah 12 karakter. Jadi disini saya memberikan 12 sebagai nilai kembali. Sebenarnya anda bisa mengurangi ataupun menambahnya. Tapi jika ditambah misal 13, maka satu karakter terakhir tidak akan disimpan di data atau truncated. Jika kurang, maka hasilnya tidak sesuai dengan yang kita inginkan. Karena itu kita buat pas saja.

declare @urut int, @hasil char(12)

Pendeklarasian yang kita buat adalah @urut dengan tipe data integer dan @hasil dengan tipe data char(12). @hasil memiliki tipe data yang sama dengan nilai kembali karena pada akhirnya nanti @hasil inilah yang akan dikembalikan.

SELECT @urut = CONVERT(INT, isnull(max(right(id_mahasiswa,4)),0)) from mahasiswa 

Pada program diatas, kita memasukkan nilai pada variabel @urut. @urut sendiri adalah variabel dengan tipe data integer. Sementara yang pada tabel mahasiswa, id_mahasiswa bertipe data char. Karena itu kita Convert id dari char ke int.

Pada tabel yang baru kita buat, belum ada data sama sekali. Karena itu kita gunakan code isnull. Code tersebut berfungsi untuk menghasilkan angka nol(0) jika data yang dimaksud tidak ada. Dengan begitu maka function bisa dibuat ketika data masih nol dan menghasilkan nilai null. Dengan isnull, maka akan mengganti nilai null dengan 0.

Pada dasarnya, program diatas adalah program untuk menampilkan semua data mahasiswa. Bagaimana jika sudah ada data yang terisi? Padahal yang kita butuhkan adalah data terakhir dari mahasiswa. Karena itu kita gunakan code max. Code tersebut berfungsi untuk mengambil nilai terakhir dari data mahasiswa.

Lalu kita ingat bahwa format dari autonumber tersebut adalah tanggal, kode jurusan, dan angka urut dimana tahun ada di paling depan dilanjutkan dengan kode jurusan dan angka urut (2018.01.0001). Kita lihat terdapat tanda baca titik(.) sebagai pemisah antara tanggal, kode, dan angka urut. Padahal tanda baca tidak bisa di convert ke integer. Karena itulah, kita gunakan code right yang mana code tersebut berfungsi untuk mengambil digit dari kanan. Code dasar dari right adalah right(kolom, jumlah_digit). Pada contoh program diatas, kita mengambil jumlah digit 4. artinya kita mengambil 4 digit dari sebelah kanan (2018.01.0001) menjadi 0001. Namun karena integer tidak mendukung 0 didepan, maka nilai yang didapat adalah 1 atau nilai terakhir setelah nol).

Karena data pada tabel mahasiswa belum ada. maka nilai yang didapat dan disimpan ke variabel @urut adalah 0.

SELECT @urut = CONVERT(INT, isnull(max(right(id_mahasiswa,4)),0)) from mahasiswa where left(id_mahasiswa,4)=YEAR(getdate())

Program diatas adalah untuk menginput variabel @hasil. Dimana hasil digunakan untuk menyimpan format autonumber yang sudah jadi. Autonumber yang kita gunakan adalah 2018.01.0001. Karena itu kita tambahkan tahun di awal dan diteruskan dengan titik dan kode jurusan.

Pada awal dari script tersebut, kita menemukan deklarasi tahun yang dikonversi ke char(4). Hal ini disebabkan karena date dan char itu berbeda. Karena itu kita mengambil tahun sekarang dan kemudian kita convert ke char(4).

@jurusan adalah parameter dari fungsi yang kita buat. Parameter tersebut ibarat sudah ada isinya dan bernilai integer. Karena itu kita perlu untuk mengubah @jurusan dari integer ke char. Lalu kita tambahkan juga 0 didepannya.

Setelah kita menyimpan @urut sebelumnya, kita tadi sudah mendapatkan nilai tersebut. Namun yang kita butuhkan adalah nilai yang berbeda dengan @urut karena @urut menyimpan nilai yang sudah disimpan di tabel mahasiswa. Maka dari itu kita tambahkan @urut+1. Hal ini untuk menambah jumlah @urut sendiri. Misal @urut bernilai 0. Maka ditambahkan 1 dan menjadi 1. Yang kemudian kita Convert ke varchar(4). Varchar sendiri bersifat fleksibel.

Format dari autonumber yang kita buat adalah 2018.01.0001. Jika kita hanya menggunakan getdate()+@jurusan+@urut+1, maka hasil yang diperoleh adalah 2018.01.1. Karena itu diantaranya kita tambahkan '000'. Hal tersebut dikarenakan @urut bertipe data integer yang mana tidak mendukung angka nol didepan.

Sekarang masalahnya adalah bagaimana jika @urut bernilai lebih dari satu digit? 99 misal. Jika kita hanya menggunakan format getdate()+@jurusan+'000'+@urut+1, maka yang dihasilkan adalah 2018.01.000100. Padahal id pada tabel mahasiswa hanya menyimpan 12 digit saja. Artinya yang tersimpan adalah 2018.01.0001. Itu menyebabkan error dimana autonumber tersebut sebelumnya sudah ada. Karena itu kita membutuhkan code right. dimana code tersebut digunakan untuk mengambil 4 digit terakhir dari data yang dihasilkan. getdate()+@jurusan+right('000'+@urut+1) Begitulah kasaran dari contoh program.

Disini kita tambahkan script where yang mana script tersebut memberikan batas dengan value - value tertentu agar data yang ditampilkan hanyalah data yang memiliki value tersebut. Script left sendiri untuk mengambil nilai dari sebelah kiri yang mana left(id, 4) artinya mengambil 4 karakter dari sebelah kiri. Kita bisa melihat 4 karakter awal merupakan tahun saat ini. Disinilah kunci dari trik ini. Jika tahun berganti, maka tidak akan ada data yang sesuai dengan value tersebut hingga akhirnya @urut menghasilkan nol(0).

SET @hasil = Convert(char(4),YEAR(getdate()))+'.0'+CONVERT(char(1),@jurusan)+'.'+right('000'+CAST( @urut+1 as VARCHAR(4)), 4)

Setelah semuanya selesai. Format id sudah dibuat dan sudah dimasukkan ke variabel @hasil. Maka kita hanya tinggal mengembalikan nilai tersebut menggunakan perintah return. Lalu kita gunakan end untuk mengakhiri function. Dengan begitu, function sudah berhasil dibuat.

Hasil keseluruhan function

--tgl.jurusan.0001 (2017.01.0001)
create function auto_tgl_jurusan(@jurusan int)
--Nilai yang dikembalikan
returns char(12)
as begin
--Deklarasi variabel yang dibutuhkan
declare @urut int, @hasil char(12)

--Mengambil 4 digit terakhir dari kolom id pada table mahasiswa
SELECT @urut = CONVERT(INT, isnull(max(right(id_mahasiswa,4)),0)) from mahasiswa where left(id_mahasiswa,4)=YEAR(getdate())

--Membuat autonumber dengan format A0001 dan dimasukkan ke variabel @hasil
SET @hasil = Convert(char(4),YEAR(getdate()))+'.0'+CONVERT(char(1),@jurusan)+'.'+right('000'+CAST( @urut+1 as VARCHAR(4)), 4)
--Mengembalikkan nilai @hasil
RETURN @hasil
END

Dari berbagai penggalan program diatas yang sudah dijelaskan satu persatu, maka program diatas ini adalah hasil dari keseluruhan program function autonumber yang sudah dijadikan satu. Penggalan program sebelumnya adalah bagian dari function diatas yang mana saya pisahkan dan dijelaskan satu persatu.

3. Menginput Data Dengan Format Autonumber Tahun, Kode Jurusan, Angka Urutan Lalu Menampilkan Data Tersebut. 

Setelah kita mengikuti langka satu dan langkah dua diatas, artinya kita sudah membuat tabel dan function sesuai kebutuhan. Saat ini kita hanya perlu menambahkan data pada tabel mahasiswa dengan id berformat autonumber dari function tersebut.

insert into mahasiswa(id_mahasiswa, nama_mahasiswa, alamat, tgl_lahir, jkel, id_jurusan) values
(dbo.auto_tgl_jurusan_year(1), 'Fadhlillah Fadhlan', 'Banyubiru', '1994-03-03','L',1),
(dbo.auto_tgl_jurusan_year(2), 'Choirul Umam', 'Gunung Kidul', '1994-10-25','L',2),
(dbo.auto_tgl_jurusan_year(3), 'Reza Pratama', 'Balikpapan', '1995-08-14','L',3)

Disini kita inputkan 3 data pada tabel mahasiswa. Pada masing - masing data yang diinput, kita menginput id dengan cara memanggil function dengan cara yang sama, sementara untuk kolom yang lain bisa diisi dengan bebas. Disini saya menginput dengan data diatas. Silahkan kunjungi artikel saya tentag CRUD Pada SQL Server.

Setelah kita menginput data diatas, sekarang yang perlu dilakukan adalah mengubah tahun di pc. Jika saat ini adalah tahun 2018, maka cobalah untuk mengganti tahun selain tahun 2018. Terserah berapapun.

insert into mahasiswa(id_mahasiswa, nama_mahasiswa, alamat, tgl_lahir, jkel, id_jurusan) values
(dbo.auto_tgl_jurusan(2), 'Ikhwan Candra Nugraha', 'Cilacap', '1994-11-14','L',2)

Lalu kita inputkan lagi seperti program diatas. Ingat, data diatas diinput setelah tanggal sudah diubah.

select id_mahasiswa, nama_mahasiswa, jkel, alamat, tgl_lahir, m.id_jurusan, nama_jurusan from mahasiswa m 
join jurusan on m.id_jurusan = jurusan.id_jurusan

Program diatas untuk menampilkan data dari tabel mahasiswa dan jurusan. Disini saya menggunakan SELECT JOIN untuk menampilkan data dari kedua tabel tersebut. Silahkan kunjungi artikel saya tentang SELECT, SELECT JOIN, DAN PIVOT SQL SERVER.

Lalu Execute dan lihat hasilnya.

Dari gambar diatas, kita bisa melihat bahwa id menggunakan format tanggal, kode jurusan dan angka urut secara berurutan dan terus bertambah seiring dengan data yang ditambahkan. Jika tampilan sudah seperti diatas. Maka autonumber dengan format tanggal, kode jurusan dan angka urut sudah berhasil dibuat. Selain itu, kita lihat pada data terakhir. Format sama akan tetapi nomor urut kembali ke angka satu. Hal itu disebabkan tahun telah berganti.

Penutup

Cara diatas memang sangat efisien dan membuat tampilan lebih menarik. Namun perlu diketahui bahwa tipe data char adalah tipe data yang berat. Dan tipe data tersebut memakan ram lebih banyak daripada integer yang notabene sangat ringan. Karena itu saya sarankan jika project yang dibuat adalah project yang benar - benar digunakan pada sebuah perusahaan untuk tidak menggunakan cara ini. Cara ini hanya memberat - beratkan ram dan mengakibatkan penyimpanan data tidak maksimal. Namun bila project yang dibuat hanya sekedar tugas akhir atau final project, anda boleh - boleh saja menggunakan cara ini asalkan tidak benar - benar digunakan di perusahaan atau toko saja.

Demikianlah tutorial yang saya berikan. Apabila ada kekurangan saya mohon maaf . Terimakasih telah berkunjung di blog saya yang sederhana ini. Silahkan tinggalkan jejak di kolom komentar. Assalamu'alaikum wr.wb.