# Thread: how to get datediff of two different columns and different rows

1. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by sudhakarrajuk
hi
how to get datediff of two different columns and different rows..
with a self-join

3. Registered User
Join Date
Apr 2008
Location
Iasi, Romania
Posts
572
SELECT DATEDIFF(DAY, a.date1, a.date2)
FROM testtable a, testtable b
WHERE a.pk_field = a_value
AND b.pk_field = another_value

4. Registered User
Join Date
Aug 2009
Posts
262

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
•