Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2010
    Posts
    11

    Unanswered: DATEDIFF help - MS SQL 2008

    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	lastruntime			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!
    Last edited by justintoo1; 07-17-12 at 19:27.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @jobs TABLE (
       jobid	INT
    ,  lastruntime	DATETIME
       )
       
    INSERT INTO @jobs (
       jobid, lastruntime)
       SELECT 9740, '2012-07-17 12:07:29.777' UNION ALL
       SELECT 9740, '2012-07-17 11:48:46.127' UNION ALL
       SELECT 9740, '2012-07-17 11:29:54.050' UNION ALL
       SELECT 9740, '2012-07-17 11:09:23.807' UNION ALL
       SELECT 9740, '2012-07-17 10:49:55.010'
    
    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
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2010
    Posts
    11

    Thumbs up

    Hello Pat,

    This is fantastic!

    I greatly apperciate this.

    Much thanks - have a good one!

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The LEAD and LAG funtions were added in SQL 2012:
    Code:
    DECLARE @jobs TABLE (
       jobid	INT
    ,  lastruntime	DATETIME
       )
       
    INSERT INTO @jobs (
       jobid, lastruntime)
       SELECT 9740, '2012-07-17 12:07:29.777' UNION ALL
       SELECT 9740, '2012-07-17 11:48:46.127' UNION ALL
       SELECT 9740, '2012-07-17 11:29:54.050' UNION ALL
       SELECT 9740, '2012-07-17 11:09:23.807' UNION ALL
       SELECT 9740, '2012-07-17 10:49:55.010' union all
       select 1234, '2012-07-17 10:27:00' union all
       select 1234, '2012-07-17 10:00:00' 
       
    select jobid, 
        lastruntime, 
        datediff(ss, lastruntime, lag(lastruntime, 1) over (partition by jobid order by lastruntime desc))
    from @jobs
    Gives the same output as Pat's. Use may depend on SQL Version, and which code you consider easier to wrap your brain around.

  5. #5
    Join Date
    Feb 2010
    Posts
    11
    Hello MCrowley,

    Thanks for the input on the matter.

    however, when I attemp to run I receive the following...

    Code:
    The Parallel Data Warehouse (PDW) features are not enabled.
    I obviously am missing PDW. That's OK though, Pat's it's sufficient.

    Thanks for the effort, much appericated!!

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by MCrowley View Post
    The LEAD and LAG funtions were added in SQL 2012:
    True, and I could have done a CTE dance too, but there were some quirks with CTEs in a few of the SQL 2008 service packs. I went with what Lewis Grizzard said: "Shoot low boys, they're riding Shetland Ponies" and provided SQL that ought to work with any SQL that supports SQL-92.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by justintoo1 View Post
    I obviously am missing PDW. That's OK though, Pat's it's sufficient.
    Just an observation, but this code gets a wee bit wonky when there are "ties" in the lastruntime column. Depending on how you (and your application) think about your data, you might need to revisit that issue.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I wonder if PDW is an enterprise feature. I am messing around with developer edition, so it is hard to tell. They certainly don't warn you.

    It was more an exercise for myself, to see if it could be done a different way.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •