4

I want to divided my to my time interval depending to the start time and end time. To explain it further, let me have this example. I have this table:

User           Elapsed Time        Start Time            End Time
User01         01:05               07-02-2015 08:45      07-02-2015 09:50

I want to divide its elapsed time to 30 minute time interval so that i will have an output like this.

Interval From        Interval To         User       Elapsed Time
07-02-2015 08:30     07-02-2015 09:00    User01     00:15
07-02-2015 09:00     07-02-2015 09:30    User01     00:30
07-02-2015 09:30     07-02-2015 10:00    User01     00:20

Thanks in advance

  • What are the data types? What is the longest interval you can have (e.g. are all start/end times for any given row guaranteed to be on the same day or within 24 hours of each other)? Why does your table bother storing elapsed time? This is redundant information you can already get from Start Time and End Time. – Aaron Bertrand Jul 2 '15 at 15:53
4

Here's a different approach that doesn't rely on loops or additional tables, however it does touch the source table twice. (You'll have to replace dbo.aTable with the name of your actual table, and add where clauses where commented below if you want to filter.)

DECLARE @i INT = 30; -- interval in minutes

DECLARE @ft SMALLDATETIME, @lt SMALLDATETIME;
SELECT @ft = MIN([Start Time]), @lt = MAX([End Time]) FROM dbo.aTable -- WHERE?;

;WITH d1(dt) AS
(
  SELECT TOP (DATEDIFF(MINUTE,@ft,@lt)/@i+2) 
    DATEADD(MINUTE,DATEDIFF(MINUTE,0,DATEADD(MINUTE,@i*(ROW_NUMBER() 
    OVER (ORDER BY [object_id])-1),@ft))/@i*@i,0)
  FROM sys.all_columns
), d2(s,e) AS (SELECT dt,DATEADD(MINUTE,@i,dt) FROM d1
), t AS
(
  SELECT [User],[Start Time],[End Time],
    StartInterval = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [Start Time])/@i*@i, 0),
    EndInterval   = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [End Time])/@i*@i, 0)
  FROM dbo.aTable -- WHERE?
),
n AS
(
  SELECT d2.s, d2.e, [User], [Start Time], [End Time],
    sr = CASE WHEN [Start Time] >= d2.s AND [Start Time] < d2.e THEN 1 END,
    er = CASE WHEN [End Time] > d2.s AND [End Time] <= d2.e THEN 1 END
   FROM d2 INNER JOIN t ON d2.s BETWEEN t.StartInterval AND t.EndInterval
 )
 SELECT [Interval From] = s, [Interval To] = e, [User], 
   [Elapsed Time] = CASE WHEN sr = 1 OR er = 1 THEN
       DATEDIFF(MINUTE, CASE WHEN sr = 1 THEN [Start Time] ELSE s END,
       CASE WHEN er = 1 THEN [End Time] ELSE e END)
   WHEN s = [End Time] THEN 0 ELSE @i END
 FROM n ORDER BY [User], [Interval From];
1

Fun problem! As Aaron mentions in his comment, a few things are ambiguous. But I put together a script that could be used as a good starting point. The script first identifies all possible 30 minute intervals, then matches user "events" to all matching intervals and does some date math to compute your elapsed times. The script also attempts to handle a user being active multiple times within the same interval. For your test inputs, the outputs match what you want.

If you expand to a larger data set, you may need to construct the final query more carefully to ensure that we only process the necessary intervals. If you create a test script that has a larger volume of data and actually exhibits performance problems, I'd be happy to take a look at that in more detail.

-- Build the test data
CREATE TABLE #userEvents (
    userName VARCHAR(100) NOT NULL,
    elapsedTime AS CONVERT(VARCHAR, endTime-startTime, 108),
    startTime DATETIME NOT NULL,
    endTime DATETIME NOT NULL
)
GO
INSERT INTO #userEvents (userName, startTime, endTime)
VALUES ('User01', '07-02-2015 08:45', '07-02-2015 09:50')
GO

-- Create a table to hold all possible 30 minute intervals
CREATE TABLE #dailyIntervals (
    intervalStartTime DATETIME NOT NULL,
    intervalEndTime AS DATEADD(mi, 30, intervalStartTime),
    PRIMARY KEY (intervalStartTime)
)
GO

-- Determine the dates that the test data spans
-- This will be used build the possible 30 minute intervals
DECLARE @minDate DATE, @maxDate DATE, @numDays INT
SELECT  @minDate = CONVERT(DATE, MIN(startTime)),
        @maxDate = CONVERT(DATE, MAX(endTime)),
        @numDays = DATEDIFF(dd, @minDate, @maxDate) + 1
FROM #userEvents;

-- Build the full set of 30 minute intervals to be considered 
WITH intervalMinutesIntoDay AS (
    -- Build starting # of minutes into the day for the 48 daily intervals of 30 minutes (0, 30, ..., 1410)
    SELECT TOP 48 30 * (ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1) AS startMinutes
    FROM master..spt_values
),
intervalStartDates AS (
    -- Build the dates (not including time component) that we need to consider
    SELECT TOP(@numDays) DATEADD(dd, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1, @minDate) AS startDate
    FROM master..spt_values
)
INSERT INTO #dailyIntervals (intervalStartTime)
SELECT DATEADD(mi, m.startMinutes, CONVERT(DATETIME, d.startDate)) AS intervalStartTime
FROM intervalStartDates d
CROSS JOIN intervalMinutesIntoDay m
GO

