15 min increment query

Question

I have a server that currently is processing jobs. Example

JobName               avgStartTime      avgEndTime
job1                  00:31:05.5500000  00:37:35.6170000
job2                  00:35:05.5500000  00:45:35.6170000
job3                  00:54:24.0600000  01:04:22.5700000
job4                  05:07:12.9100000  06:04:59.7800000
job5                  20:48:04.6870000  21:08:20.1430000
job6                  19:40:24.6870000  19:57:48.4570000

and I would like to create a query that would give a column with 15 min increments, and another one with the jobs processed in those 15 mins. For example.

Interval   NumberOfJobs   JobName
00:00:00   0
00:15:00   0
00:30:00   2              Job1
                          Job2
00:45:00                  Job3
01:00:00                  Job3
01:15:00

Here is my query to calculate the 15 min increments.

CREATE TABLE #15MinuteIncrements(Increment DATETIME
                         , PRIMARY KEY(Increment));

DECLARE @dIncr DATETIME =
                     (SELECT CONVERT( DATETIME, CONVERT(DATE, GETDATE())));

DECLARE @dEnd DATETIME =
                    (SELECT CONVERT( DATETIME, CONVERT(DATE,
                                                     (SELECT DATEADD(day, 1, GETDATE())))));

WHILE(@dIncr < @dEnd)
    BEGIN
       INSERT INTO                    #15MinuteIncrements(Increment)
       VALUES
            (@dIncr);
       SELECT @dIncr = DATEADD(MINUTE, 15, @dIncr);
    END;
GO

I have just been doing inserts into a temp table in 15 mins increments, and then I joined it to my table. But still, I don't get the numbers I want. — Will, Apr 15 '16 at 15:55

score 0 · Accepted Answer · answered Apr 15 '16 at 16:53

I think this will work, using your #15MinuteIncrements table. It uses multiple tables, but you can parse that into subqueries.

CREATE TABLE time_log_A AS
SELECT
    JobName,
    SEC_TO_TIME(FLOOR((TIME_TO_SEC(avgEndTime)+450)/900)*900) AS I
FROM
    time_log
;

CREATE TABLE time_log_B AS
SELECT
    I,
    COUNT(DISTINCT JobName) AS NumberOfJobs
FROM
    time_log_A
GROUP BY
    I
;

SELECT DISTINCT
  INC.Increment AS "Interval",
    B.NumberOfJobs,
    A.JobName
FROM
  #15MinuteIncrements INC
  LEFT JOIN time_log_A A
      ON A.I = INC.Increment
    LEFT JOIN time_log_B B
      ON A.I = INC.Increment
ORDER BY
  INC.Increment,
    A.JobName
;

15 min increment query

1 Answers1