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:

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:

Also, I'm attaching screenshot:

CodePudding user response：

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

CodePudding user response：

1. With either Common Table Expression (CTE), table variable or temp table to build a table data with seconds is greater than or equal 8.

2. test_c table cross apply with CTE. GROUP BY timestamp, product_id and client_id columns. Perform the COUNT for num_of_products.

WITH CTE AS
(
  SELECT [timestamp]
        , [product]
        , [client_id]
        , [seconds]
        , DATEADD(minute, -5, timestamp) AS 'before_5'
        , DATEADD(minute, 5, timestamp) AS 'after_5'
  FROM test_c
  WHERE seconds >= 8
) 
SELECT b.[timestamp]
  , a.[product]
  , a.[client_id]
  , MAX(b.[seconds]) AS [seconds]
  , COUNT(1) AS [num_of_products]
FROM test_c a
CROSS APPLY (
  SELECT *
  FROM CTE b
  WHERE a.[product] = b.[product]
    and a.[client_id] = b.[client_id]
    and a.[timestamp] >= [before_5] and a.[timestamp] <= [after_5]
) b
GROUP BY b.[timestamp], a.[product], a.[client_id]

Sample DB Fiddle