Time Calculation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Time Calculation

Author

Topic

AlanPBates
Starting Member

34 Posts

Posted - 2010-05-07 : 15:19:41

I have a table -
CREATE TABLE [dbo].[SortStartEnd](
[RowIdentity] [varchar](20) NULL,
[SOS] [bit] NULL,
[EOS] [bit] NULL,
[SortTime] [datetime] NULL
) ON [PRIMARY]

Data inserted -
insert into SortStartEnd values ('Start','0','0','2010-05-07 12:00:49.723')
insert into SortStartEnd values ('End','0','1','2010-05-07 17:00:49.723')

The table will be updated periodically to reflect new timings. There will be only 2 rows at any given time.

How do I write a function to return the time elapsed between the 2 rows of data. This function will be called by other procedures about 3 or 4 times in an hour ?

Thanks,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-07 : 16:15:46

You can use the DATEDIFF function to return the time elapsed between the two rows.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

AlanPBates
Starting Member

34 Posts

Posted - 2010-05-07 : 16:17:24

I want to make a function .. how to use the dateDiff ?

Thanks,

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-07 : 16:25:04

Try this:

CREATE TABLE [dbo].[SortStartEnd](
[RowIdentity] [varchar](20) NULL,
[SOS] [bit] NULL,
[EOS] [bit] NULL,
[SortTime] [datetime] NULL
) ON [PRIMARY]

INSERT INTO SORTSTARTEND VALUES ('START','0','0','2010-05-07 14:00:49.723')
INSERT INTO SORTSTARTEND VALUES ('END','0','1','2010-05-07 16:00:49.723')
INSERT INTO SORTSTARTEND VALUES ('START','0','0','2010-05-07 17:00:49.723')
INSERT INTO SORTSTARTEND VALUES ('END','0','1','2010-05-07 18:00:49.723')

Select t.SortTime, t1.EndTime, Datediff(mi,t.SortTime,t1.EndTime) as DiffInMinutes
From [SortStartEnd] t
CROSS APPLY (SELECT Min(SortTime) AS EndTime
from [SortStartEnd]
Where SortTime > t.SortTime
and RowIdentity ='END') t1
WHERE [RowIdentity] ='START'

I am here to learn from Masters and help new bees in learning.

AlanPBates
Starting Member

34 Posts

Posted - 2010-05-07 : 16:28:56

I get null results ...

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-07 : 16:28:59

My earlier sql statement was on assumption that you may have many start and end time.

In case you will have only 2 records in the table them try the below statement.

Select T.[SortTime] as StartTime, T1.[SortTime] as EndTime,
DateDiff(Mi,T.SortTime, T1.SortTime) as DiffInMinutes
From SORTSTARTEND T, SORTSTARTEND T1
where T.RowIdentity ='START' AND T1.RowIdentity ='END'

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-07 : 16:31:36