Convert datediff calculation to decimal

Posted on 2011-05-11
Last Modified: 2012-05-11

How would I convert this query to return a result in decimal:

After_Shift = datediff(minute, TimeAfterShiftStart, MAX(ClockOut)) / 60.0
Question by:NerishaB
    LVL 15

    Expert Comment

    by:Aaron Shilo
    DATEDIFF(second, start_date, end_date) / 60.0 / 60.0
    LVL 3

    Accepted Solution

    Use CONVERT or CAST to convert it to decimal.
    SELECT CONVERT(DECIMAL(5,1),datediff(minute, GETDATE()-1, GETDATE()) / 60)
    SELECT CAST(datediff(minute, GETDATE()-1, GETDATE()) / 60 AS DECIMAL(5,1))

    Open in new window

    LVL 21

    Expert Comment

    by:Alpesh Patel
    It also give in decimal

    Author Comment

    @ Krtyknm:  The result I am getting is something like : 10582011.001

    The result should be : 0.011

    Author Comment

    What I actually need to do is find a way to use both the sum and the max in the same datediff function, like so:

    TotalWorkHours = CAST(sum(datediff(minute, ClockIn, MAX( ClockOut)) / 60.0) as decimal(4,2)),

    I get the following error though:
    "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

    Any help?
    LVL 9

    Expert Comment

    Can you share your table schema with sample input and sample output?
    LVL 3

    Expert Comment

    Hi NerishaB,

    Can you explain brefily about your query like what are the fields and their datatype.


    Author Comment

    The query is very large, see attached
    ; with emp_clock as
    Select 	Personel_ID = Pers.Personel_ID,
    FullName	= Pers.[Name] + ' ' + Pers.Surname,
    RowNo		= row_number() over (partition by Pers.Personel_ID order by RC.ClockTime),
    ClockDate = dateadd(day, datediff(day, 0, RC.ClockTime), 0),
    DayAdj = case when Default_Shift_End < Default_Shift_Start then 1 else 0 end,
    ClockTime	= RC.ClockTime, 
    InOrOut	= Dir.[Name],
    ShiftName	= ShiftRules.[Name],
    ShiftStartTime	= convert(varchar(10), ShiftRules.Default_Shift_Start, 108),		
    ShiftEndTime	= convert(varchar(10), ShiftRules.Default_Shift_End, 108),			
    ShiftStart= dateadd(day, datediff(day, 0, RC.ClockTime), 0) + ShiftRules.Default_Shift_Start,
    ShiftEnd	= dateadd(day, datediff(day, 0, RC.ClockTime), 0) + ShiftRules.Default_Shift_End + case when ShiftRules.Default_Shift_End < ShiftRules.Default_Shift_Start then 1 else 0 end, BreakName= SRBreak.[Name], BreakStartTime	= convert(varchar(10), SRBreak.SR_Break_StartTime, 108),
    BreakEndTime = convert(varchar(10), SRBreak.SR_Break_EndTime, 108), BreakStart	= dateadd(day, datediff(day, 0, RC.ClockTime), 0) + SRBreak.SR_Break_StartTime,
    BreakEnd	= dateadd(day, datediff(day, 0, RC.ClockTime), 0) + SRBreak.SR_Break_EndTime+ case when SRBreak.SR_Break_EndTime < SRBreak.SR_Break_StartTime then 1 else 0 end
    FROM 	Personnel Pers 
    INNER JOIN RawClocks RC ON	Pers.Personel_ID = RC.Person_ID
    INNER JOIN Direction Dir 	ON RC.Direction_ID	= Dir.Direction_ID
    INNER JOIN Emp_ShiftGroup EmpShift ON Pers.Personel_ID = EmpShift.Employee_ID
    INNER JOIN Rules_ShiftRulesGroup ShiftRules	ON ShiftRules.ShiftRulesGroup_ID = EmpShift.ShiftRulesGroup_ID
    INNER JOIN Rules_SRDay SRDay ON ShiftRules.ShiftRulesGroup_ID = SRDay.ShiftRulesGroup_ID And srday.cycledayno=  EmpShift.StartDate_CycleDayNo + (day(RC.ClockTime - EmpShift.EmpSR_StartDate)  % ShiftRules.SRShiftCycle_Days)-1 
    LEFT OUTER JOIN Rules_SRBreak SRBreak ON SRBreak.SRDay_ID = SRDay.SRDay_ID
    timelog as 
     select Personel_ID = coalesce(ci.Personel_ID, co.Personel_ID),
     FullName	= coalesce(ci.FullName, 	co.FullName),
     ClockIn= coalesce(ci.ClockTime, 	co.ClockDate - co.DayAdj + co.ShiftStartTime),
    ClockOut	= coalesce(co.ClockTime, 	ci.ClockDate + ci.DayAdj + ci.ShiftEndTime),
    ShiftName	= coalesce(ci.ShiftName, 	co.ShiftName),
    ShiftStartTime	= coalesce(ci.ShiftStartTime,co.ShiftStartTime),
    ShiftEndTime	= coalesce(ci.ShiftEndTime, co.ShiftEndTime),
    ShiftStart	= coalesce(ci.ShiftStart,	co.ShiftStart),
    ShiftEnd	= coalesce(ci.ShiftEnd,		co.ShiftEnd),
    BreakName	= coalesce(ci.BreakName,	co.BreakName),
    BreakStartTime = coalesce(ci.BreakStartTime,	co.BreakStartTime),
    BreakEndTime = coalesce(ci.BreakEndTime,	co.BreakEndTime),
    BreakStart= coalesce(ci.BreakStart, 	co.BreakStart),
    BreakEnd	= coalesce(ci.BreakEnd, 	co.BreakEnd)
    from	emp_clock ci
    full outer join emp_clock con ci.Personel_ID	= co.Personel_ID
    and ci.RowNo = co.RowNo - 1 and co.ClockTime	< dateadd(day, 1, ci.ClockDate) + ci.ShiftStartTime
    where not (ci.InOrOut is null and co.InOrOut	= 'IN')
    and not (ci.InOrOut = 'OUT'	and	co.InOrOut	is null)
    and (ci.InOrOut = 'IN' or	ci.InOrOut is null)
    detail as
    	select	Personel_ID, FullName, 
    		TimeBeforeShiftStart	= case 	when ClockIn < ShiftStart then ClockIn end,
    TimeBeforeShiftEnd	= case 	when ClockIn < ShiftStart then ShiftStartTime end,
    TimeAfterShiftStart	= case 	when ClockOut > ShiftEnd
    then ShiftEnd end,
    TimeAfterShiftEnd	= case 	when ClockOut > ShiftEnd
    then ClockOut
    TimeBreakStart = case when ClockIn 	between BreakStart and BreakEnd or ClockOut between BreakStart and 	BreakEnd
    then case when ClockIn > BreakStart then ClockIn else BreakStart end end,
    TimeBreakEnd = case when ClockIn 	between BreakStart and BreakEnd or   ClockOut 	between BreakStart and 	BreakEnd
    then case when ClockOut < BreakEnd then ClockOut else BreakEnd end
    from	timelog
    summary as
    	select	Personel_ID, 
    		ClockDate	= dateadd(day, datediff(day, 0, ClockIn), 0),
    		WorkHours	= sum(datediff(minute, ClockIn, ClockOut) / 60.0),
    		BefShift	= cast(isnull(sum(datediff(minute, TimeBeforeShiftStart, TimeBeforeShiftEnd)), 0) / 60.0 as decimal(10,2)),
    		AfterShift = isnull(sum(datediff(minute, TimeAfterShiftStart, TimeAfterShiftEnd)), 0) / 60.0,
    		WorkDuringBreak	= isnull(sum(datediff(minute, TimeBreakStart, TimeBreakEnd) / 60.0), 0)
    	from	detail
    	group by Personel_ID, dateadd(day, datediff(day, 0, ClockIn), 0)
    select	*
    from    summary
    order by Personel_ID

    Open in new window


    Author Closing Comment

    Thanks, your comment helped.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now