Short time substraction - 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 2005 Forums

Transact-SQL (2005)

Short time substraction

Author

Topic

ArtoumLaFounda
Starting Member

4 Posts

Posted - 2008-12-30 : 14:50:06

Hello,

I'm converting a Microsoft Access Database to SQL Server 2005. I'm having a problem with a query that calculates the flow of an effluent. Once a day technicians note the counter reading and also note the time at which the reading was took. After data are enter in the database.

Effluent flow is calculate this way:

DeltaReading = (ReadingDay2 - ReadingDay1) = (300 - 200) = 100

DeltaTime = (TimeDay2 - TimeDay1) = (9:15 - 9:00) = 15
= (TimeDay2 - TimeDay1) = (9:00 - 9:15) = -15

Flow = (DeltaReading - DeltaReading*(DeltaTime)/1440)
= 100 - 100*(15)/1440 = 98.96
= 100 - 100*(-15)/1440 = 101.04

My problem is I cant figure how to create the DeltaTime function in Transact-SQL. In Access TimeDay is a DateTime field set to short time and DeltaTime is calculate this way :

DeltaTime = (TimeDay2 - TimeDay1)/0.000694444
= (9:00 - 9:15)/0.000694444 = -15

Help would be really appreciate.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 15:02:46

[code]select t.TimeDay,(t.TimeDay-COALESCE(t1.TimeDay,0))/0.000694444 AS DeltaTime
FROM YourTable t
OUTER APPLY (SELECT TOP 1 TimeDay
FROM YourTable
WHERE datefield<t.datefield
ORDER BY datefield DESC)t1
[/code]

assuming you've datefield in table to determine the order

ArtoumLaFounda
Starting Member

4 Posts

Posted - 2008-12-30 : 15:54:59

Implicit conversion from data type datetime to int is not allowed.

In Access I was able to subtract 2 TIME datatype and obtain the result as integer...as I mentioned (9:00AM - 9:15AM)/0.000694444= -15
or (2:00PM - 11:00AM)/0.000694444 = 180

SQL Server supports only DATE + TIME datatype...so when I exported my Access TIME datatype fields...
SQL Server automatically filled the DATE part for me so 9:00AM(TIME) in Access is now 30/12/1899 9:00AM(DATE + TIME) in SQL Server

I need a function to get (30/12/1899 9:00AM - 30/12/1899 9:15AM) = -15

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-12-30 : 16:15:49

quote:
Originally posted by ArtoumLaFounda
...I need a function to get (30/12/1899 9:00AM - 30/12/1899 9:15AM) = -15

select Diff_Minutes = datediff(minute,'1899-12-30 09:00','1899-12-30 09:15')


Results:

Diff_Minutes 
------------ 
15

(1 row(s) affected)

CODO ERGO SUM

ArtoumLaFounda
Starting Member

4 Posts

Posted - 2008-12-30 : 19:19:30

Exactly what I was looking for.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 02:53:51

quote:
Originally posted by ArtoumLaFounda

Implicit conversion from data type datetime to int is not allowed.

In Access I was able to subtract 2 TIME datatype and obtain the result as integer...as I mentioned (9:00AM - 9:15AM)/0.000694444= -15
or (2:00PM - 11:00AM)/0.000694444 = 180

SQL Server supports only DATE + TIME datatype...so when I exported my Access TIME datatype fields...
SQL Server automatically filled the DATE part for me so 9:00AM(TIME) in Access is now 30/12/1899 9:00AM(DATE + TIME) in SQL Server

I need a function to get (30/12/1899 9:00AM - 30/12/1899 9:15AM) = -15

ah...so they were datetime fields...i thought there were of numeric data type like decimal,numeric,..

ArtoumLaFounda
Starting Member

4 Posts

Posted - 2008-12-31 : 12:16:48

Sorry I should have post only "I need a function to get (30/12/1899 9:00AM - 30/12/1899 9:15AM) = -15"

Thanks a lot you made my day...here's my final T-SQL Functions :

CREATE FUNCTION getTimeOnly(@DateTime DateTime)
RETURNS datetime
AS
BEGIN
RETURN dateadd(day, -datediff(day, 0, @datetime), @datetime)
END
GO

CREATE FUNCTION getDeltaTime (@time1 datetime, @time2 datetime)
RETURNS int
AS
BEGIN
DECLARE @delta int
SET @delta = datediff(minute,dbo.getTimeOnly(@time1),dbo.getTimeOnly(@time2))
RETURN @delta
END
GO

CREATE FUNCTION getFlowDE (@jour datetime)
RETURNS int
AS
BEGIN
DECLARE @read2 int
SELECT @read2 = DE_int FROM tblEffluents WHERE Jour = @jour
DECLARE @read1 int
SELECT @read1 = DE_int FROM tblEffluents WHERE Jour = (@jour-1)
DECLARE @deltaRead int
SET @deltaRead = @read2 - @read1
IF(@deltaRead<0)
SET @deltaRead = (@deltaRead + 100000000)

DECLARE @time2 datetime
SELECT @time2 = DE_heure FROM tblEffluents WHERE Jour=@jour
DECLARE @time1 datetime
SELECT @time1 = DE_heure FROM tblEffluents WHERE Jour=(@jour-1)
DECLARE @deltaTime int
SET @deltaTime = dbo.getDeltaTime(@time1, @time2)

DECLARE @flowDE int
SET @flowDE = (@deltaRead - @deltaRead*@deltaTime/1440)
RETURN @flowDE

END
GO

Happy New Year and thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources