August 18, 2008 at 10:21 am
Hi:
I'd like to know if I can include in a result set a calculated value based on the difference of column values in adjacent table rows. Consider the following 3-column table:
[font="Courier New"]Key StartTimestamp EndTimestamp
--------------------------------------------------
1 08/01/08 12:00:00 AM 08/01/08 01:00:00 AM
2 08/01/08 02:00:00 AM 08/01/08 03:15:00 AM
3 08/01/08 04:00:00 AM 08/01/08 05:30:00 AM
4 08/01/08 06:00:00 AM 08/01/08 07:45:00 AM[/font]
These four rows have start and end times. I'd like for Transact-SQL to return the difference between the first end time and the next start time. My output would contain three rows and might look like this (differences are in minutes):
[font="Courier New"]BeforeKey AfterKey Difference
------------------------------
1 2 60
2 3 45
3 4 30[/font]
Normally I'd use an application (.Net, VB, whatever) to return the results from the above table and iterate the records and calculate these difference. It just seems so inefficient though. Is there a way to get this from straight the DB? Thanks.
August 18, 2008 at 1:15 pm
If key is in sequence and there is no break in that sequence then you can use the current table values only, if there is break in key sequence, then get data in temporary table by adding a new identity column in it as:
Select identity(int,1,1) ID,* Into #Temp From Table1
then you can use the below query to fetch data, I used the table name as Table1
Select
Before.Key as BeforeKey
,After.Key as Afterkey
,datediff(mi,Before.Endtimestamp,isnull(After.Strattimestamp,Before.Endtimestamp)) Difference
From Table1 Before
Left Outer Join Table1 After On After.Key = Before.Key+1
Tariq
master your setup, master yourself.
http://mssqlsolutions.blogspot.com
August 18, 2008 at 2:51 pm
Excellent. Thanks Muhammad.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply