Calculating 'time Difference' Between Two Records

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hello friends,

I was trying to find report in format

jobno | TimeDiff

for the following table

actionhistoryid	jobno	actiondate	        actionstatus
910891	        206448	2011-05-13 10:31:18.877	    33
910931	        206448	2011-05-13 10:38:43.673	     8

Where actionstatus value stands for 33(For-Marketing),8(Approve)
TimeDiff is time period, when the job was send For Marketing and when the job is Approved by marketing

can anyone tell me, how can i write query for the same?

Thanks in advance

Posted 12-May-11 19:46pm

dhage.prashant01

Updated 12-May-11 20:17pm

v2

Add a Solution

4 solutions

Solution 4

another way using inner join

select a.jobno, datediff(s, a.actiondate, b.actiondate)
from table a
inner join table b on a.jobno = b.jobno and a.actionhistoryid != b.actionhistoryid
and a.actionstatus = 33
where a.jobno = @jobno

Will give you the difference in seconds

Posted 15-May-11 20:55pm

nit_singh

Updated 15-May-11 20:56pm

v2

Solution 1

SQL

SELECT 
      jobno, 
      TimeDifference =  datediff(decimal(5,2), 0, (startTime - endTime))
FROM
(
      SELECT
            startTime = convert(datetime, select actiondate from table where ( jobno = jonId and actionstatus = 33 )),
            endTime   = convert(datetime, select actiondate from table where ( jobno = jobId and actionstatus = 8 ))
)

This should do the trick for you assuming you have a specific value for jobId.

Else you need to fine tune the code with small modification.

With datediff function you can get the time difference in Minutes, Seconds and Milliseconds.

This you can mention as the first parameter.

datediff ( format, startDate, endDate )

BR//
Harsha

Posted 12-May-11 20:23pm

ZeeroC00l

Updated 12-May-11 20:33pm

v3

Comments

dhage.prashant01 13-May-11 6:27am

I tried following

with CTE as (
select jobno,
(select actiondate from tbl_action_history A2 where A2.actionhistoryid=(A1.actionhistoryid-1)) [QC],
(select actiondate from tbl_action_history A3 where A3.actionhistoryid=A1.actionhistoryid) [ACC]
from tbl_action_history A1
where actionstatus = 8 )

SELECT C.jobno,C.QC,C.ACC,DATEDIFF(D,C.ACC,C.QC) FROM CTE C

C.QC values are coming wrong. As u see in my question the entries are back to back

any solution y C.QC values coming wrong?

Solution 2

use datdiff...

try this

Posted 12-May-11 21:17pm

beginner in C#.net

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

dhage.prashant01 · Accepted Answer · 2011-05-15T20:02:00

I got following the result for my query

WITH CTE AS
(
	select 
		A1.jobno,
		CONVERT(date, A1.actiondate) [QC],
		(select CONVERT(date, MAX(actiondate)) from tbl_action_history A2 
		where A2.jobno=A1.jobno and A2.actionhistoryid > A1.actionhistoryid and A2.actionstatus = 8) [ACC],
		actionstatus,cycle
	from tbl_action_history A1
	where actionstatus = 33 and CONVERT(date,actiondate) between '05/12/2011' and '05/13/2011')

SELECT C.jobno,C.QC,C.ACC,DATEDIFF(DAY,C.QC, C.ACC) [DateDiff],C.actionstatus,c.cycle
FROM CTE C order by C.jobno desc

Hope it helps others, if they face the same situation like me.