WITH userIntervals AS (
    -- For each user event, slot it into the proper intervals
    SELECT i.intervalStartTime,
            i.intervalEndTime,
            ue.userName,
            -- Start time is the user's startTime or, if the event started before this interval, the start of the interval
            CASE WHEN ue.startTime > i.intervalStartTime THEN ue.startTime ELSE i.intervalStartTime END AS userIntervalStartTime,
            -- End time is the user's entTime or, if the event ended after this interval, the end of the interval
            CASE WHEN ue.endTime < i.intervalEndTime THEN ue.endTime ELSE i.intervalEndTime END AS userIntervalEndTime
    FROM #dailyIntervals i
    JOIN #userEvents ue
        -- Detect whether user is active during some (or all) of the interval
        -- While it's a bit harder to read, the alternate option below may improve performance
        -- by allowing a seek to process only the necessary intervals
        ON ue.startTime <= i.intervalEndTime/*Alterate option: ON DATEADD(mi, -30, ue.startTime) <= i.intervalStartTime*/
        AND ue.endTime >= i.intervalStartTime
)
-- For each interval that the user event matched, compute the number of minutes the user was active during that interval
-- Note that we GROUP BY and SUM the data in case a user is active multiple times within the same interval
SELECT ui.intervalStartTime, ui.intervalEndTime, ui.userName,
    SUM(DATEDIFF(mi, userIntervalStartTime, userIntervalEndTime)) AS elapsedTimeInInterval
FROM userIntervals ui
GROUP BY ui.intervalStartTime, ui.intervalEndTime, ui.userName
GO

-- Cleanup
DROP TABLE #userEvents
GO
DROP TABLE #dailyIntervals
GO
0

Unfortunately, the starting point of your data makes this rather challenging. If it must be done at runtime, this is one possible solution:

-- Mimicing existing data

DECLARE @TableName TABLE ([User] VARCHAR(6), [Elapsed Time] VARCHAR(5), [Start Time] DATETIME, [End Time] DATETIME)

INSERT INTO @TableName VALUES ('User01', '01:05', '07-02-2015 08:45', '07-02-2015 09:50')

-- Setting up dateframe creation

DECLARE @mintime DATETIME = (SELECT MIN([Start Time]) FROM @TABLENAME),
        @maxtime DATETIME = (SELECT MAX([End Time]) FROM @TABLENAME)

DECLARE @i DATETIME =  DATEADD(MINUTE, (DATEPART(MINUTE, @mintime )/30) * 30, DATEADD(HOUR, DATEDIFF(HOUR, 0, @mintime), 0)),
        @tolastinterval DATETIME =  DATEADD(MINUTE, (DATEPART(MINUTE, @maxtime )/30) * 30, DATEADD(HOUR, DATEDIFF(HOUR, 0, @maxtime), 0))

DECLARE @dateframe TABLE (datetime DATETIME)

-- Populate DateFrame Table

WHILE @i <= @tolastinterval
BEGIN
    INSERT INTO @dateframe SELECT @i

    SET @i = DATEADD(MINUTE, 30, @i)
END

SELECT d.[datetime] [Interval From],
       DATEADD(MINUTE, 30, d.datetime) [Interval To],
       t.[User],
       '00:' + LEFT(CAST(MIN(
                    DATEDIFF(MINUTE, 
                    CASE WHEN d.[datetime] < t.[Start Time] THEN t.[Start Time] ELSE d.[datetime] END, 
                    CASE WHEN t.[End Time] < DATEADD(MINUTE, 30, d.datetime) THEN t.[End Time] ELSE DATEADD(MINUTE, 30, d.datetime) END
                    )) AS CHAR(2)) + '0', 2)
FROM @dateframe d
FULL OUTER JOIN @TABLENAME t
    ON 1=1
WHERE d.[datetime] <= t.[End Time]
    OR DATEADD(MINUTE, 30, d.datetime) >= t.[Start Time]
GROUP BY d.[datetime],
       t.[User]

Though it's horrible to look at.

If this is a common issue that you will want an ongoing solution to, another possible solution is to create a static dimension based on half hours and use that as the base, instead of dynamically creating the frame at runtime.

Also, this solution isn't robust enough to handle overlapping time entries for the same user- that would require another level of handling:

;WITH C1 AS
(
SELECT [user], [start time] AS ts, 1 AS type, ROW_NUMBER() OVER (ORDER BY [start time]) AS s, NULL AS e
FROM @TableName
UNION ALL
SELECT [user], [end time] AS ts, -1 AS type, NULL AS s, ROW_NUMBER() OVER (ORDER BY [end time]) AS e
FROM @TableName
),
C2 AS
(
SELECT C1.*, ROW_NUMBER() OVER (ORDER BY [user], ts, type DESC) AS se
FROM C1
),
C3 AS
(
SELECT [user],
       ts, 
       FLOOR((ROW_NUMBER() OVER (ORDER BY ts) - 1) / 2 + 1) AS grpnum
       FROM C2
       WHERE COALESCE(s - (se - s) - 1, (se - e) - e) = 0
) 

SELECT [user], MIN(ts) AS start_date, max(ts) AS end_date
FROM C3
GROUP BY [user], grpnum

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.