How to get the time difference into another colum as we need to know the frequencies between the rows.
So provide me the sqlserver query to find the solution
For example the difference between 6.49 and 7.27 is 38 minutes and that 38 minutes should be displayed in another column.
The 3rd column will be the difference between 7.27 and 7.52 Like that continuosly..
Nilanka DharmadasaPosted Dec 14, 2009, 11:23 PM
Try this query.
SELECT time_column1, time_column2, ROUND(cast((datediff(mi, time_column2, time_column1) / 60.0) as FLOAT),2) AS time_difference FROM table_name
If you have any problem please ask me.
If you find this answer useful, please check 'Do you like this answer' checkbox.
Nilanka DharmadasaPosted Dec 15, 2009, 4:28 AM
Use following query.
select x.datetimecolumn , y.datetimecolumn,
ROUND(cast((datediff(mi, x.datetimecolumn, ISNULL(y.datetimecolumn, x.datetimecolumn)) / 60.0) as FLOAT),2) AS time_difference
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY datetimecolumn) as id_1, datetimecolumn
FROM table_name) x
left join
(SELECT ROW_NUMBER() OVER(ORDER BY datetimecolumn) as id_2, datetimecolumn
FROM table_name) y
on
x.id_1 = y.id_2 - 1
ramesh pPosted Dec 15, 2009, 2:36 AM
Thank you for your reply..
I have another requirement. I have one table, it is having only 1 datetime column.
DateTime
---------------------------
15/12/2009 5:00:00 AM
Now i want to write query for getting time difference between 1&2 rows, 2&3 rows, 3&4 rows, 4&5 rows....so on
result should be displyed in another column like this
Timediff
------------
0
30
15
15
30
10
Kirtan PatelPosted Dec 14, 2009, 11:22 PM
Here is your Solution .
Suppose your Time columns are Time1 And Time 2 then and make Sure that your time1,time2 column should be Numeric() datatype.
select Time1,Time2,(Time2-Time1) as 'Difference' from YourTableName;
if my answer helps you then check "Do you like this answer" checkbox please :)