CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2008-r2
usr021986
usr021986

Reputation: 3511

Time calulation according to state for a testcase

I have below information in SQL table (Do have other related field but these are main data required)

Name        State       Time
tst1        Start       2012-06-24 11:51:48.5210000
tst1        Completed   2012-06-24 11:56:48.5210000
tst1        InProgress  2012-06-24 11:53:48.5210000
tst1        Completed   2012-06-24 11:56:48.5210000
tst1        InProgess   2012-06-24 11:53:48.5210000
tst1        Start       2012-06-24 11:51:48.5210000

The above data are for a test & with its timestamp for relative state. I need to calculate the time it takes for complete one test from getting the difference for Start timestamp & Completed timestamp.

The below format is optional.but the time taken for one test case need to be calculated. If we have one test case with name & time stamp as same, then it can be considered as one.

//optional format
Also need to get the times taken for one state to another state.

Like

Name    State                 Timetaken
tst1    start-Completed         5:00
tst1    start-Inprogress        2:00
tst1    Inprogress-Completed    3:00

Note:tst1 data logged in the data can be random(as in the table above state is start, then completed then Inprogress)

Upvotes: 1

Views: 65

Answers (2)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

You can use the option with APPLY operator and values as a Table Source

SELECT [state], 
       CAST(COALESCE(Timetaken, Timetaken2, Timetaken3) AS time) AS Timetaken
FROM (
      VALUES('Start-Completed'),
            ('Start-Inprogress'),
            ('Inprogress-Completed')
      ) x([state]) 
CROSS APPLY (
             SELECT TOP 1 
               MAX(CASE WHEN [state] = 'Completed' 
                        THEN CAST([time] AS datetime) END) 
                 - MAX(CASE WHEN [state] = 'Start' 
                            THEN CAST([time] AS datetime) END) AS Timetaken,
               MAX(CASE WHEN [state] = 'InProgress' 
                        THEN CAST([time] AS datetime) END)
                 - MAX(CASE WHEN [state] = 'Start' 
                            THEN CAST([time] AS datetime) END) AS Timetaken2, 
               MAX(CASE WHEN [state] = 'Completed' 
                        THEN CAST([time] AS datetime) END) 
                 - MAX(CASE WHEN [state] = 'InProgress' 
                            THEN CAST([time] AS datetime) END) AS Timetaken3
             FROM dbo.test133 t
             WHERE x.[state] LIKE '%' + t.[state] + '%'
             GROUP BY name
             ) o

Result:

state                  Timetaken
Start-Completed        00:05:00.0000000
Start-Inprogress       00:02:00.0000000
Inprogress-Completed   00:03:00.0000000

See demo on SQLFiddle

Upvotes: 0

JoseTeixeira
JoseTeixeira

Reputation: 1306

If you have overlaping times for the tests which have the same name, just as in the example (twice start at 11:51 and twice completed at 11:56), then it is not possible to know which completed corresponds to which start.

SELECT t1.Name
    , CASE WHEN t1.State='Start' THEN 'Start-Inprogress'
           WHEN t1.State='InProgress' THEN 'Inprogress-Completed'
           WHEN t1.State='Completed' THEN 'Start-Completed'
    END AS State
    , CASE WHEN t1.State='Completed'
           THEN right(CONVERT(VARCHAR,(t1.Time-(SELECT max(Time) FROM TableName t2 WHERE t1.Name = t2.Name AND t2.Time < t1.Time AND t2.State='Start')),108),5)
           ELSE right(CONVERT(VARCHAR,((SELECT min(Time) FROM TableName t2 WHERE t1.Name = t2.Name AND t2.Time > t1.time AND t2.State!='Start')-t1.Time),108),5)
    END AS Timetaken
FROM TableName t1
GROUP BY t1.Name, t1.State, t1.Time

Upvotes: 1

Related Questions