How to make store procedure for input date holiday although data was entry once

[deib97]

I have table like this :

CREATE TABLE IF NOT EXISTS `hari_libur` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tgl_libur` date NOT NULL,
  `keterangan` varchar(64) NOT NULL,
  `ulangi` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `hari_libur`
--

INSERT INTO `hari_libur` (`id`, `tgl_libur`, `keterangan`, `ulangi`) VALUES
(1, '2010-12-25', 'Hari Raya Natal', 1),
(2, '2010-08-17', 'Proklamasi Kemerdekaan RI', 1),
(3, '2010-01-01', 'Tahun Baru Masehi', 1),
(4, '2012-08-19', 'Hari raya Idul Fitri', 0),
(5, '2012-08-20', 'Hari Raya Idul Fitri', 0);

I want to auto insert for "ulangi" = 1 is repeat every year when i call a storeprocedure. How to make it?

So i dont entry data every start year.

thanks

[Barand]

running this query should do it

INSERT INTO hari_libur (tgl_libur, keterangan, ulangi)
    SELECT MAX(tgl_libur) + INTERVAL 1 YEAR
        , keterangan
        , 1
    FROM hari_libur
    WHERE ulangi = 1
    GROUP BY CONCAT(MONTH(tgl_libur),DAY(tgl_libur));

[deib97]

Hi Barand,

 

I try your code :

CREATE PROCEDURE auto_insert (IN ulang INT)
BEGIN
    INSERT INTO hari_libur (tgl_libur, keterangan, ulangi)
    SELECT MAX(tgl_libur) + INTERVAL 1 YEAR
        , keterangan
        , 1
    FROM hari_libur
    WHERE ulangi = 1
    GROUP BY CONCAT(MONTH(tgl_libur),DAY(tgl_libur));
END

CALL auto_insert(1)

 

Yes, success... But this generate insert all.

Maybe im wrong? cmiiw

[Barand]

It was working when it left the factory

 

After running it twice I had

mysql> SELECT * FROM test.hari_libur
    -> ORDER BY keterangan, tgl_libur;
+----+------------+---------------------------+--------+
| id | tgl_libur  | keterangan                | ulangi |
+----+------------+---------------------------+--------+
|  4 | 2012-08-19 | Hari raya Idul Fitri      |      0 |
|  5 | 2012-08-20 | Hari Raya Idul Fitri      |      0 |
|  1 | 2010-12-25 | Hari Raya Natal           |      1 |
|  6 | 2011-12-25 | Hari Raya Natal           |      1 |
| 10 | 2012-12-25 | Hari Raya Natal           |      1 |
|  2 | 2010-08-17 | Proklamasi Kemerdekaan RI |      1 |
|  7 | 2011-08-17 | Proklamasi Kemerdekaan RI |      1 |
| 11 | 2012-08-17 | Proklamasi Kemerdekaan RI |      1 |
|  3 | 2010-01-01 | Tahun Baru Masehi         |      1 |
|  8 | 2011-01-01 | Tahun Baru Masehi         |      1 |
|  9 | 2012-01-01 | Tahun Baru Masehi         |      1 |
+----+------------+---------------------------+--------+

[deib97]

Finally i change name of storeprocedure.

Thanks Barand help me..solved :happy-04:

[deib97]

Hi, im back Barand.

How to automatically call storeprocedure if the date now on server is 2014-01-01, 2015-01-01, ....?

Or maybe i must manual action for call it.