• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 490
  • Last Modified:

Convert datediff calculation to decimal

Hi,

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

After_Shift = datediff(minute, TimeAfterShiftStart, MAX(ClockOut)) / 60.0
0
NerishaB
Asked:
NerishaB
1 Solution
 
Aaron ShiloChief Database ArchitectCommented:
DATEDIFF(second, start_date, end_date) / 60.0 / 60.0
0
 
KrtyknmCommented:
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

0
 
Alpesh PatelAssistant ConsultantCommented:
It also give in decimal
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

The result should be : 0.011
0
 
NerishaBAuthor Commented:
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
 
anillucky31Commented:
Can you share your table schema with sample input and sample output?
0
 
KrtyknmCommented:
Hi NerishaB,

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

Thanks,
Karthik
0
 
NerishaBAuthor Commented:
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

Open in new window

0
 
NerishaBAuthor Commented:
Thanks, your comment helped.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now