Link to home
Start Free TrialLog in
Avatar of SimonJohnG
SimonJohnG

asked on

Convert float to hours and minutes in this script

I would like to the diff(float) field to convert to hours and minutes. Am relatively new and don't know how to incorporate this into the below:

SELECT UserId, WorkDate, FirstIn, LunchOut, LunchIn, FinalOut, '07:00' As Roster,
     CONVERT(varchar(5), DATEADD(MINUTE, DATEDIFF(MINUTE, FirstIn, FinalOut) - 
         ISNULL(DATEDIFF(MINUTE,LunchOut, LunchIn), 0), 0), 8) AS Time_Worked,
         CONVERT(float(5), DATEADD(MINUTE, DATEDIFF(MINUTE, FirstIn, FinalOut) - 
         ISNULL(DATEDIFF(MINUTE,LunchOut, LunchIn), 0), 0), 8) AS DIFF
 FROM (
     SELECT 
         UserId, CAST(DATEADD(DAY, DATEDIFF(DAY, 0, [when_adjusted]), 0) AS date) AS WorkDate,
         ---MAX(CASE WHEN INOUT = 2 THEN '00:50' END) AS LunchFlex,
         MIN(CASE WHEN INOUT = 0 THEN [when_adjusted] END) AS FirstIn,
         MAX(CASE WHEN INOUT = 2 THEN [when_adjusted] END) AS LunchOut,
         MAX(CASE WHEN INOUT = 3 THEN [when_adjusted] END) AS LunchIn,
         MAX(CASE WHEN INOUT = 1 THEN [when_adjusted] END) AS FinalOut
     FROM [Safescan].[dbo].[attendant]
     CROSS APPLY (
         SELECT 
             CASE WHEN DATEPART(HOUR, [WHEN]) = 3 THEN DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, 0, [WHEN]) / 15.0) * 15, 0)
                  WHEN DATEPART(HOUR, [WHEN]) = 2 THEN DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, 0, [WHEN]) / 15.0) * 15, 0)
                  ELSE [WHEN]
                 END AS when_adjusted
     ) AS adjust_when
     GROUP BY USERID, DATEADD(DAY, DATEDIFF(DAY, 0, [when_adjusted]), 0)
 ) AS derived
 ORDER BY WorkDate

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial