Calculating Time Variance (HELP!!) 3

[talenx]

Hello all,
This is more then likely very simple and I’m just overlooking it. But I have been racking my head on this for a couple of days now, and I haven't been able to come up with a viable solution.
I have a start time and an end time with in the same record.
Here is the dataset
ID starttime endtime
------------------------------------------------------ ------------------------------------------------------
1 2004-11-18 04:33:00.000 2004-11-18 14:33:00.000
2 2004-11-18 04:00:00.000 2004-11-18 14:00:00.000
3 2004-11-18 04:00:00.000 2004-11-18 14:00:00.000
4 2004-11-17 04:23:00.000 2004-11-17 14:43:00.000
5 2004-11-16 07:45:00.000 2004-11-16 16:37:00.000
6 2004-11-16 05:56:00.000 2004-11-16 15:45:00.000
---
End dataset
I need to be able to calculate the time difference between the start time and the end time

Example
Start time = 5:30 am
End time = 3:00 pm
Difference = 9:30 hours

I attempted to use the DATEDIFF function but it would always round up to the nearest hour, never showing the minutes

select datediff(mi,starttime,endtime)/60 from Fact_tRouteDetail

--RESULT SET 
ID	Variance
1	10
2	10
3	10
4	10
5	8
6	9

Any thoughts

Thanks
Talenx

 

[ESquared]

Answering two questions in one thread... yuck!

I did it in one query a few months ago. I would probably do it completely differently, now. Some of it looks awful.

SELECT
   OrderNumber,
   StartDate,
   Mo,
   Shop,
   BusinessHoursDifference =
      (case
         WHEN DatePart(dw,d1) IN (1,7) THEN 0
         ELSE dbo.MyMin(Convert(float,d2), Convert(int,Convert(float,d1))+.5) - dbo.MyMin(Convert(float,d1),Convert(int,Convert(float,d1))+.5)
         END 
      + dbo.MyMax(DateDiff(d,d1,d2)-1,0)*.5 - dbo.MyMax(0,DateDiff(ww,d1+1, d2))*.5 -dbo.MyMax(0,DateDiff(ww,d1, d2-1))*.5
      + case
         WHEN DateDiff(d,d1,d2)<=0 OR DatePart(dw,d2) IN (1,7) THEN 0
         ELSE dbo.MyMin(Convert(float,d2), Convert(int,Convert(float,d2))+.5) - Convert(int,Convert(float,d2))
         END
      ) * 24
FROM (
   SELECT
      OrderNumber,
      StartDate,
      Mo = dbo.MonthDate(StartDate),
      d1 = StartDate - .25,
      d2 = EndDate - .25
      FROM MyTable
) A

This query is based on 6am-6pm business hours. Thus the -.25 to simplify calculations (making times 12am - 12pm).

 

[donutman]

I think I interpreted the question differently. My total time included hours outside of business hours. For example if the ticket was opened Wed at 3pm and closed at 9am on Thur, that was 18 hours not 3 hours. If a ticket was opened Wed at 6pm and closed at Thur 9am, then I count that as 1 hour. Not really clear what was asked for.
You didn't post your functions and I just stuffed the entire calculation into a function.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Karl, as you guessed, the functions just return the max or min of two values, similar to the Excel worksheet functions. It made things much simpler to be able to use them.

ghdavis, if you want a custom solution for your requirements, you'll need to post in a new thread.

 

[fireant]

ESquared,

I hope I'm not deviating from the thread. I'm not very good at SQL and also need help. I also am having issues with converting to the data type I need. I've used datediff as follows:


select
sec=convert(varchar(8),datediff(ss,[date open],getdate()),108)
from _smdba_._telmaste_
where sequence=5328925

The above statement returned the result set sec=615877.

I'd like to convert that to the format "1d 06:24:36" but then I'm not sure how to use your case statement

SELECT
Elapsed=
CASE WHEN endtime-starttime >= 1 THEN Convert(varchar(6), Convert(int,endtime-starttime))+'d+' ELSE '' END
+ Convert(varchar(8),endtime-starttime,108)

in conjunction with my statement above. Would you be able to help?

Thanks,

Louie C.

 

[mrdenny]

This should do the trick for you.

/*Declare your variabled*/
declare @sec int
declare @TimeLength datetime
declare @FormattedDate varchar(25)

/*Get the number of seconds*/
select
@sec=convert(varchar(8),datediff(ss,[date open],getdate()),108)
from _smdba_._telmaste_ 
where sequence=5328925

/*Get that into date time formatt using 1/1/1900 as the base date*/
set @TimeLength = '1/1/1900'
set @TimeLength = dateadd(ss, @TimeLength, @sec)
set @FormattedDate = ''

/*If more than one day set the @FormattedDate string as needed.*/
if datediff(dd, '1/1/1900', @TimeLength) <> 0
	set @FormattedDate = convert(varchar(3), datediff(dd, '1/1/1900', @TimeLength)) + 'd '

/*Set the Formatted Date with the hours, minutes and seconds*/
set @FormattedDate = @FormattedDate + convert(varchar(10), @TimeLength, 108)

/*Return the @FormattedDate variable with the correct info*/
select @FormattedDate

This could probably be done within a single select statement, but this breakdown shows all the steps that need to be done.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[ESquared]

