.::: Grouping SQl Server by Date/day. dan Archive Database (table) base on Date/Day In MariaDB / MySQL :::.

1. create database source & destination;
create database teguhth;
create database teguhth_archive;
 

2. create database source & destination;
CREATE TABLE Transaksi (
    ID INT PRIMARY KEY,
    Tanggal DATETIME,
    Jumlah INT
);
 

3. Insert table to source database

INSERT INTO Transaksi (ID, Tanggal, Jumlah)
VALUES
    (1, '2023-12-17 08:30:00', 100),
    (2, '2023-12-17 12:45:00', 100),
    (3, '2023-12-17 13:45:00', 100),
    (4, '2023-12-17 15:45:00', 100),
    (5, '2023-12-17 17:45:00', 100),
    
    (6, '2023-12-18 09:15:00', 100),
    (7, '2023-12-18 14:30:00', 100),
    (8, '2023-12-18 15:30:00', 100),
    (9, '2023-12-18 17:30:00', 100),
    (10, '2023-12-18 20:30:00', 100),
    
    (11, '2023-12-19 10:00:00', 100),
    (12, '2023-12-19 14:00:00', 100),
    (13, '2023-12-19 15:00:00', 100),
    (14, '2023-12-19 17:00:00', 100),
    (15, '2023-12-19 19:00:00', 100);
    
4. grouping

-- Grouping per hari

SELECT DATE_FORMAT(Tanggal, '%Y-%m-%d') AS Hari, SUM(Jumlah) AS TotalJumlah
FROM  Transaksi
GROUP BY DATE_FORMAT(Tanggal, '%Y-%m-%d')
ORDER BY Hari;
 

   
5. prepare check & archive to DBArchive

select * from teguhth.Transaksi
where  DATE_FORMAT(Tanggal, '%Y-%m-%d') = '2023-12-17';

insert into teguhth_archive.Transaksi select * from teguhth.Transaksi
where  DATE_FORMAT(Tanggal, '%Y-%m-%d') = '2023-12-17';

6. check destination DBArchive

select * from teguhth_archive.Transaksi;
select * from teguhth_archive.Transaksi where  DATE_FORMAT(Tanggal, '%Y-%m-%d') = '2023-12-17';
 

7. delete from database source base on day

delete from teguhth.Transaksi where DATE_FORMAT(Tanggal, '%Y-%m-%d') = '2023-12-17';
select * from teguhth.Transaksi ;

select * from teguhth.Transaksi
where  DATE_FORMAT(Tanggal, '%Y-%m-%d') = '2023-12-17';