How To find time difference between two rows of record

Question

How To find time difference between two rows of record

windows_mss

SSCrazy

Points: 2426
More actions
November 6, 2015 at 11:49 am

#302160

Hi
I have the table with the similar set of records which mentioned below, i'm little bit confused to find the time difference between two rows of record. By Using the MsgOut column i have to find time taken b/w PS & PV and some record doesnt have PV .
LogID LocIDClientCert MsgType MsgOutMessageTimeStamp System
1151334934NOT SPECIFIEDQ_T12PS 2015-10-01 00:00:40.980AHR
1151335243NOT SPECIFIEDD_T12PV 2015-10-01 00:00:53.800AHR
1151342944NOT SPECIFIEDQ_T12PS 2015-10-01 00:05:40.957AHR
1151343281NOT SPECIFIEDD_T12PV 2015-10-01 00:05:53.670AHR
1151350046NOT SPECIFIEDQ_T12PS 2015-10-01 00:10:40.970AHR
1152760563759NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:29.617AHR
1152760739690NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:33.633AHR
Please help me out.
___
Known Is An Drop,Unknown Is An Ocean....
Njoy Programming 🙂

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

windows_mss (11/6/2015)
Hi
I have the table with the similar set of records which mentioned below, i'm little bit confused to find the time difference between two rows of record. By Using the MsgOut column i have to find time taken b/w PS & PV and some record doesnt have PV .
LogID LocIDClientCert MsgType MsgOutMessageTimeStamp System
1151334934NOT SPECIFIEDQ_T12PS 2015-10-01 00:00:40.980AHR
1151335243NOT SPECIFIEDD_T12PV 2015-10-01 00:00:53.800AHR
1151342944NOT SPECIFIEDQ_T12PS 2015-10-01 00:05:40.957AHR
1151343281NOT SPECIFIEDD_T12PV 2015-10-01 00:05:53.670AHR
1151350046NOT SPECIFIEDQ_T12PS 2015-10-01 00:10:40.970AHR
1152760563759NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:29.617AHR
1152760739690NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:33.633AHR
Please help me out.

Based on your sample what should the output look like?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

windows_mss SSCrazy Points: 2426 More actions · Answer 2

Thanks Sean,

The result will be looks like as below,

LocIDClientCertificationMsgType Time Taken

NOT SPECIFIED QRY_T12 10 Sec

759NOT SPECIFIED QRY_T12 30 Sec

690NOT SPECIFIED QRY_T12 40 Sec

___
Known Is An Drop,Unknown Is An Ocean....
Njoy Programming 🙂

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

windows_mss (11/6/2015)
Thanks Sean,
The result will be looks like as below,
LocIDClientCertificationMsgType Time Taken
NOT SPECIFIED QRY_T12 10 Sec
759NOT SPECIFIED QRY_T12 30 Sec
690NOT SPECIFIED QRY_T12 40 Sec

You are going to have to explain this a bit more clearly. I would recommend starting with sample data in a consumable format. That means a create table statement and an insert statement to populate it. Then an explanation of how you get those results. From the sample data to your stated output I just don't see any correlation there at all.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Here is your data turned into something consumable.

if OBJECT_ID('tempdb..#Something') is not null

drop table #Something

create table #Something

(

LogID int

, LocID int

, ClientCert varchar(20)

, MsgType varchar(10)

, MsgOut char(2)

, MessageTimeStamp datetime

, System char(3)

)

insert #Something

select 1151334934, null, 'NOT SPECIFIED', 'Q_T12', 'PS', '2015-10-01 00:00:40.980', 'AHR' union all

select 1151335243, null, 'NOT SPECIFIED', 'D_T12', 'PV', '2015-10-01 00:00:53.800', 'AHR' union all

select 1151342944, null, 'NOT SPECIFIED', 'Q_T12', 'PS', '2015-10-01 00:05:40.957', 'AHR' union all

