SQL Server: Manejar Time mayor de 24h con función escalar.

Este ejemplo resuelve el post Anterior pero ahora usando una función en vez de un procedimiento, lo que permite llamar a dicha función desde dentro del Select y así poder usar Group By:

CREATE FUNCTION dbo.floatToTimeChar
(
    – Add the parameters for the function here
    @timeAsFloat float
)
RETURNS varchar(14)
AS
BEGIN
    DECLARE @TotTimeVC varchar(14)

    declare @h int;
    declare @m int;
    declare @s int;

    set @h=CAST(@timeAsFloat/3600 as int);
    set @m=CAST( (CAST(@timeAsFloat as Int)%3600)/60 as int);
    set @s=(CAST(@timeAsFloat as Int)%3600)%60;

    declare @mVC varchar(2);
    declare @sVC varchar(2);
    set @mVC=Convert(varchar(2),@m);
    set @sVC=Convert(varchar(2),@s);

    if LEN(@mVC)=1
    begin
        set @mVC=’0′+@mVC;
    end;
    if LEN(@sVC)=1
    begin
        set @sVC=’0′+@sVC;
    end;

    set @totTimeVC=Convert(varchar(8),@h)+’:'+@mVC+’:'+@sVC;    

    – Return the result of the function
    RETURN @TotTimeVC
END

Para usar esa función usamos el siguiente Select:

select
       (Select ST.SportTypeName from SportTypes ST where ST.SportTypeID=Sessions.SportTypeID) as SportName,      
       COUNT(SessionID) as SesionsCant,       
       CAST(SUM(Distance)as decimal(7,2))as TotDist,
       CAST(AVG(Distance)as decimal(7,2)) as AvgDist,
       MAX(Distance) as MaxDist,
                    
       dbo.floatToTimeChar(SUM(CAST(Time As Float)*24*60*60)) As TotTimeCalc,
       dbo.floatToTimeChar(AVG(CAST(Time As Float)*24*60*60)) As AvgTimeCalc,                       
       dbo.floatToTimeChar(MAX(CAST(Time As Float)*24*60*60)) As MaxTimeCalc,                       
                           
       CAST(AVG(CAST(MedHR as float)) as decimal (7,2) )as AvgMedHR,
       MAX(MedHR) as MaxMedHR,
       CAST(AVG(CAST(MaxHR as float)) as decimal (7,2) )as AvgMaxHR,
       MAX(MaxHR) as MaxMaxHR,
       CAST(AVG(CAST(Value as float)) as decimal (7,2) )as AvgValue,
       MAX(Value) as MaxValue,
       
       CONVERT(varchar,MIN(Date),106) as firstDate,
       CONVERT(varchar,MAX(Date),106) as lastDate,
       DATEDIFF (day, CONVERT(varchar,MIN(Date),106), CONVERT(varchar,MAX(Date),106))+1 as days
       
from Sessions
where UserID=@userID
group by SportTypeID;