Read 285 times | Created 2013-05-15 06:50:32 | Updated 2017-09-15 09:15:38 | | |

 

DROP TABLE IF EXISTS surat;
CREATE TABLE IF NOT EXISTS surat (
  code set('SM','SK') NOT NULL,
  id int(4) unsigned zerofill NOT NULL AUTO_INCREMENT,
  tgl DATE NOT NULL,
  judul varchar(255) NOT NULL,
  PRIMARY KEY (tgl,code,id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
INSERT INTO surat(code,tgl,judul) VALUES
('SM','2017-09-14','Undangan Seminar'),
('SM','2017-09-14','Undangan Workshop'),
('SK','2017-09-14','Surat Tagihan'),
('SM','2017-09-14','Undangan Sarasehan'),
('SM','2017-09-15','Pemberitahuan'),
('SK','2017-09-15','Surat Balasan 1'),
('SM','2017-09-15','Pemberitahuan ke 2');
 
SELECT CONCAT(id,'/',code,'/',MONTH(tgl),'/',YEAR(tgl))AS nomor_surat,tgl, judul FROM surat;

+----------------+------------+--------------------+
| nomor_surat    | tgl        | judul              |
+----------------+------------+--------------------+
| 0001/SM/9/2017 | 2017-09-14 | Undangan Seminar   |
| 0002/SM/9/2017 | 2017-09-14 | Undangan Workshop  |
| 0001/SK/9/2017 | 2017-09-14 | Surat Tagihan      |
| 0003/SM/9/2017 | 2017-09-14 | Undangan Sarasehan |
| 0001/SM/9/2017 | 2017-09-15 | Pemberitahuan      |
| 0001/SK/9/2017 | 2017-09-15 | Surat Balasan 1    |
| 0002/SM/9/2017 | 2017-09-15 | Pemberitahuan ke 2 |
+----------------+------------+--------------------+