Joining table result to query - 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

Joining table result to query

Author

Topic

Jas
Yak Posting Veteran

51 Posts

Posted - 2011-04-18 : 08:15:37

Hi,

I am joining 5 tables together. They are descibed like this:

The Employee Table is all th details about an employee
The ClockIns table is the data received from the access control device, to tell us when the employee clocked in and out of the building
The Direction table is the direction whether the employee clocked in or out.
The EmpRules table joins the Rules to the Employee table
The rules table is what sets the type of shift it is, ie, night shift or day shift.
The Day table tells you what each day is, ie Monday to Sunday. There is a field on the Day table that is called "Normal". This it set to true for all the days except Saturday or Sunday. Now, the query works, but the result is displayed like this:

FullName ClockTime Normal Hours_Worked
Person A 2011/04/01 1 08:52:14
Person A 2011/04/02 1 06:20:00
Person A 2011/04/03 1 06:00:00

Now, the query should not display a 1 for the 2nd and 3rd, it should be a 0. Here is the query. What am I doing wrong here?

; with cte as (select Emp.Name + ' ' + Emp.Surname As FullName, Clock.ClockTime, Dir.InOrOut, Day.Normal,
row_no = row_number() over (partition by Emp.Name + ' ' + Emp.Surname order by Clock.ClockTime)
from Employee Emp INNER JOIN ClockIns Clock ON Emp.Emp_Id = Clock.Emp_ID
INNER JOIN Direction Dir ON Clock.Dir_ID = Dir.Dir_ID
INNER JOIN Emp_Rules EmpRules ON Emp.Emp_ID = EmpRules.Employee_ID
INNER JOIN Rules ON Rules.Rules_ID = EmpRules.Rules_ID
INNER JOIN Day Day ON Rules.Rules_ID = SRDay.Rules_ID),
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) + '17:00:00' end,
c1.Normal
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 = dateadd(day, datediff(day, 0, ClockIn), 0),
Hours_Worked = convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108),
NormalDayY_N = NormalDay
FROM cte2
where Normal = 'false'
GROUP BY FullName, dateadd(day, datediff(day, 0, ClockIn), 0), NormalDay

Jas
Yak Posting Veteran

51 Posts

Posted - 2011-04-18 : 09:22:28

What I want, is to join this query :

select Emp.Name + ' ' + Emp.Surname As FullName, Day.Name, Day.NormalDay
from Employee Emp, EmpRules, RulesGroup,
Day, ClockIns C
WHERE Emp.Emp_Id = EmpRules.Employee_ID
AND Emp.Emp_ID = C.Emp_ID
AND EmpRules.RulesGroup_Id = RulesGroup.RulesGroup_Id
AND Day.RulesGroup_Id = RulesGroup.RulesGroup_Id
AND SRDay.NormalDay = 0
Group by Emp.Name + ' ' + Emp.Surname, Day.Name, Day.NormalDay

With this query:

; with cte as (select Emp.Name + ' ' + Emp.Surname As FullName, Clock.ClockTime, Dir.InOrOut, Day.Normal,
row_no = row_number() over (partition by Emp.Name + ' ' + Emp.Surname order by Clock.ClockTime)
from Employee Emp INNER JOIN ClockIns Clock ON Emp.Emp_Id = Clock.Emp_ID
INNER JOIN Direction Dir ON Clock.Dir_ID = Dir.Dir_ID
INNER JOIN Emp_Rules EmpRules ON Emp.Emp_ID = EmpRules.Employee_ID
INNER JOIN Rules ON Rules.Rules_ID = EmpRules.Rules_ID
INNER JOIN Day Day ON Rules.Rules_ID = SRDay.Rules_ID),
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,
c1.Normal
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 = dateadd(day, datediff(day, 0, ClockIn), 0),
Hours_Worked = convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108),
NormalDayY_N = NormalDay
FROM cte2
where Normal = 'false'
GROUP BY FullName, dateadd(day, datediff(day, 0, ClockIn), 0), Norma

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources