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

---

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

---