sql query to find the first 72 hours data based on the min date of each entry

Question

data

record no record datetime

1 01/01/2023 10:00

1 01/01/2023 11:10

1 01/01/2023 12:10

2 02/01/2023 12:10

2 02/02/2023 12:25

the query needs to return the records for the first 72 hours data for each record based on the min date for that record

Answer 1

Hi @NeophyteSQL

Try this query:

Declare @table table(record_no int, record_datetime datetime)

Insert into @table values
(1,'01/01/2023 10:00'),
(1,'01/01/2023 11:10'),
(1,'01/01/2023 12:10'),
(2,'02/01/2023 12:10'),
(2,'02/02/2023 12:25')

;WITH CTE AS
(
SELECT record_no
      ,MIN(record_datetime) AS record_datetime
	  ,DATEADD(HOUR,72,MIN(record_datetime)) AS record_datetime_72
FROM @table
GROUP BY record_no
)
SELECT T.* 
FROM @table T LEFT JOIN CTE C ON T.record_no=C.record_no
WHERE T.record_datetime <= C.record_datetime_72

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.