Count rows within date range with condition

I’m not sure that this issue can be solved in SQL server, but I decided to ask you anyway.

Let’s create a table and add some random variables:

CREATE TABLE test_c (
    timestamp datetime,
    product varchar(max),
    client_id varchar(max),
    seconds int,
    )
--Insert values
INSERT INTO test_c VALUES
    ('2022-08-05 08:17:55', 'B', '5', 12)
    , ('2022-08-05 07:14:59', 'A', '11', 1)
    ,   ('2022-08-05 07:16:44', 'A', '11', 1)
    ,   ('2022-08-05 07:17:50', 'A', '11', 10)
    ,   ('2022-08-05 07:22:50', 'A', '11', 1)
    ,   ('2022-08-05 07:31:59', 'A', '11', 1)
    ,   ('2022-08-05 08:16:44', 'B', '5', 1)
    ,   ('2022-08-05 08:17:50', 'B', '5', 7)
    ,   ('2022-08-05 08:22:50', 'B', '5', 3)
    ,   ('2022-08-05 08:31:59', 'B', '5', 1)
    ,   ('2022-08-05 08:44:59', 'B', '5', 1)
    ,   ('2022-08-05 08:45:59', 'B', '5', 1)
    ,   ('2022-08-05 08:46:59', 'C', '3', 3)
    ,   ('2022-08-05 08:16:44', 'C', '3', 3)
    ,   ('2022-08-05 08:17:50', 'C', '3', 3)
    ,   ('2022-08-05 08:22:50', 'C', '3', 3)
    ,   ('2022-08-05 08:31:59', 'C', '3', 3)
    ,   ('2022-08-05 08:44:59', 'C', '3', 3)
    ,   ('2022-08-05 08:45:59', 'C', '3', 3)
    ,   ('2022-08-05 08:46:59', 'C', '3', 3)

Now, let’s add to the table two columns:

• “before_5” – five minutes before the given timestamp
• “after_5” – five minutes the given timestamp

SELECT *
        , DATEADD(minute, -5, timestamp) AS 'before_5'
        , DATEADD(minute, 5, timestamp) AS 'after_5'
FROM test_c

Now our table looks in the following way:

timestamp	product	client_id	seconds	before_5	after_5
2022-08-05 07:14:59.000	A	11	1	2022-08-05 07:09:59	2022-08-05 07:19:59
2022-08-05 07:14:59.000	A	11	1	2022-08-05 07:09:59	2022-08-05 07:19:59

The task is the following: If the the value in the column “seconds”>=8, then I need to count the number of the same products within the range (5 minutes before and 5 minutes after) of this row.

I want to receive the following results:

timestamp	product	client_id	seconds	num_of_products
2022-08-05 07:17:50.000	A	11	10	5
2022-08-05 08:17:55.000	B	5	12	4

Also, I’m attaching screenshot:

Answer

A simple correlated subquery should give you your expected results.

select [Timestamp], Product, Client_Id, Seconds, 
  case when seconds < 8 then 0 else (
    select Count(*) from t t2 
      where t2.product = t.product 
        and t.timestamp >= DateAdd(minute, -5, t2.timestamp) 
        and t.timestamp < DateAdd(minute, 5, t2.timestamp)
      ) end Num_Of_Products
from t
order by [Timestamp];

It’s not clear if you’re expecting to filter the results to only rows with seconds >=8 however if that’s the case the query just needs a where criteria and removal of the case expression:

select [Timestamp], Product, Client_Id, Seconds, (
  select Count(*) from t t2 
  where t2.product = t.product 
    and t.timestamp >= DateAdd(minute, -5, t2.timestamp) 
    and t.timestamp < DateAdd(minute, 5, t2.timestamp)
  ) Num_Of_Products
from t
where t.seconds >= 8
order by [Timestamp];

Note that datetime was deprecated years ago by datetime2 and varchar(max) should be avoided if your product names are not potentially > 8kb in length.

See DB<>Fiddle