CODEWITHSUNDEEP
sqlsql-serverdatetimegroup-by
Daniel
Daniel

Reputation: 2295

Calculate time span over a number of records

I have a table that has the following schema:

ID | FirstName | Surname | TransmissionID | CaptureDateTime
1  | Billy     | Goat    | ABCDEF         | 2018-09-20 13:45:01.098
2  | Jonny     | Cash    | ABCDEF         | 2018-09-20 13:45.01.108
3  | Sally     | Sue     | ABCDEF         | 2018-09-20 13:45:01.298
4  | Jermaine  | Cole    | PQRSTU         | 2018-09-20 13:45:01.398
5  | Mike      | Smith   | PQRSTU         | 2018-09-20 13:45:01.498

There are well over 70,000 records and they store logs of transmissions to a web-service. What I'd like to know is how would I go about writing a script that would select the distinct TransmissionID values and also show the timespan between the earliest CaptureDateTime record and the latest record? Essentially I'd like to see what the rate of records the web-service is reading & writing.

Is it even possible to do so in a single SELECT statement or should I just create a stored procedure or report in code? I don't know where to start aside from SELECT DISTINCT TransmissionID for this sort of query.

Here's what I have so far (I'm stuck on the time calculation)

SELECT DISTINCT [TransmissionID],
        COUNT(*) as 'Number of records'
  FROM [log_table]
  GROUP BY [TransmissionID]
  HAVING COUNT(*) > 1

Not sure how to get the difference between the first and last record with the same TransmissionID I would like to get a result set like:

TransmissionID | TimeToCompletion | Number of records |
ABCDEF         |            2.001 |             5000  |

Upvotes: 0

Views: 138

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270493

A method that returns the average time for all transmissionids, even those with only 1 record:

SELECT TransmissionID,
    COUNT(*),
    DATEDIFF(second, MIN(CaptureDateTime), MAX(CaptureDateTime)) * 1.0 / NULLIF(COUNT(*) - 1, 0)
FROM yourdata
GROUP BY TransmissionID;

Note that you may not actually want the maximum of the capture date for a given transmissionId. You might want the overall maximum in the table -- so you can consider the final period after the most recent record.

If so, this looks like:

SELECT TransmissionID,
    COUNT(*),
    DATEDIFF(second,
             MIN(CaptureDateTime),
             MAX(MAX(CaptureDateTime)) OVER ()
            ) * 1.0 / COUNT(*)
FROM yourdata
GROUP BY TransmissionID;

Upvotes: 1

Salman Arshad
Salman Arshad

Reputation: 272256

Simply GROUP BY and use MIN / MAX function to find min/max date in each group and subtract them:

SELECT
    TransmissionID,
    COUNT(*),
    DATEDIFF(second, MIN(CaptureDateTime), MAX(CaptureDateTime))
FROM yourdata
GROUP BY TransmissionID
HAVING COUNT(*) > 1

Upvotes: 3

Fahmi
Fahmi

Reputation: 37473

Use min and max to calculate timespan

SELECT [TransmissionID],
        COUNT(*) as 'Number of records',datediff(s,min(CaptureDateTime),max(CaptureDateTime)) as timespan
  FROM [log_table]
  GROUP BY [TransmissionID]
  HAVING COUNT(*) > 1

Upvotes: 2

Related Questions