Solved

# Convert datediff calculation to decimal

Posted on 2011-05-11
Medium Priority
482 Views
Hi,

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

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

LVL 15

Expert Comment

ID: 35736092
DATEDIFF(second, start_date, end_date) / 60.0 / 60.0
0

LVL 3

Accepted Solution

Krtyknm earned 1500 total points
ID: 35736104
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))
0

LVL 21

Expert Comment

ID: 35736107
It also give in decimal
0

Author Comment

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

The result should be : 0.011
0

Author Comment

ID: 35736261
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?
0

LVL 9

Expert Comment

ID: 35736373
Can you share your table schema with sample input and sample output?
0

LVL 3

Expert Comment

ID: 35736651
Hi NerishaB,

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

Thanks,
Karthik
0

Author Comment

ID: 35736759
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,
ClockIn,
ClockOut,
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
end,
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
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
0

Author Closing Comment

ID: 35830200
Thanks, your comment helped.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down ā¦
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a paā¦
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
###### Suggested Courses
Course of the Month14 days, 14 hours left to enroll

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

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