Where to put RIGHT JOIN to get data needed.

Attached (Code 2 below) is code that runs but it's not showing all the records I need.  Because I know Access better I tried to re-create just the table portion of the query in Access (Code 1 below).  When I changed the EmployeeShiftNotes link to a RIGHT JOIN a lot more data was retrieved...in Access.  I don't know how to make the same change to my Reporting Services code.  I tried changing "INNER JOIN"  to "RIGHT JOIN" but it's not enough.  I'm guessing placement of the join in the problem and I don't know how to fix it.
i can really use some help.

Code 1...Portion of code in Access...
SELECT dbo_UserList.UserName, dbo_EmployeeHours.TimeIn
FROM dbo_EmployeeShiftNotes RIGHT JOIN ((dbo_EmployeeList INNER JOIN dbo_UserList ON (dbo_EmployeeList.Company = dbo_UserList.Company) AND (dbo_EmployeeList.ManagerUID = dbo_UserList.UID)) INNER JOIN dbo_EmployeeHours ON (dbo_EmployeeList.EmployeeId = dbo_EmployeeHours.EmployeeId) AND (dbo_EmployeeList.Company = dbo_EmployeeHours.Company)) ON dbo_EmployeeShiftNotes.RecordId = dbo_EmployeeList.RecordId;

Code 2 in Report Services
SELECT 	Supervisor,
	'Lunch' as 'Type',
	EmployeeID, 
	Name, 
	CONVERT(varchar(24), TS_Day,100) as TimeOut, 
	CONVERT(varchar(24), (dateadd(ss,sum(ts_seconds),TS_Day)),100) as TimeIn,
	CASE when  convert(varchar(10), (dateadd(ss,sum(ts_seconds),TS_Day)),101) < '12/08/2011'
		THEN '30+'
	when BreakFlag = 3
		THEN '60'
	when BreakFlag = 2
		THEN '30'
	ELSE '30+' 
	END as 'LunchLength',
	CONVERT(varchar,datediff(hh,0,(dateadd(ss,sum(ts_seconds) ,0))))+ 
		substring((CONVERT(varchar(8),(dateadd(ss,sum(ts_seconds) ,0)),114)),3,3) as HrsMin,
	CASE when Breakflag = 3  --60 minute lunch
	THEN convert(varchar(5), dateadd(minute, -60, dateadd(second, sum(ts_seconds), 0)), 8)
	ELSE convert(varchar(5), dateadd(minute, -30, dateadd(second, sum(ts_seconds), 0)), 8)
	END AS TimeOver,
	ShiftNote
FROM
(
 SELECT	TS_Day, 
	L.EmployeeId, 
	L.LastName + ', ' + L.FirstName AS Name, 
	U.UserName, 
	U.UserName as Supervisor,
        N.ShiftNote,
        times.BreakFlag,
	Sum(TS_Seconds) as ts_Seconds
 FROM
 (
   SELECT company,
	employeeid, 
	recordid,
        convert(datetime,convert(varchar(20),[timeOut],100)) as TS_Day, 
        timein, 
	[timeout], 
	BreakFlag,
        IsNull ( 
      ( SELECT TOP 1 case  
	    WHEN t1.Breakflag =  2 OR t1.BreakFlag = 3 THEN CASE 
	    WHEN datediff(ss,t1.timeout,isnull(t2.timein,t1.timeout)) > 1860 
		 THEN datediff(ss,t1.timeout,isnull(t2.timein,t1.timeout)) 
         else 0 end else 0 end
         
        FROM EmployeeHours t2 
	WHERE t2.employeeid = t1.employeeid 
          AND t2.company=t1.company 
          AND convert(varchar(8),t2.timein) = convert(varchar(8),t1.timein)     -- must be same day
          AND (t2.timein > t1.timein or (t2.timeout > t1.timeout and t2.timein = t1.timein))
        ORDER BY t2.timein asc, t2.timeout asc, recordid asc),0) as ts_seconds
    FROM EmployeeHours t1
  ) times
 
--Current Code..runs but does not show all data
  INNER JOIN EmployeeList L ON (times.EmployeeId = L.EmployeeId) AND (times.Company = L.Company) 
  LEFT JOIN UserList U ON L.ManagerUID = U.UID and L.Company = U.Company
--I changed this from INNER JOIN to RIGHT JOIN
  RIGHT JOIN EmployeeShiftNotes N ON L.RecordId = N.RecordId AND L.EmployeeId = N.EmployeeId
 
  WHERE convert(datetime,convert(char(8),times.TimeIn,112)) between @From and @To
    AND L.Suspend = 0
    AND L.Class in (2,4,6,7)
    AND times.Company=1
    AND U.UserName = @Supervisor
 
  GROUP BY times.employeeid,  
	L.EmployeeId, 
	L.LastName + ', ' + L.FirstName, 
	U.UserName, 
        N.ShiftNote,
	TS_Day, 
	times.recordid,
	times.Breakflag
 ) daily
 
