Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Simple conversion of value to one decimal place rounded to tenth

Status
Not open for further replies.

Jdbenike

MIS
Sep 11, 2008
74
US
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.
 
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:
Code:
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.
 
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 ??
 
You can't really produce a resultset with different data types in the same column. You could conditionally format the value within Reporting Services.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top