קוד:
SELECT PersID ,
CASE
WHEN ((DATEDIFF( MINUTE , Beg_Time, End_Time) % 60) < 0 )
THEN
CASE WHEN ( LEN(DATEDIFF( MINUTE , Beg_Time, End_Time) % 60 ) < 3 )
THEN CAST( DATEDIFF(MINUTE,Beg_Time ,End_Time)/60 as varchar)+ ':0' + cast(abs( (DATEDIFF( MI , Beg_Time ,End_Time )) % 60) as varchar(100) ) + ' Hrs'
ELSE CAST( DATEDIFF(MINUTE,Beg_Time ,End_Time)/60 as varchar)+ ':' + cast(abs(( DATEDIFF( MI , Beg_Time ,End_Time )) % 60 ) as varchar(100) ) + ' Hrs'
END
ELSE
CASE WHEN ( LEN(DATEDIFF( MINUTE , Beg_Time, End_Time) % 60 ) < 2 )
THEN CAST( datediff(MINUTE,Beg_Time ,End_Time)/60 as varchar) + ':0' + cast( ( DATEDIFF( MI , Beg_Time, End_Time)) % 60 as varchar(100) ) + ' Hrs'
ELSE CAST( datediff(MINUTE,Beg_Time ,End_Time)/60 as varchar) + ':' + cast( ( DATEDIFF( MI , Beg_Time, End_Time)) % 60 as varchar(100) ) + ' Hrs'
END
END
AS TtlHrs
FROM sumhrs