.::: Grouping Day, Time, Date, month, year for Archive, Cleansing in SQL Server :::.

 

1. create table 

CREATE TABLE datefilter (
    datex DATETIME NOT NULL
);

2. create date sample 3 years

;WITH cte AS (
    -- Mulai dari bulan ini
    SELECT 
        CAST(DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AS DATETIME) AS dt,
        1 AS lvl
    UNION ALL
    -- Mundur 36 bulan (3 tahun)
    SELECT 
        DATEADD(MONTH, -1, dt),
        lvl + 1
    FROM cte
    WHERE lvl < 36
)
INSERT INTO datefilter (datex)
SELECT DATEADD(MILLISECOND, 1, dt)              -- Tanggal 1
FROM cte
UNION ALL
SELECT DATEADD(MILLISECOND, 1, DATEADD(DAY, 14, dt))   -- Tanggal 15
FROM cte
OPTION (MAXRECURSION 0);

3. check sample data 
 
select count(*) from datefilter 
select * from datefilter order by datex desc
 

4. per bulan 
SELECT 
    CONVERT(char(7), datex, 120) AS [month],  -- Hasil: YYYY-MM
    COUNT(*) AS record_count
FROM 
    datefilter
GROUP BY 
    CONVERT(char(7), datex, 120)
ORDER BY 
    [month];

SELECT 
    FORMAT(CONVERT(datetime, datex, 120), 'yyyy-MM') AS [month],
    COUNT(*) AS record_count
FROM 
    datefilter
GROUP BY 
    FORMAT(CONVERT(datetime, datex, 120), 'yyyy-MM')
ORDER BY 
    [month];
 

5. perhari 

SELECT 
    CAST(datex AS date) AS [day],   -- Ambil tanggal (tanpa waktu)
    COUNT(*) AS record_count
FROM 
    datefilter
GROUP BY 
    CAST(datex AS date)
ORDER BY 
    [day];
    

6. pertahun

SELECT 
    YEAR(datex) AS [year],          -- Ambil tahun dari datex
    COUNT(*) AS record_count
FROM 
    datefilter
GROUP BY 
    YEAR(datex)
ORDER BY 
    [year];
    

7. pertahun pakai where 

select * from datefilter where YEAR(datex)=2025 order by datex desc
SELECT COUNT(*) FROM datefilter WHERE YEAR(datex) = 2025

SELECT * FROM datefilter
WHERE YEAR(datex) <= 2025  AND MONTH(datex) <= 6     -- di bawah Juni
ORDER BY datex DESC;

SELECT count(*) FROM datefilter
WHERE YEAR(datex) <= 2025  AND MONTH(datex) <= 6     -- di bawah Juni
 

8. pertahun pakai where

SELECT 
    YEAR(datex) AS [year],          -- Ambil tahun dari datex
    COUNT(*) AS record_count
FROM 
    datefilter
where YEAR(datex)=2025
GROUP BY 
    YEAR(datex)
ORDER BY 
    [year];
 

9. per bulan pakai where 

SELECT 
    CONVERT(char(7), datex, 120) AS [month],  -- Hasil: YYYY-MM
    COUNT(*) AS record_count
FROM 
    datefilter
where YEAR(datex)=2025
GROUP BY 
    CONVERT(char(7), datex, 120)
ORDER BY 
    [month];
 

10. perhari pakai where 
    
SELECT 
    CAST(datex AS date) AS [day],   -- Ambil tanggal (tanpa waktu)
    COUNT(*) AS record_count
FROM 
    datefilter
where YEAR(datex)=2025
GROUP BY 
    CAST(datex AS date)
ORDER BY 
    [day];