Simple conversion of value to one decimal place rounded to tenth

[Jdbenike]

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...

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.

 

[RiverGuy]

In SQL Server, if you divide and Integer by an Integer, you get an Integer. So to change that behavior, make one of the values a decimal.

For example:

SELECT 1100 / 60 -- returns 18
SELECT 1100 / CONVERT(DECIMAL(18,1), 60) -- returns 18.33333333

You can then format the value in your report for one decimal place.

 

[Jdbenike]

What about if I don't want to return that .0 at the end of the value?? What if I only want to return that value if it's needed ??

 

[RiverGuy]

You can't really produce a resultset with different data types in the same column. You could conditionally format the value within Reporting Services.