Mehedi's Earth

Recently, I was in need to generate a report to find out how many customer calls daily within 10 AM to 5 PM in first 3 days of June, 2021. This is little bit tricky which can be done using DATEPART function.

My Data

Expected Output

DATEPART Function

DATEPART function returns an integer represents the date part of the specified date.

Let's create the table and sample data.

IF OBJECT_ID('Test1', 'U') IS NOT NULL
    DROP TABLE Test1;
GO

-- Create the table
CREATE TABLE Test1(
    call_time datetime,
    name    varchar(10) default ('Mehedi')
)
GO

-- Populate with sample data
INSERT INTO Test1 (call_time, name)
VALUES ('2021-06-01 08:00','A')
,('2021-06-01 09:05','C')
,('2021-06-01 12:50','E')
,('2021-06-01 16:17','D')
,('2021-06-01 18:53','G')
,('2021-06-02 11:45','B')
,('2021-06-02 15:02','A')
,('2021-06-02 23:02','A')
,('2021-06-03 03:04','E')
,('2021-06-03 09:10','E')
,('2021-06-03 11:07','F')
,('2021-06-03 13:09','A')
,('2021-06-03 16:26','E')
,('2021-06-03 19:56','C')
,('2021-06-03 21:24','A')
GO

SELECT * FROM Test1
GO

SELECT    CONVERT(varchar(10), call_time, 111),count(*)
FROM         Test1
where DATEPART(year, call_time) = 2021 // Year. you can put your year
AND DATEPART(month, call_time) = 6 // your desired month
AND DATEPART(hour, call_time) >= 9 // My starting time
AND DATEPART(hour, call_time) <= 17 // My end time
group by CONVERT(varchar(10), call_time, 111)
order by CONVERT(varchar(10), call_time, 111)
GO

That is all for today. Happy TSQLing!