ansaurus

Question

Query to get the duration and details from a table

Answer 1

A:

Making certain assumptions about column type, etc:

SELECT
   BUSID,
   OPID,
   STATUS,
   TIME,
   DURATION,
   COUNT
FROM
   TABLENAME
WHERE
   BUSID = 1O1
ORDER BY
   TIME
;

kem 2009-12-12 01:23:09

This will not work as DURATION needs to be calculated.

sajoz 2009-12-12 01:29:53

right. i'm an idiot. sorry.

kem 2009-12-12 01:51:29

Answer 2

A:

I don't have access to SQL Server at the moment, so there may be syntax errors in the following:

SELECT
    BUSID,
    OPID,
    IF (MOVING = 1) 'MOVING' ELSE IF (STOPPED = 1) 'STOPPED' ELSE 'PARKED' AS STATUS
    TIME,
    COUNT
FROM BUS_DATA_TABLE
GROUP BY BUSID
ORDER BY TIME

You'll note that this does not include duration. Until you order your data, you don't know which is the previous entry. Once the data is ordered you can calculate the duration as the difference between the times in consecutive records. You could do this by SELECTing into a new table and then running a second query.

Grouping by BUSID, should give you your report for all buses.

dave 2009-12-12 01:36:42

Answer 3

+2 A:

You can use Common Table Expressions to calculate the duration between the different rows.

WITH cte_log AS
(
    SELECT 
        Row_Number() 
    OVER 
    (
        ORDER BY time DESC
    )
    AS 
        id, time, busid, opid, moving, stopped, parked, count
    FROM
        log_table
    WHERE 
        busid = 101
)
SELECT
    current_rows.busid,
    current_rows.opid,
    current_rows.time,
    DATEDIFF(second, current_rows.time, previous_rows.time) AS duration
    current_rows.count
FROM
    cte_log_position AS current_rows
LEFT OUTER JOIN 
    log_table AS previous_rows ON ((current_rows.row_id + 1) = previous_rows.row_id)
WHERE 
    current_rows.busid = 101
ORDER BY
    current_rows.time DESC;

The WITH statement creates a temporary result set that is defined within the execution scope of this query. We are using it to fetch the previous records of each row and to calculate the time difference between the the current and the previous record.

This example was not tested, and it may not work perfectly, but I hope it gets you going in the correct direction. Feel free to leave feedback.

You may also want to check the following external links on how to use Common Table Expressions:

Daniel Vassallo 2009-12-12 01:37:10

one problem is often id/sequences miss a value for example if a record has been deleted. you'd need to do a top/limit query where id>? to get the next record.

pstanton 2009-12-12 01:42:20

@pstanton: yes, it could be necessary. I am sure the above example needs to be tweaked for a practical application.

Daniel Vassallo 2009-12-12 01:46:30

Answer 4

+1 A:

personally i would denormalize the data so you have start_time and end_time in the one row. this will make the query much more efficient.

pstanton 2009-12-12 01:42:31

ansaurus

tags:

views:

answers:

Query to get the duration and details from a table

related questions