Nesting Select statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Nesting Select statement

Author

Topic

Jas
Yak Posting Veteran

51 Posts

Posted - 2011-04-20 : 04:32:50

Hi,

I have a query that returns the total number of hours and employee works, how long an employee works before work starts, and how long they worked after hours. It also returns how many hours an employee works on their day off. Now, I want to get the number of hours worked during their breaks. There is a Break table, that contains the start and end time of a persons break. Not everyone has breaks, for eg, when working on a weekend. I tried to do an outer join, but the result is not correct.

Now, I thought of nesting a select statement like so:

; with cte as (select Emp.Name + ' ' + Emp.Surname As FullName, C.ClockTime, Dir.InOrOut,
SDay.NormalDay, SDay.SBreak,
Convert(varchar,Rules.Default_Shift_End,8) AS DefaultShiftEnd,
Convert(varchar,Rules.Default_Shift_Start,8) AS DefaultShiftStart,
row_no = row_number() over (partition by Emp.Name + ' ' + Emp.Surname order by C.ClockTime)
from Employee Emp INNER JOIN ClockIns C ON Emp.Emp_Id = C.Emp_ID
INNER JOIN Direction Dir ON C.Dir_ID = Dir.Dir_ID
INNER JOIN EmpShift ON Emp.Emp_ID = EmpShift.Employee_ID
INNER JOIN Rules ON Rules.Rules_ID = EmpShift.Rules_ID
INNER JOIN SDay ON SDay.Rules_ID = Rules.Rules_ID
And SRDay.CycleDayno= EmpShift.StartDate_CycleDayNo + (day(RC.ClockTime - EmpShift.EmpSR_StartDate) % ShiftRulesGroup.SRShiftCycle_Days)-1),
cte2 as ( select c1.FullName, ClockIn = c1.ClockTime,
ClockOut = case when c2.Name = 'OUT' then c2.ClockTime
else dateadd(day, datediff(day, 0, c1.ClockTime), 0) + DateAdd(dd, DateDiff(dd, 0, c1.DefaultShiftEnd), 0) end,
DefaultStart = c1.DefaultShiftStart, DefaultEnd = c1.DefaultShiftEnd,
c1.NormalDay, c1.SRBreak
from cte c1 left join cte c2 on c1.row_no = c2.row_no - 1
and c1.FullName = c2.FullName where c1.Name = 'IN')
select FullName, Date = convert(nvarchar(10), ClockIn, 111), NormalDay, SRBreak,
TotalHours_Worked = convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108),
Hours_Worked_BeforeShift = convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn,
case when datediff(hh,0,ClockIn)%24 < datepart(hour, DefaultStart)
AND datediff(hh,0,ClockOut)%24 >= datepart(hour, DefaultStart)
THEN dateadd(hh,(datediff(hh,0,ClockOut)/24)* 24 + datepart(hour, DefaultStart),0) end)), 0), 108),
Hours_DuringBreak = case when SRBreak = 0 then
(Select SRBreak.SR_Break_StartTime From Rules_SRBreak SRBreak) end,Hours_Worked_AfterShift = convert(varchar(10), dateadd(second, sum(datediff(second,
case when DATEDIFF(hh,0,ClockIn)%24 < DatePart(hour, DefaultEnd)
AND DATEDIFF(hh,0,ClockOut)%24 >= DatePart(hour, DefaultEnd)
THEN DATEADD(hh,(DATEDIFF(hh,0,ClockIn)/24)* 24 + DatePart(hour, DefaultEnd),0)
END, ClockOut)), 0), 108),
HoursWorked_OffDay = case when NormalDay = 0 then
convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) end
FROM cte2
GROUP BY FullName, convert(nvarchar(10), ClockIn, 111), NormalDay, SRBreak

But it does not work. Any ideas on how to get this correct?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources