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

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

select @@servername as ServerName,* from sys.sysdatabases where name like '%teguhth%'
 

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

3. Insert table to source database
use teguhth;

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);

select @@servername as ServerName,db_name() as DBName,* from Transaksi;
 

4. grouping

-- Grouping per hari
SELECT @@servername as ServerName,db_name() as DBName,
    count(*) TotalJumlah,
    CONVERT(DATE, Tanggal) AS Hari,
    SUM(Jumlah) AS TotalJumlah
FROM
    Transaksi
GROUP BY
    CONVERT(DATE, Tanggal)
ORDER BY
    Hari;
 

5. prepare check & archive to DBArchive

select * from teguhth.dbo.Transaksi
where  CONVERT(DATE, Tanggal) = '2023-12-17'

insert into teguhth_archive.dbo.Transaksi select * from teguhth.dbo.Transaksi
where  CONVERT(DATE, Tanggal) = '2023-12-17'
 

6. check destination DBArchive

select * from Transaksi;
select @@servername as ServerName,db_name() as DBName,* from Transaksi;
SELECT @@servername as ServerName,db_name() as DBName,from teguhth.dbo.Transaksi
 

7. delete from database source base on day
SELECT @@servername as ServerName,db_name() as DBName,from teguhth.dbo.Transaksi ;
delete from teguhth.dbo.Transaksi where  CONVERT(DATE, Tanggal) = '2023-12-17';
select * from teguhth.dbo.Transaksi ;
SELECT @@servername as ServerName,db_name() as DBName,from teguhth.dbo.Transaksi ;