Refer the below sample code for your reference.
-- Temporary Table
CREATE TABLE #TempTable
(EmployeeId INT
,LeaveDate DATETIME
,LeaveReason VARCHAR(30) NULL)
GO
-- INSERT Query
INSERT INTO #TempTable
SELECT 1,'2017-12-31', ''
UNION ALL
SELECT 2,'2017-12-01', NULL
UNION ALL
SELECT 1,'2017-12-06', 'Sick leave'
UNION ALL
SELECT 4,'2017-12-08', NULL
UNION ALL
SELECT 18,'2017-12-12', NULL
UNION ALL
SELECT 11,'2017-12-13', 'Sick leave'
UNION ALL
SELECT 6,'2017-12-14', NULL
UNION ALL
SELECT 9,'2017-12-17', NULL
UNION ALL
SELECT 3,'2017-12-18', 'Sick leave'
UNION ALL
SELECT 12,'2017-12-25', NULL
UNION ALL
SELECT 16,'2017-12-27', 'Sick leave'
UNION ALL
SELECT 17,'2018-01-02', NULL
UNION ALL
SELECT 15,'2018-01-05', 'Sick leave'
UNION ALL
SELECT 11,'2018-01-11', NULL
UNION ALL
SELECT 13,'2018-01-12', 'Sick leave'
UNION ALL
SELECT 11,'2018-01-15', NULL
UNION ALL
SELECT 4,'2018-01-16', NULL
UNION ALL
SELECT 2,'2018-01-17', 'Sick leave'
GO
-- Test query to check the records in thirty days
DECLARE @SearchDate DATETIME
SET @SearchDate = '2017-12-16 00:00:00.000'
SELECT * FROM #TempTable
WHERE LeaveDate BETWEEN @SearchDate
AND DATEADD(DAY,30,@SearchDate)
ORDER BY LeaveDate
Output
EmployeeId |
LeaveDate |
LeaveReason |
9 |
2017-12-17 00:00:00.000 |
NULL |
3 |
2017-12-18 00:00:00.000 |
Sick leave |
12 |
2017-12-25 00:00:00.000 |
NULL |
16 |
2017-12-27 00:00:00.000 |
Sick leave |
1 |
2017-12-31 00:00:00.000 |
|
17 |
2018-01-02 00:00:00.000 |
NULL |
15 |
2018-01-05 00:00:00.000 |
Sick leave |
11 |
2018-01-11 00:00:00.000 |
NULL |
13 |
2018-01-12 00:00:00.000 |
Sick leave |
11 |
2018-01-15 00:00:00.000 |
NULL |