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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.