DECLARE @s int
SET @s = 615877
SELECT
  DHMS = convert(varchar(5), @s / 86400) + 'd '
  + Right('0' + convert(varchar(2), (@s / 3600) % 24), 2) + ':'
  + Right('0' + convert(varchar(2), (@s / 60) % 60), 2) + ':'
  + Right('0' + convert(varchar(2), @s % 60), 2)

 

[mrdenny]

ESquared, now your just showing off.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[ESquared]

What? What? I just answered the question in the most straightforward way I knew how!

 

[donutman]

That was the best you could do, ESquared?

[Blue]DECLARE[/Blue] @s [Blue]int[/Blue]
[Blue]SET[/Blue] @s [Gray]=[/Gray] 615877
[Blue]SELECT[/Blue]
  DHMS [Gray]=[/Gray] [Fuchsia]Convert[/Fuchsia][Gray]([/Gray][Blue]varchar[/Blue][Gray]([/Gray]5[Gray])[/Gray][Gray],[/Gray] @s / 86400[Gray])[/Gray] [Gray]+[/Gray] [red]'d '[/red]
  [Gray]+[/Gray] [Fuchsia]Convert[/Fuchsia][Gray]([/Gray][Blue]varchar[/Blue][Gray]([/Gray]30[Gray])[/Gray][Gray],[/Gray][Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]ss[Gray],[/Gray]@s[Gray],[/Gray]0[Gray])[/Gray][Gray],[/Gray]108[Gray])[/Gray]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Do I ever do less than my best?

 

[donutman]

Only until you do better...which is quite often!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Competing with myself is almost as much fun as competing with other people!

 

[fireant]

Thanks for the input guys! That was fast! You guys are great! I modified donutman's code (Esquared, mrdenny: don't be offended, it's just the shortest ) to suit my needs, seen below.

DECLARE @sec as int
select
@sec=datediff(ss,[date open],getdate())
from _smdba_._telmaste_ 
where sequence=5328925
select
DHMS=Convert(varchar(5),@sec / 86400) + 'd '
  + Convert(varchar(30),DateAdd(ss,@sec,0),108)

w/c worked beautifully. I then modified it more into a function:

CREATE FUNCTION [dbo].[fn_CalcDuration] (@start datetime,@end datetime)  
RETURNS VARCHAR as
BEGIN
DECLARE @sec as int
DECLARE @DHMS as varchar
select
@sec=datediff(ss,@start,@end)
select @DHMS=Convert(varchar(5),@sec / 86400) + 'd '
  + Convert(varchar(30),DateAdd(ss,@sec,0),108)
RETURN @DHMS
END

The function was parsed and ran successfully. I then used below to try it on the records.

select *,dbo.fn_CalcDuration(hd_ccdt01,hd_ccdt02) as duration from _smdba_._telmaste_

But then when I looked at the duration column, it's not giving out the supposed output. It's just rounding it off to the day. For less than a day, the output is zero.

I'm thinking it may be the datetime thing in the variable declaration at the function heading for @start and @end but I'm not sure.

What am I doing wrong?

Thanks,

Louie C.

 

[gmmastros]

I looked in to this a little and found your problem. In your UDF, you have the line...

DECLARE @DHMS as varchar

Change it to....

DECLARE @DHMS as varchar(30)

 

[gmmastros]

Sorry. I posted too soon. Try this....

Alter FUNCTION [dbo].[fn_CalcDuration] (@start datetime,@end datetime)
RETURNS VARCHAR(30) as
BEGIN
DECLARE @sec as int
DECLARE @DHMS as varchar(30)
select
@sec=datediff(ss,@start,@end)
select @DHMS=Convert(varchar(5),@sec / 86400) + 'd ' + Convert(varchar(30),DateAdd(ss,@Sec - @sec / 86400,0),108)
RETURN @DHMS
END

 

[fireant]

Hey GM,
That worked beautifully! But what's it for? Is it just to specify the length of the field?

Thanks guys!

Louie C.

 

[donutman]

Fireant, by putting the code into a function you will take a performance hit of at least 20%. The code is so succinct that there isn't much reason to do so.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[gmmastros]

fireant, I honestly don't know why it didn't work when you did NOT specify the length of the varchar variable. It's good practice to ALWAYS specify the length of a varchar variable. It's habit for me.

There are others in here that probably know the *real* answer to your question. Truth is... I had a bit of insomnia last night, saw your post, and decided it would be more fun to work on that than watch late night tv.

 

[ESquared]

The default length for character fields is (I think) 20 characters. So saying

varchar

is equivalent to

varchar(20)

 

[gmmastros]

I suppose this is getting a little side tracked...

Declare @Test VarChar
Set @Test = 'abcdefghijklmnopqrstuvwxyz'
Select @Test

returns.....

(No column name)
a

Looks like the default length of a varchar is 1 character. But when you use it in the Convert function, the behavior is a little different.

Select Convert(varchar, GetDate())
Select Convert(VarChar(6), GetDate())

The first one returns the entire date, but the second one only returns the first 6 characters of the date. So, inside the function, it must be treating an *unsized* varchar as something other than 1.

I suppose the moral of this story is to *always* specify the length of a varchar variable.