Code:
DECLARE @WEDate smalldatetime, @Dist varchar(6)
SET @WEDate = '01/24/2009'
SET @Dist = 'D12'
SELECT *,
TotalTime = SundayTime + MondayTime + TuesdayTime + WednesdayTime + ThursdayTime + FridayTime + SaturdayTime
This is the top of my query only. The sundaytime, mondaytime.. ect values are in seconds. If i simply divide this value by 60, and 60 again I get a rounded whole number. So let's say I get 40, I need it to come back as 40.5 in case there was an extra 30 minutes.
So how would I re-write that to convert it into a whole number with one decimal place rounded to the tenth.
Here is the whole code below...
Code:
DECLARE @WEDate smalldatetime, @Dist varchar(6)
SET @WEDate = '01/24/2009'
SET @Dist = 'D12'
SELECT *,
TotalTime = SundayTime + MondayTime + TuesdayTime + WednesdayTime + ThursdayTime + FridayTime + SaturdayTime
FROM(SELECT HomeLaborLevelName2, PERSONNUM, PersonFullName, HomeLaborLevelName3, HomeLaborLevelName5, HomeLaborLevelDsc5 as Jobtitle,
MAX(CASE WHEN DOW = 'Sunday' THEN Schedule ELSE '' END) AS SundayDate,
MAX(CASE WHEN DOW = 'Sunday' Then ScheduleTime ELSE '' END) AS SundayTime,
MAX(CASE WHEN DOW = 'Monday' THEN Schedule ELSE '' END) AS MondayDate,
MAX(CASE WHEN DOW = 'Monday' Then ScheduleTime ELSE '' END) AS MondayTime,
MAX(CASE WHEN DOW = 'Tuesday' THEN Schedule ELSE '' END) AS TuesdayDate,
MAX(CASE WHEN DOW = 'Tuesday' Then ScheduleTime ELSE '' END) AS TuesdayTime,
MAX(CASE WHEN DOW = 'Wednesday' THEN Schedule ELSE '' END) AS WednesdayDate,
MAX(CASE WHEN DOW = 'Wednesday' Then ScheduleTime ELSE '' END) AS WednesdayTime,
MAX(CASE WHEN DOW = 'Thursday' THEN Schedule ELSE '' END) AS ThursdayDate,
MAX(CASE WHEN DOW = 'Thursday' Then ScheduleTime ELSE '' END) AS ThursdayTime,
MAX(CASE WHEN DOW = 'Friday' THEN Schedule ELSE '' END) AS FridayDate,
MAX(CASE WHEN DOW = 'Friday' Then ScheduleTime ELSE '' END) AS FridayTime,
MAX(CASE WHEN DOW = 'Saturday' THEN Schedule ELSE '' END) AS SaturdayDate,
MAX(CASE WHEN DOW = 'Saturday' Then ScheduleTime ELSE '' END) AS SaturdayTime
FROM
(SELECT CT.HomeLaborLevelName2, CT.PERSONNUM, CT.PersonFullName, CT.HomeLaborLevelName3,CT.HomeLaborLevelName5,CT.HomeLaborLevelDsc5, CALENDARDTM,
DOW=DATENAME(dw, CalendarDTM),
Schedule=CASE WHEN PAYCODEID = '401' THEN '03 VAC' ELSE ISNULL(RIGHT(CONVERT(varchar(20), ShiftStartDate),7)+'-'+
RIGHT(CONVERT(varchar(20), ShiftEndDate),7), 'OFF') END,
ScheduleTime=ISNULL(datediff(second, shiftstartdate, shiftenddate), 0)
FROM
(SELECT HomeLaborLevelName2, PERSONNUM, PersonFullName, HomeLaborLevelName3,HomeLaborLevelName5,HomeLaborLevelDsc5, CALENDARDTM
FROM Calendar,
(SELECT HomeLaborLevelName2, PERSONNUM, PersonFullName, HomeLaborLevelName3,HomeLaborLevelName5,HomeLaborLevelDsc5
FROM VP_Person
WHERE
HomeLaborLevelName2 = @Dist AND EmploymentStatus = 'Active' AND Homelaborlevelname5 IN ('1000','1001','1010','1012','1013','1017','1040', '1042','1045','1100','1105','1113','1126','1136','1185','1194','1209',
'1231','1260','1330','1351','1567','1573','1576','1577','1581', '1582', '1583', '1584','1586', '1587', '1601',
'1681','1708','1709','1889','1890','1898','1900','1901','1911', '1912','1928','2010','2037','2181','2183','2214',
'2266', '2273','2335', '2336', '2337', '2338', '2339','2340','2341', '2342', '2343', '2350','2360','2361', '2362',
'2363','2369', '2370', '2371', '2381', '2382', '2383', '2384')) as PTbl
WHERE CALENDARDTM between DATEADD(dd, -6, @WEDate) and @WEDate) as CT
LEFT OUTER JOIN VP_Schedule
ON SHIFTSTARTDATE between DATEADD(dd, -6, @WEDate) and DATEADD(mi, 1439, @WEDate) AND
CONVERT(datetime, CONVERT(varchar(12), ShiftStartDate, 1)) = CalendarDTM AND
CT.PersonNUM = VP_Schedule.PERSONNUM AND
VP_Schedule.HomeLaborLevelName2 = @Dist) as Sched
GROUP BY PERSONNUM, PersonFullName, HomeLaborLevelName3, HomeLaborLevelName2, HomeLaborLevelName5,HomeLaborLevelDsc5
)
tbl
ORDER BY HomeLaborLevelName3, PersonNum
Thank you ahead of time.