SQL add this complicated time expression ??

[DOGWATCH]

I have a challenge where I need to add up two time expressions. In Shift1Duration and Shift2Duration the expression will return a value like 8:20 and 7:00.
There is an additional column DurationTotal that must sum these two expression so 8:20 + 7:00 would equal 15:00 or basically 15 hours. The time expresion fields are cast as a varchar not datetime .

here is the expression: The duration column expression needs to be re-written because DurationTotal should equal
ShiftDuration1 + ShiftDuration2. This is one of these problems easier said than done... any ideas how to sum them together?

Shift1Duration=Case When (DateDiff(mi,Cast(StartShift1 as DateTime),Cast(EndShift1 as DateTime))>0) Then 
	IsNull(Convert(Varchar(2),(Cast(DateDiff(mi,Cast(StartShift1 as DateTime),Cast(EndShift1 as DateTime))as int))/60) + ':' +
			Case When Convert(Varchar(2),(Cast(DateDiff(mi,Cast(StartShift1 as DateTime),Cast(EndShift1 as DateTime))as int))%60)<10 Then '0' +
			Convert(Varchar(1),(Cast(DateDiff(mi,Cast(StartShift1 as DateTime),Cast(EndShift1 as DateTime))as int))%60) Else
			Convert(Varchar(2),(Cast(DateDiff(mi,Cast(StartShift1 as DateTime),Cast(EndShift1 as DateTime))as int))%60)End,'0:00')
		Else '0:00' END,


	Shift2Duration=Case When (DateDiff(mi,Cast(StartShift2 as DateTime),Cast(EndShift2 as DateTime))>0) Then 
	IsNull(Convert(Varchar(2),(Cast(DateDiff(mi,Cast(StartShift2 as DateTime),Cast(EndShift2 as DateTime))as int))/60) + ':' +
			Case When Convert(Varchar(2),(Cast(DateDiff(mi,Cast(StartShift2 as DateTime),Cast(EndShift2 as DateTime))as int))%60)<10 Then '0' +
			Convert(Varchar(1),(Cast(DateDiff(mi,Cast(StartShift2 as DateTime),Cast(EndShift2 as DateTime))as int))%60) Else
			Convert(Varchar(2),(Cast(DateDiff(mi,Cast(StartShift2 as DateTime),Cast(EndShift2 as DateTime))as int))%60)End,'0:00')
		Else '0:00'  END,


	DurationTotal= Case When (DateDiff(mi,Cast(StartShift1 as DateTime),Cast(EndShift1 as DateTime))>0) Then 
	IsNull(Convert(Varchar(2),(Cast(DateDiff(mi,Cast(StartShift1 as DateTime),Cast(EndShift1 as DateTime))as int))/60) + ':' +
			Case When Convert(Varchar(2),(Cast(DateDiff(mi,Cast(StartShift1 as DateTime),Cast(EndShift1 as DateTime))as int))%60)<10 Then '0' +
			Convert(Varchar(1),(Cast(DateDiff(mi,Cast(StartShift1 as DateTime),Cast(EndShift1 as DateTime))as int))%60) Else
			Convert(Varchar(2),(Cast(DateDiff(mi,Cast(StartShift1 as DateTime),Cast(EndShift1 as DateTime))as int))%60)End,'0:00')
		Else '0:00' END

 

[donutman]

It's hard to tell what you have as raw data vs what you may be computing. But if you have a ShiftStart and a ShiftEnd that is stored as datetime, then you should have no problems getting to where you want. Life becomes more difficult, however, if you insist on using varchar fields where another choice might be more appropriate.
Sometimes the best way to do math with datetime fields is to convert them to float. It depends on what you want to do.
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[DOGWATCH]

You have the startshift and endshit of shifts one and two stored already. The Shif1Duration expression basically breaks down those times and will calculate the time difference between then.

The challenge is, you must compute Shift1Duration and Shift2Duration separately and then break those experssion back down into minutes, add the minutes together and then rebuild the expression in terms of hours and minutes. Or maybe there is an easier way ??

 

[donutman]

The particular method that you use depends on what's best for the "big picture". You might want to consider creating just "computed" columns: S1DMinutes int, S2DMinutes int, TotalHrs int, TotalMin int. Those would be a piece of cake as they say in my business.
DateDiff(mi, StartShift1, EndShift1) as S1DMinutes,
DateDiff(mi, StartShift2, EndShift2) as S2DMinutes,
(S1DMinutes+S2DMinutes)/60 as TotalHrs,
(S1DMinutes+S2DMinutes)%60 as TotalMin
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[zhavic]

What about this ?:

Shift1Duration = CONVERT( char(5), DATEADD( mi, DATEDIFF( mi, CAST( StartShift1 AS datetime ), CAST( EndShift1 AS datetime ) ), CAST( '' AS datetime ) ), 108 ),
Shift2Duration = CONVERT( char(5), DATEADD( mi, DATEDIFF( mi, CAST( StartShift2 AS datetime ), CAST( EndShift2 AS datetime ) ), CAST( '' AS datetime ) ), 108 ),
DurationTotal  = CONVERT( char(5), DATEADD( mi, DATEDIFF( mi, CAST( StartShift1 AS datetime ), CAST( EndShift1 AS datetime ) ) + DATEDIFF( mi, CAST( StartShift2 AS datetime ), CAST( EndShift2 AS datetime ) ), CAST( '' AS datetime ) ), 108 )

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[DOGWATCH]

that expression just appeared to give NULL result, however it didn't crash.

 

[zhavic]

aha, you have NULLs in that columns EndShift and StartShift ?

So you can simply use ISNULL()

Shift1Duration = CONVERT( char(5), DATEADD( mi, DATEDIFF( mi, CAST( ISNULL( StartShift1, '' ) AS datetime ), CAST( ISNULL( EndShift1, '' ) AS datetime ) ), CAST( '' AS datetime ) ), 108 ),
Shift2Duration = CONVERT( char(5), DATEADD( mi, DATEDIFF( mi, CAST( ISNULL( StartShift2, '' ) AS datetime ), CAST( ISNULL( EndShift2, '' ) AS datetime ) ), CAST( '' AS datetime ) ), 108 ),
DurationTotal  = CONVERT( char(5), DATEADD( mi, DATEDIFF( mi, CAST( ISNULL( StartShift1, '' ) AS datetime ), CAST( ISNULL( EndShift1, '' ) AS datetime ) ) + DATEDIFF( mi, CAST( ISNULL( StartShift2, '' ) AS datetime ), CAST( ISNULL( EndShift2, '' ) AS datetime ) ), CAST( '' AS datetime ) ), 108 )

I tested that SQL, and for example, if StartShift1 = '05:42' and EndShift1 = '9:25' then Shift1Duration will be '03:43'


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[DOGWATCH]

Actually the program requirements have changed considerably but thanks for the help anyway.