SQL query based on date range

Question

I have table

emp_id	date_time
e001	23-01-01 07:08
e001	23-01-01 16:07
e001	23-01-03 07:45
e001	23-01-03 16:01

I want to query to show result based on date range. for example I set the date range from 23-01-01 to 23-01-03 for e001 then the result is:

date	in	out
23-01-01	07:08	16:07
23-01-02	null	null
23-01-03	07:45	16:01

Answer 1

Taking your example data, and turning it into some DDL/DML (this is a really helpful first step you should do when asking these types of questions):

DECLARE @Table TABLE (emp_id VARCHAR(4), date_time DATETIME);
INSERT INTO @Table (emp_id, date_time) VALUES
('e001', '2023-01-01 07:08'),
('e001', '2023-01-01 16:07'),
('e001', '2023-01-03 07:45'),
('e001', '2023-01-03 16:01');

This becomes a simple exercise in data extrapolation to make sure we have rows for each interval. We can do this simply using a CTE. Then, joining from the full data set back to an aggregation of the table (assuming you just want the first date time as the IN and the last as the OUT where they don't match).

Finally, just apply the predicate to limit the date range:

;WITH Dates AS (
SELECT MIN(CAST(date_time AS DATE)) AS mnDate, MAX(CAST(date_time AS DATE)) AS mxDate FROM @Table
UNION ALL
SELECT DATEADD(DAY,1,mnDate), mxDate
  FROM Dates
 WHERE mnDate < mxDate
)

SELECT mnDate AS Date, InDateTime, OutDateTime
  FROM Dates d
    LEFT OUTER JOIN (
                     SELECT CAST(date_time AS DATE) AS date, MIN(date_time) AS InDateTime, NULLIF(MAX(date_time),MIN(date_time)) AS OutDateTime 
                       FROM @Table 
                      GROUP BY CAST(date_time AS DATE)
                    ) a
      ON d.mnDate = a.date
 WHERE d.mnDate BETWEEN '2023-01-01' AND '2023-01-03';

Date	InDateTime	OutDateTime
2023-01-01	2023-01-01 07:08:00.000	2023-01-01 16:07:00.000
2023-01-02
2023-01-03	2023-01-03 07:45:00.000	2023-01-03 16:01:00.000