CODEWITHSUNDEEP
t-sqlsql-server-2008-r2
Mazhar
Mazhar

Reputation: 3837

TSQL Cursor Alternative to Speed up my query

 Row Status  Time
 1   Status1 1383264075
 2   Status1 1383264195
 3   Status1 1383264315
 4   Status2 1383264435
 5   Status2 1383264555
 6   Status2 1383264675
 7   Status2 1383264795
 8   Status1 1383264915
 9   Status3 1383265035
 10  Status3 1383265155
 11  Status2 1383265275
 12  Status3 1383265395
 13  Status1 1383265515
 14  Status1 1383265535
 15  Status2 1383265615

The [Time] column holds POSIX time

I want to be able to calculate the number of seconds a given [Status] is active for within a given time period without using CURSORS. If this is the only then that is fine as I've already done that.

Using the above sample data extract, how do I calculate how long "Status1" has been active for?

That is, Substract Row1.[Time] from Row4.[Time], Substract Row8.[Time] from Row9.[Time], Substract Row13.[Time] from Row15.[Time].

Thankyou in advance

Upvotes: 2

Views: 311

Answers (2)

Andriy M
Andriy M

Reputation: 77657

The solution by @erikxiv will work if the Row values have no gaps. If they do have gaps, you could try the following method:

SELECT
  TotalDuration = SUM(next.Time - curr.Time)
FROM
  dbo.atable AS curr
CROSS APPLY
  (
    SELECT TOP (1) Time
    FROM dbo.atable
    WHERE Row > curr.Row
    ORDER BY Row ASC
  ) AS next
WHERE
  curr.Status = 'Status1'
;

For every row matching the specified status, the correlated subquery in the CROSS APPLY clause will fetch the next Time value based on the ascending order of Row. The current row's time is then subtracted from the next row's time and all the differences are added up using SUM().

Please note that in both solutions it is implied that the order of Row values follows the order of Time values. In other words, ORDER BY Row is assumed to be equivalent to ORDER BY Time or, if Time can have duplicates, to ORDER BY Time, Row.

Upvotes: 1

erikxiv
erikxiv

Reputation: 4075

Assuming that each row represents that the specific Status is active from the specified Time until the next row, one would have to somehow calculate the difference between row N and N+1. One way would be to use a nested query (try it here: SQL Fiddle).

SELECT SUM(Duration) as Duration
FROM (
  SELECT f.Status, s.Time-f.Time as Duration
  FROM Table1 f
  JOIN Table1 s on s.Row = f.Row+1
  WHERE f.Status = 'Status1') a

Upvotes: 2

Related Questions