CODEWITHSUNDEEP
sqlsql-server
Rish
Rish

Reputation: 457

How To get the Last 15 minutes values(sessions) for each row in the table in sql select with SUM

Log Analyzer table Table for reference

I want to get a table of

1> requestTime 2> NumberOfSessionsInLast15minutes

How to write the query to get a table where the requestTime column will be same and the NumberOfSessions column will be sum of sessions in last 15 minutes

EXAMPLE:

The number of sessions for this one 2012-06-06 00:12:00.0000 should be sum of sessions betwen 2012-06-06 00:12:00.0000 and (2012-06-06 00:12:00.0000) - 15 minutes .

Upvotes: 0

Views: 6244

Answers (3)

Andomar
Andomar

Reputation: 238236

You could use a subquery to retrieve the number of sessions:

select  s1.requesttime
,       (
        select  sum(NumberOfSessions)
        from    sessions s2
        where   dateadd(minute, -15, s1.requesttime) < s2.requesttime
                and s2.requesttime <= s1.requesttime
        ) as TotalNumberOfSessions
from    sessions s1

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280459

Here's a way to do it generically, providing a breakdown of 15-minute periods for a given day:

DECLARE @requests TABLE(requestTime DATETIME, NumberOfSessions INT);

INSERT @requests SELECT '20120605 23:59', 2
UNION ALL SELECT '20120606 00:00', 500
UNION ALL SELECT '20120606 00:07', 400
UNION ALL SELECT '20120606 00:17', 300
UNION ALL SELECT '20120606 23:57', 500
UNION ALL SELECT '20120607 00:00', 100;

DECLARE @day SMALLDATETIME; -- this would be a stored procedure parameter
SET @day = '20120606';

;WITH n AS 
(
  SELECT TOP 96 n = DATEADD(MINUTE, 15*
  (ROW_NUMBER() OVER (ORDER BY [object_id])-1), @day)
  FROM sys.all_columns ORDER BY [object_id]
)
SELECT requestTime = n.n, SumNumberOfSessions = COALESCE(SUM(NumberOfSessions), 0)
FROM n LEFT OUTER JOIN @requests AS r
ON r.requestTime >= n
AND r.requestTime < DATEADD(MINUTE, 15, n)
GROUP BY n.n
ORDER BY requestTime;

Results:

requestTime                SumNumberOfSessions
-----------------------    -------------------
2012-06-06 00:00:00.000    900
2012-06-06 00:15:00.000    300
2012-06-06 00:30:00.000    0
...
2012-06-06 23:30:00.000    0
2012-06-06 23:45:00.000    500

Upvotes: 0

MatBailie
MatBailie

Reputation: 86775

DECLARE
  @reportTime   DATETIME
SELECT
  @reportTime = '2012-06-06 00:12'

SELECT
  @reportTime,
  SUM(NumberOfSessions)
FROM
  yourTable
WHERE
      requestTime >  DATEADD(minute, -15, @reportTime)
  AND requestTime <= @reportTime

Mix and match >=, >, < and <= depending on your needs and the exact behaviour of your data.

(Normally a 15 minute window would be >= 00:00 AND < 00:15, but your definition is slightly different from that.)


If you want it for all records in the source table...

SELECT
  base.requestTime,
  SUM(history.NumberOfSessions)
FROM
  yourTable    AS base
INNER JOIN
  yourTable    AS history
    ON  history.requestTime >  DATEADD(minute, -15, base.requestTime)
    AND history.requestTime <= base.requestTime
GROUP BY
  base.requestTime

Upvotes: 1

Related Questions