?
Solved

Convert datediff calculation to decimal

Posted on 2011-05-11
9
Medium Priority
?
482 Views
Last Modified: 2012-05-11
Hi,

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

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

Expert Comment

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

Accepted Solution

by:
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))

Open in new window

0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35736107
It also give in decimal
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

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

The result should be : 0.011
0
 

Author Comment

by:NerishaB
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

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

Expert Comment

by:Krtyknm
ID: 35736651
Hi NerishaB,

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

Thanks,
Karthik
0
 

Author Comment

by:NerishaB
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

Open in new window

0
 

Author Closing Comment

by:NerishaB
ID: 35830200
Thanks, your comment helped.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

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.

Join & Ask a Question