GROUP BY name, 
	employeeid,
	Supervisor, 
	Shiftnote,
	TS_Day, 
	ts_seconds,
	BreakFlag
 
HAVING ((sum(ts_seconds) / 60) >31) AND  ((sum(ts_seconds) / 60) <> 60)

Open in new window

BobRosasAsked:
Who is Participating?
 
LowfatspreadCommented:
please try this ...

ok i've simplified the output ... but you need to explain what the report is intended to show...

are you trying to show total over lunch/break time for a period and show the detail lines as well...

e.g. over the week the employee has exceed daily lunch allowance by ? x minutes
  and here is the breakdown of the offending days....

    (in which case you should also probably include those days and periods where they underused there allowance....
    )

please clarify what the report is intended to show...
;with cte as 
   (select employeeid,company,breakflag,convert(char(8),timein,112) as Theday
          ,timein,timeout
          ,row_number() over (partition by employeeid,company,convert(char(8),timein,112)
                               order by timein) as rn
     from employeehours
   )
,lunch as 
   (select a.employeeid,a.company,a.theday,a.timein,a.timeout,a.breakflag
          ,datediff(seconds,a.timeout,coalesce(b.timein,a.timeout)) as breakseconds
     from cte as a
     left outer join cte as B
       on a.employeeid=b.employeeid
      and a.company=b.company
      and a.theday=b.theday
      and a.rn=b.rn-1
     where a.breakflag in (2,3)
    )
,overlimit as (
   select a.* 
    from lunch as a
    where breakseconds > case breakflag when 2 then 1800 else 3600 end
   )
select  *
 from overlimit 
 where employeeid=3215 and company=1


does this produce the offending rows?


if so then try this 

;with cte as 
   (select employeeid,company,breakflag,convert(char(8),timein,112) as Theday
          ,timein,timeout
          ,row_number() over (partition by employeeid,company,convert(char(8),timein,112)
                               order by timein) as rn
     from employeehours
   )
,lunch as 
   (select a.employeeid,a.company,a.theday,a.timein,a.timeout,a.breakflag
          ,datediff(seconds,a.timeout,coalesce(b.timein,a.timeout)) as breakseconds
     from cte as a
     left outer join cte as B
       on a.employeeid=b.employeeid
      and a.company=b.company
      and a.theday=b.theday
      and a.rn=b.rn-1
     where a.breakflag in (2,3)
    )
,overlimit as (
   select a.* 
    from lunch as a
    where breakseconds > case breakflag when 2 then 1800 else 3600 end
   )
SELECT 	Supervisor,
	'Lunch' as 'Type',
	EmployeeID, 
	Name, 
	TimeOut, 
	TimeIn,
	CASE breakflag
	when  3 THEN '60'
	when  2 THEN '30'
	END as 'LunchLength'
   ,(breakseconds - case breakflag when 2 then 1800 else 3600 end)/60.00
	AS TimeOver
   ,coalesce(ShiftNote,'') as shiftnote
FROM
(
 SELECT	TS_Day, 
	L.EmployeeId, 
	L.LastName + ', ' + L.FirstName AS Name,
	U.UserName as Supervisor,
        N.ShiftNote,
        times.BreakFlag,
	Sum(TS_Seconds) as ts_Seconds
 FROM overlimit as times
  INNER JOIN EmployeeList L 
     ON times.EmployeeId = L.EmployeeId
    AND times.Company = L.Company 
   LEFT OUTER JOIN UserList U 
     ON L.ManagerUID = U.UID 
    and L.Company = U.Company
   LEFT OUTER JOIN EmployeeShiftNotes N 
     ON L.RecordId = N.RecordId 
    AND L.EmployeeId = N.EmployeeId
 
  WHERE convert(datetime,convert(char(8),times.TimeIn,112)) between @From and @To
    AND L.Suspend = 0
    AND L.Class in (2,4,6,7)
    AND times.Company=1
    AND U.UserName = @Supervisor
 
 ) daily
 order by name,employeeid,timein

Open in new window

0
 
LowfatspreadCommented:
please explain in "business english"

what it is you are trying to achieve....

you may need an OUTER JOIN , but until we understand your requirements and the optionality of the data/relationships
we are shooting in the dark...
0
 
