Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    5

    Unanswered: how to get datediff of two different columns and different rows

    hi
    how to get datediff of two different columns and different rows..

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sudhakarrajuk
    hi
    how to get datediff of two different columns and different rows..
    with a self-join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    SELECT DATEDIFF(DAY, a.date1, a.date2)
    FROM testtable a, testtable b
    WHERE a.pk_field = a_value
    AND b.pk_field = another_value
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  4. #4
    Join Date
    Aug 2009
    Posts
    262

    here is your example

    TABLE1
    RID identity ,seed1 ( as rowid )
    stn int ( as userid)
    starttime datetime
    endtime datetime,

    i needed to calculate the difference between end time rid=1 and start time rid=2 . i did this code.


    SELECT rid,STN,CASE WHEN DATEDIFF (S,
    (SELECT ENDTIME FROM #tempdcrd DD WHERE D.RID=DD.RID AND D.STN=DD.STN ),
    (SELECT TOP(1) CALLTIME FROM table1 DD WHERE D.STN=DD.STN AND DD.RID>D.RID )) >=0
    THEN DATEDIFF (S,
    (SELECT ENDTIME FROM #tempdcrd DD WHERE D.RID=DD.RID AND D.STN=DD.STN ),
    (SELECT TOP(1) CALLTIME FROM table1 DD WHERE D.STN=DD.STN AND DD.RID>D.RID ))
    WHEN DATEDIFF (S,
    (SELECT ENDTIME FROM table1 DD WHERE D.RID=DD.RID AND D.STN=DD.STN ),
    (SELECT TOP(1) CALLTIME FROM table1 DD WHERE D.STN=DD.STN AND DD.RID>D.RID )) IS NULL
    THEN SUM(D.DURATION)
    ELSE 0
    END AS interval
    INTO #TEMP2
    FROM table1 D
    GROUP BY STN,RID

Posting Permissions

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