CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2
Pete Dragun
Pete Dragun

Reputation: 37

How to calculate Duration at a specific range T-SQL

The following query will build a tempoary table with Item 1, 2, and 3. The idea of this is to generate a simple dataset of an issue I am facing.

Running it will produce the following result:

Target  PollTime    PrevPollTime    Duration
-------------------------------------------------------------
Item1   2014-11-18 00:12:14.000 2014-11-18 00:05:14.000 7
Item3   2014-11-18 00:08:14.000 2014-11-18 00:02:14.000 6
Item3   2014-11-18 00:14:14.000 2014-11-18 00:08:14.000 6

However if you change the @EndDate parameter to '2014-11-18 00:11:14' the result is:

Item3   2014-11-18 00:08:14.000 2014-11-18 00:02:14.000 6

My issue is that I would like to add the functionality that the result would be:

Item1   2014-11-18 00:11:14.000 2014-11-18 00:05:14.000 6
Item3   2014-11-18 00:08:14.000 2014-11-18 00:02:14.000 6

And if the @EndDate parameter was changed to '2014-11-18 00:25:14':

Item1   2014-11-18 00:12:14.000 2014-11-18 00:05:14.000 7
Item1   2014-11-18 00:25:14.000 2014-11-18 00:14:14.000 11
Item2   2014-11-18 00:25:14.000 2014-11-18 00:14:14.000 11
Item3   2014-11-18 00:08:14.000 2014-11-18 00:02:14.000 6
Item3   2014-11-18 00:14:14.000 2014-11-18 00:08:14.000 6
Item3   2014-11-18 00:25:14.000 2014-11-18 00:14:14.000 11

The @EndDate Parameter would have to be include somehow. 

DROP TABLE #TempTable
CREATE TABLE #TempTable(
Target VARCHAR(255),
PollTime DATETIME)

INSERT INTO #TempTable (Target, PollTime) VALUES('Item1', CAST('2014-11-18 00:00:14.000' AS    DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item1', CAST('2014-11-18 00:01:14.000' AS DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item1', CAST('2014-11-18 00:02:14.000' AS DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item1', CAST('2014-11-18 00:03:14.000' AS DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item1', CAST('2014-11-18 00:04:14.000' AS DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item1', CAST('2014-11-18 00:05:14.000' AS DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item1', CAST('2014-11-18 00:12:14.000' AS DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item1', CAST('2014-11-18 00:13:14.000' AS DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item1', CAST('2014-11-18 00:14:14.000' AS DATETIME))

INSERT INTO #TempTable (Target, PollTime) VALUES('Item2', CAST('2014-11-18 00:00:14.000' AS DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item2', CAST('2014-11-18 00:01:14.000' AS DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item2', CAST('2014-11-18 00:02:14.000' AS DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item2', CAST('2014-11-18 00:03:14.000' AS DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item2', CAST('2014-11-18 00:04:14.000' AS DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item2', CAST('2014-11-18 00:05:14.000' AS DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item2', CAST('2014-11-18 00:06:14.000' AS DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item2', CAST('2014-11-18 00:07:14.000' AS DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item2', CAST('2014-11-18 00:08:14.000' AS DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item2', CAST('2014-11-18 00:09:14.000' AS DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item2', CAST('2014-11-18 00:10:14.000' AS DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item2', CAST('2014-11-18 00:11:14.000' AS DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item2', CAST('2014-11-18 00:12:14.000' AS DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item2', CAST('2014-11-18 00:13:14.000' AS DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item2', CAST('2014-11-18 00:14:14.000' AS DATETIME))

INSERT INTO #TempTable (Target, PollTime) VALUES('Item3', CAST('2014-11-18 00:01:14.000' AS DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item3', CAST('2014-11-18 00:02:14.000' AS DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item3', CAST('2014-11-18 00:08:14.000' AS    DATETIME))
INSERT INTO #TempTable (Target, PollTime) VALUES('Item3', CAST('2014-11-18 00:14:14.000' AS DATETIME))

DECLARE @StartDate DATETIME = CAST('2014-11-18 00:00:00' AS DATETIME)
DECLARE @EndDate DATETIME = CAST('2014-11-18 00:15:00' AS DATETIME);

WITH Data AS (
SELECT *
FROM #TempTable 
WHERE PollTime BETWEEN @StartDate AND @EndDate
), 
RankData AS 
(
SELECT * FROM Data A
    CROSS APPLY 
    (
        SELECT 
            PrevPollTime = PollTime
        FROM 
        (
            SELECT *, 
                Rn = ROW_NUMBER()OVER(ORDER BY PollTime DESC)
            FROM #TempTable B
            WHERE B.Target = A.Target AND B.PollTime < A.PollTime 
        ) T WHERE Rn = 1 
    ) AS C          
WHERE (DATEDIFF(MINUTE, C.PrevPollTime, A.PollTime) > 5)
)
SELECT *, 
Duration = DATEDIFF(MINUTE, PrevPollTime, PollTime) 
FROM RankData

Upvotes: 0

Views: 77

Answers (2)

Matej Hlavaj
Matej Hlavaj

Reputation: 1038

modify your CTE with two CASE clausules. Here is modified code: (modified version, try it :) , now it working :-))

DECLARE @StartDate DATETIME = CAST('2014-11-18 00:00:00' AS DATETIME)
DECLARE @EndDate DATETIME = CAST('2014-11-18 00:25:14' AS DATETIME);


IF OBJECT_ID('tempdb..#TempTable2') IS NOT NULL DROP TABLE #TempTable2
SELECT Target,PollTime 
INTO #TempTable2
FROM #TempTable
UNION ALL
SELECT DISTINCT Target,@EndDate AS PollTime
FROM #TempTable
WHERE @EndDate > PollTime;



WITH Data AS (
SELECT DISTINCT Target,CASE WHEN PollTime > @EndDate THEN @EndDate ELSE PollTime END AS PollTime
FROM #TempTable2
WHERE PollTime BETWEEN @StartDate AND (CASE WHEN PollTime > @EndDate THEN PollTime ELSE @EndDate END)
), 
RankData AS 
(
SELECT * FROM Data A
    CROSS APPLY 
    (
        SELECT 
            PrevPollTime = PollTime
        FROM 
        (
            SELECT *, 
                Rn = ROW_NUMBER()OVER(ORDER BY PollTime DESC)
            FROM #TempTable2 B
            WHERE B.Target = A.Target AND B.PollTime < A.PollTime 
        ) T WHERE Rn = 1 
    ) AS C          
WHERE (DATEDIFF(MINUTE, C.PrevPollTime, A.PollTime) > 5)
)
SELECT *, 
Duration = DATEDIFF(MINUTE, PrevPollTime, PollTime) 
FROM RankData

Upvotes: 1

Pete Dragun
Pete Dragun

Reputation: 37

I added a UNION where I get the Max date and make my compairson, rewriting my query a little. 

DECLARE @StartDate DATETIME = CAST('2014-11-18 00:00:00' AS DATETIME)
DECLARE @EndDate DATETIME = CAST('2014-11-18 00:20:14' AS DATETIME);

WITH Data AS (
SELECT 
    DISTINCT Target, 
    PollTime = CASE WHEN PollTime > @EndDate THEN @EndDate ELSE PollTime END
FROM #TempTable 
WHERE PollTime BETWEEN @StartDate AND (CASE WHEN PollTime > @EndDate THEN PollTime ELSE @EndDate END)
), 
RankData AS 
(
SELECT * FROM Data A
    CROSS APPLY 
    (
        SELECT 
            PrevPollTime = PollTime
        FROM 
        (
            SELECT *, 
                Rn = ROW_NUMBER()OVER(ORDER BY PollTime DESC)
            FROM #TempTable B
            WHERE B.Target = A.Target AND B.PollTime < A.PollTime 
        ) T WHERE Rn = 1 
    ) AS C          
UNION 
SELECT 
    Target,
    PollTime = @EndDate,
    PrevPollTime = MAX(PollTime)
FROM Data A
WHERE PollTime BETWEEN @StartDate AND @EndDate      
GROUP BY Target             
)
SELECT *, 
Duration = DATEDIFF(MINUTE, PrevPollTime, PollTime) 
FROM RankData WHERE (DATEDIFF(MINUTE, PrevPollTime, PollTime) > 5)

Upvotes: 0

Related Questions