BobRosasAuthor Commented:
Thanks for your quick response.  
The problem is that I thought the code was working when I deployed it because the reports showed people who came back late from lunch.  Then I had managers complain because they had late employees who did not show on the report.  I THINK  the problem is that the table EmployeeShiftNotes had the wrong link.  My report appears to be showing people who are late but only if they have a Shift Note.  I need people to show even if they don't have a corresponding Shft Note.  So I need to include all people from EmployeeList and only those records from EmployeeShiftNotes where RecordId is equal.  
I hope this makes it more clear.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
LowfatspreadCommented:
thats more like it....

yes i wanted you to confirm that it was  optional to have a shift note....

could you also confrim which of the other relations are optional or mandatory....

0
 
LowfatspreadCommented:
you need a left join then ....

it normally isn't a good idea to mix LEFT AND RIGHT joins in a query... stick to one format and usually its much easier to understand and maintain the code....

a couple of observations ...
1. so you can have lunch breaks over midnight (ie you don't have evening/night shifts)?

2. if you only have a 30 min break allowance but take 60 minutes your code will exclude them...
      (having <> 3600  9or your original <>60)

3. in general when you don't have to do calculations ... don't  ie whats the point in dividing by 60 when its easy to specify the
    limits in seconds  and very obvious (every knows an hour is 3600 seconds...)


SELECT 	Supervisor,
	'Lunch' as 'Type',
	EmployeeID, 
	Name, 
	CONVERT(varchar(24), TS_Day,100) as TimeOut, 
	CONVERT(varchar(24), (dateadd(ss,sum(ts_seconds),TS_Day)),100) as TimeIn,
	CASE when  convert(varchar(10), (dateadd(ss,sum(ts_seconds),TS_Day)),101) < '12/08/2011'
		THEN '30+'
	when BreakFlag = 3
		THEN '60'
	when BreakFlag = 2
		THEN '30'
	ELSE '30+' 
	END as 'LunchLength',
	CONVERT(varchar(30),datediff(hh,0,(dateadd(ss,sum(ts_seconds) ,0))))+ 
		substring((CONVERT(varchar(8),(dateadd(ss,sum(ts_seconds) ,0)),114)),3,3) as HrsMin,
	convert(varchar(5),dateadd(minute,
                CASE when Breakflag = 3  --60 minute lunch
	            THEN -60
	            ELSE -30
	            END 
                 , dateadd(second, sum(ts_seconds), 0)), 8) AS TimeOver,
	coalesce(ShiftNote,'') as shiftnote
FROM
(
 SELECT	TS_Day, 
	L.EmployeeId, 
	L.LastName + ', ' + L.FirstName AS Name,
	U.UserName as Supervisor,
        N.ShiftNote,
        times.BreakFlag,
	Sum(TS_Seconds) as ts_Seconds
 FROM
 (
   SELECT company,
	employeeid, 
	recordid,
        convert(datetime,convert(varchar(20),[timeOut],100)) as TS_Day, 
        timein, 
	[timeout], 
	BreakFlag,
        IsNull ( 
      ( SELECT TOP 1 case  
	    WHEN t1.Breakflag in (2,3) 
            THEN CASE WHEN datediff(ss,t1.timeout,isnull(t2.timein,t1.timeout)) > 1860 
		      THEN datediff(ss,t1.timeout,isnull(t2.timein,t1.timeout)) 
                      else 0 end 
            else 0 
            end         
        FROM EmployeeHours t2 
	WHERE t2.employeeid = t1.employeeid 
          AND t2.company=t1.company 
          AND convert(varchar(8),t2.timein) = convert(varchar(8),t1.timein)     -- must be same day
          AND (t2.timein > t1.timein 
               or (t2.timeout > t1.timeout and t2.timein = t1.timein))
        ORDER BY t2.timein asc, t2.timeout asc, recordid asc)
      ,0) as ts_seconds
    FROM EmployeeHours t1
  ) times
 
  INNER JOIN EmployeeList L 
     ON (times.EmployeeId = L.EmployeeId) 
    AND (times.Company = L.Company) 
   LEFT OUTER JOIN UserList U 
     ON L.ManagerUID = U.UID 
    and L.Company = U.Company
   LEFT OUTER JOIN EmployeeShiftNotes N 
     ON L.RecordId = N.RecordId 
    AND L.EmployeeId = N.EmployeeId
 
  WHERE convert(datetime,convert(char(8),times.TimeIn,112)) between @From and @To
    AND L.Suspend = 0
    AND L.Class in (2,4,6,7)
    AND times.Company=1
    AND U.UserName = @Supervisor
 
  GROUP BY times.employeeid,  
	L.EmployeeId, 
	L.LastName , L.FirstName, 
	U.UserName, 
        N.ShiftNote,
	TS_Day, 
	times.recordid,
	times.Breakflag
 ) daily
 
GROUP BY name, 
	employeeid,
	Supervisor, 
	Shiftnote,
	TS_Day, 
	ts_seconds,
	BreakFlag 
HAVING sum(ts_seconds) > 1860 
  and sum(ts_seconds) <> 3600

Open in new window

0
 
BobRosasAuthor Commented:
Thanks for all your help and the changes.  You are right...it does make for better code.  Only wish it would have solved my problem.  
I believe all other relations are mandatory.
1. We do not have night shifts.
2. I'm not sure how to fix this one.  I will do more testing.  If user clocks out for 30 minute lunch and comes back after 70 minutes I want them to show as 40 minutes late...not exclude them.  The reason I have this code is because when a user clocked out for a 60 minute lunch and came back in exactly 60 minutes it showed on the report as being Tardy 0 minutes and I dont want that either.
3.  Good point!
At this point I'm just trying to figure out where else my bottle neck is coming from.  I'm still missing users from the reports.
0
 
LowfatspreadCommented:
ok what is the userlist ? why do we left join it?

what does employeehours actually record?
 
what is employeelist ?

can your show us some test data...?

do you have any idea what category of employee/break type is missing?



0
 
BobRosasAuthor Commented:
The userList has the manager for each employee.  The report is run my supervisor and so each supervisor should be able to run a report with a list of only their people who are late.  It could happen that a manager was not assigned but the specific records I'm looking for do show a manager.

Employee hous shows when they clock in and when they clock out and the BreakFlag tells you what type...1 = break, 2 = 30 lunch 3 = 60 lunch
This sample data show EmpId 3215 taking a 32 minute lunch and should show on report but does not...
EmployeeId      Company      TimeIn                           TimeOut                       Rate           BreakFlag
3215      1      12/1/11 8:47 AM      12/1/11 11:30 AM      $0.00       2
3215      1      12/1/11 12:02 PM      12/1/11 3:02 PM      $0.00       1

I'm sorry but I'm not sure I understand what you are asking on your last question. There are only three break types...
1 = break, 2 = 30 lunch 3 = 60 lunch
Is that what you are asking?
0
 
LowfatspreadCommented:
looking at your code again...

why are you aggregating anyway?

 is it valid to have more than 1 lunch break?
 can a person have both a 30minute lunch and a 60minute lunch per day....?

if aggregation is required then remove the ts_seconds from the group by.... as that is probably the cause of your problem...

otherwise the group/sum is not required and the having clause should be some sort of where condition to
eliminate the "valid" lunch breakes..

can you explain , how the system actually is intended to work, as time recording to this level is virtually impossible, and usually dismissed as too much of an admin overhead, too intrusive and not good for general employee morale,well being...
(i.e. where is the concern to ensure people do take there full lunch allotment, do have a break, ....
  how are the actual employee hours records recorded ... an automatic time clock system,  manual entry,...)


0
 
BobRosasAuthor Commented:
Thank you so much for all your input!  I apprreciate all your detailed help and will try to answer your questions.
An employee is only paid for 1 break in a 4 hour period but that doesn't mean they don't take more than one break.  An employee is also only allowed one lunch a day but clocking out may easily have been a missed punch that needs to be accounted for.  The 30 and 60 is intended for 2 different schedules.  Some employees are authorized for 30 minutes, others for 60.  It's the call of the supervisor.  You are correct in saying this requires a lot of overhead but it's what I've been requested to do.

This is actually only a portion of the report.
I UNION 3 querys together.  One for Tardy, one for Breaks and one for lunch.  The results show in a report something like this...
Emp Id   Name          Type    Date           Lunch Length   Total Time    Late/Over   Shift Note
111        Joe Smith     Tardy  12/1/2011                                 0:03
                                   Break  12/2/2011                                 0:21             00:06  
                                   Lunch  12/3/2011         60                    1:03             00:03
It's based per incident, not a weekly basis.

Thank you so much for all your hard work on the code.  It will take me some time to go thru it and try to figure it all out.  Right now I'm desperately trying to keep up with this post while in the middle of another more pressing situation...and I'm off tomorrow.  I really appreciate your help but feel it's only fair to award points and post a related question once I have time to go thru your current code.  I've maxed out points!  You've more than  earned it.  
Thanks again!
0
 
BobRosasAuthor Commented:
Thanks again!
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.

All Courses

From novice to tech pro — start learning today.