CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
sajoz
sajoz

Reputation: 23

Query to get the duration and details from a table

I have a scenario and not quite sure how to query it. As a sample, I have following table structure and want to get the history of the action for bus:

ID-----TIME---------BUSID----OPID----MOVING----STOPPED----PARKED----COUNT
1------10:10:10-----101------1101-----1---------0----------0---------15
2------10:10:11-----102------1102-----0---------1----------0---------5
3------10:11:10-----101------1101-----1---------0----------0---------15
4------10:12:10-----101------1101-----0---------1----------0---------15
5------10:13:10-----101------1101-----1---------0----------0---------19
6------10:14:10-----101------1101-----1---------0----------0---------19
7------10:15:10-----101------1101-----0---------1----------0---------19
8------10:16:10-----101------1101-----0---------0----------1---------0
9------10:17:10-----101------1101-----0---------0----------1---------0

I want to write a query to get the status of a bus like:

BUSID----OPID----STATUS-----TIME---------DURATION---COUNT
101------1101----MOVING-----10:10:10-----2-----------15
101------1101----STOPPED----10:12:10-----1-----------15
101------1101----MOVING-----10:13:10-----2-----------19
101------1101----STOPPED----10:15:10-----1-----------19
101------1101----PARKED-----10:16:10-----2-----------0
I am using SQL Server 2008.

Thanks for your help.

Upvotes: 2

Views: 491

Answers (4)

Daniel Vassallo
Daniel Vassallo

Reputation: 344521

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:

Upvotes: 2

pstanton
pstanton

Reputation: 36689

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.

Upvotes: 1

dave
dave

Reputation: 11985

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.

Upvotes: 0

kem
kem

Reputation: 1127

Making certain assumptions about column type, etc:

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

Upvotes: -1

Related Questions