-
DATEDIFF - time between logins
Hi,
I have a query that I am having some trouble with.
Code:
select jobid, lastruntime
from jobs where jobid= 9740
order by lastruntime desc
..produces...
Code:
jobid lastruntime
9740 2012-07-17 12:07:29.777
9740 2012-07-17 11:48:46.127
9740 2012-07-17 11:29:54.050
9740 2012-07-17 11:09:23.807
9740 2012-07-17 10:49:55.010
What I need to do is create another column that utilizes the DATEDIFF function to find the time between lastruntime. I know I can use DATEDIFF, but the problem lies with the fact that this table (jobs) has no unique column.
i can use the following query, but it requires me to manually enter in the start and end dates...
Code:
select datediff (minute, '2012-07-13 13:23:31.793', '2012-07-13 13:45:13.250') as 'time between lastruntime'
What I need is results like the following....
Code:
userid logintime time between lastruntime
9740 2012-07-17 12:07:29.777 -19
9740 2012-07-17 11:48:46.127 -19
9740 2012-07-17 11:29:54.050 etc...
9740 2012-07-17 11:09:23.807 etc..
9740 2012-07-17 10:49:55.010 etc..
Can anyone help me implement this logically...?
Thanks!
-
Code:
;with CTE (jobid, lastruntime, rownum)
as (
select jobid
, lastruntime
, row_number() over (order by lastruntime desc)
from jobs
)
select CTE.jobid
, CTE.lastruntime
, datediff(minute, CTE2.lastruntime, CTE.Lastruntime) 'time between lastruntime'
from CTE
left join CTE CTE2
on CTE2.rownum = CTE.rownum+1
-
RESOLVED - DATEDIFF - time between logins
Hello Stork,
I thank you for you reply. I should have update this forum with my solution.
Code:
SELECT this.jobid, this.lastruntime, DATEDIFF(s, prev.lastruntime, this.lastruntime)
FROM @jobs AS this
LEFT JOIN @jobs AS prev
ON (prev.jobid = this.jobid
AND prev.lastruntime = (SELECT Max(z1.lastruntime)
FROM @jobs AS z1
WHERE z1.jobid = this.jobid
AND z1.lastruntime < this.lastruntime))
ORDER BY this.jobid, this.lastruntime
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|