Hello all,
SQL rookie here. I'm looking to count the number of occurrences an ID has 3 days before the date and 3 days after the date. I'm a little stuck.
Here is an example of the table and desired output I'm looking for....
Thanks for you help !
Please provide consumable test data in ISO format:
CREATE TABLE #t
(
ID int NOT NULL
,[Date] datetime NOT NULL
);
INSERT INTO #t
VALUES (1, '20191114 00:00:00')
,(2, '20191114 00:15:00')
,(5, '20191114 00:30:00')
,(5, '20191114 00:45:00')
,(7, '20191115 01:00:00')
,(7, '20191115 01:15:00')
,(1, '20191115 01:30:00')
,(2, '20191115 01:45:00')
,(5, '20191121 02:00:00')
,(6, '20191122 02:15:00')
,(7, '20191122 02:30:00')
,(7, '20191122 02:45:00')
,(2, '20191122 03:00:00')
,(7, '20191122 03:15:00');One approach:
SELECT T.ID, T.[Date], X1.[< 3 days], X2.[> 3 days]
FROM #t T
CROSS APPLY
(
SELECT COUNT(*)
FROM #t T1
WHERE T1.ID = T.ID
AND T1.[Date] >= DATEADD(day, -3, T.[Date])
AND T1.[Date] < T.[Date]
) X1 ([< 3 days])
CROSS APPLY
(
SELECT COUNT(*)
FROM #t T1
WHERE T1.ID = T.ID
AND T1.[Date] > T.[Date]
AND T1.[Date] <= DATEADD(day, 3, T.[Date])
) X2 ([> 3 days]);The code above uses 72 hours, but I think you may mean 3 calendar days?! Did you mean 72 hours or 3 calendar days?
I meant 72 hrs, but I know how to edit that portion.
Ifor's approach does use 72 hours, so you should be fine.
I greatly appreciate the response.
Follow up question # 1 regarding the "insert value" section (likely an error on how I submitted the question).. The format I presented was just an example as the actual table is enormous. The example "Table A" was intended to show the existing table output. If 'ID' and 'Date' are existing columns with data, how do I avoid manually inserting values?
Follow up question #2, I will eventually need to join another table. Let's say Table B where 'ID' is the common field. How will that change the query?
Thanks
#t is test data.
You just replace #t with your table name.