select 1151343281, null, 'NOT SPECIFIED', 'D_T12', 'PV', '2015-10-01 00:05:53.670', 'AHR' union all

select 1151350046, null, 'NOT SPECIFIED', 'Q_T12', 'PS', '2015-10-01 00:10:40.970', 'AHR' union all

select 1152760563, 759, 'NOT SPECIFIED', 'Q_T12', 'PS', '2015-10-01 15:28:29.617', 'AHR' union all

select 1152760739, 690, 'NOT SPECIFIED', 'Q_T12', 'PS', '2015-10-01 15:28:33.633', 'AHR'

select *

from #Something

Now if you can explain the business rules for the output I will help you create the query.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

windows_mss SSCrazy Points: 2426 More actions · Answer 5

Thanks Sean,

Business of the output is to find the number of request per LocID, avg time taken per request, min time taken & max time taken.

Combination of 1 PS & 1 PV is consider as 1 request, some transaction will have only PS so such transaction we consider as one request and time consumed will be 0 Secs.

I have come up the sample resultset.

if OBJECT_ID('tempdb..#SomethingRes') is not null

drop table #SomethingRes

create table #SomethingRes

(

LogID varchar(3)

,ClientCert varchar(25)

, MsgType varchar(25)

,Response varchar(25)

,Requests int

, AveTime int

, MinimumTransactionTime int

, MaximumTransactionTime int

, Start datetime

, Finish datetime

)

insert #SomethingRes

SELECT '', 'NOT SPECIFIED', 'Unknown','PS',3,2,1,2,'2015-10-02 16:21:01.803','2015-10-22 17:14:51.900' union all

SELECT 759, 'NOT SPECIFIED','Q_T12','PS',2,2,1,2,'2015-10-07 15:49:15.277','2015-10-22 17:14:51.900' union all

SELECT 690, 'NOT SPECIFIED','Q_T12','PS',1,2,1,2,'2015-10-07 16:59:50.090','2015-10-22 17:14:51.900'

SELECT * FROM #SomethingRes

___
Known Is An Drop,Unknown Is An Ocean....
Njoy Programming 🙂

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

windows_mss (11/6/2015)
Thanks Sean,
Business of the output is to find the number of request per LocID, avg time taken per request, min time taken & max time taken.
Combination of 1 PS & 1 PV is consider as 1 request, some transaction will have only PS so such transaction we consider as one request and time consumed will be 0 Secs.
I have come up the sample resultset.
if OBJECT_ID('tempdb..#SomethingRes') is not null
drop table #SomethingRes
create table #SomethingRes
(
LogID varchar(3)
,ClientCert varchar(25)
, MsgType varchar(25)
,Response varchar(25)
,Requests int
, AveTime int
, MinimumTransactionTime int
, MaximumTransactionTime int
, Start datetime
, Finish datetime
)
insert #SomethingRes
SELECT '', 'NOT SPECIFIED', 'Unknown','PS',3,2,1,2,'2015-10-02 16:21:01.803','2015-10-22 17:14:51.900' union all
SELECT 759, 'NOT SPECIFIED','Q_T12','PS',2,2,1,2,'2015-10-07 15:49:15.277','2015-10-22 17:14:51.900' union all
SELECT 690, 'NOT SPECIFIED','Q_T12','PS',1,2,1,2,'2015-10-07 16:59:50.090','2015-10-22 17:14:51.900'
SELECT * FROM #SomethingRes

But this doesn't make any sense. There is nothing that ties a PS to a PV yet there are only 3 PS rows in the output?? Then there are number or requests but it doesn't make any sense how you come up with those values. This is probably a really simple query but there just aren't any rules that make sense yet.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

windows_mss SSCrazy Points: 2426 More actions · Answer 7

Thanks a lot Sean Lange for your guidance, I planning to do some code changes in the product to log the proper logs into this table.

Thanks Once Again.

___
Known Is An Drop,Unknown Is An Ocean....
Njoy Programming 🙂