Issue with Joins

[tmumaw]

Good morning everyone,

Got a question.  I have a table with all my employees by day in it, I also have another table with employees time.  In the employee's time table I might have only 10 entries for an office which has 13 employees.  All 13 employees are in my employee by day.  I'm trying to join the 2 so I can see all employees with and without time.  Any ideas?  Here is my script and tables.  What I would like to see is list of employees by day ones with time and ones without time.

Thanks

all my employees:
LOAD
ENo,
"Date",
EmpKey
FROM [lib://QVD/HR/EmpDaily.QVD] (qvd)
;

employees time:
Load
Pers.No._PERNR.CATSDB & Num(Date_WORKDATE.CATSDB) as EmpKey,
Pers.No._PERNR.CATSDB as ENo,
Right("Send. CCtr_SKOSTL.CATSDB",6) as [Profit Center],
Date_WORKDATE.CATSDB,
Num(Date_WORKDATE.CATSDB) as NumTimeDate,
Hours_CATSHOURS.CATSDB,
"A/A type_AWART.CATSDB",
1 as HasTime
FROM [lib://QVD/Billing/CATS/CATSDB.QVD]
(qvd)
where "A/A type_AWART.CATSDB" ='0099' or
"A/A type_AWART.CATSDB" ='0069' or
"A/A type_AWART.CATSDB" ='0068'
;

[edwin]

you are obviously joining by Eno and EmpKey and it looks like a simple join (guessing employees table contains all the manufactured dates or is this a different project?)

what are you observing though?  are they not joining properly?  are you sure the Eno/EmpKey value combination is present in both tables?

[edwin]

shouldnt your join just be Eno and Date?  obviously, employees time table has date time but you should get just the date component and use that in the join

[edwin]

take a look at this and hope it gives you an idea

[tmumaw]

Thanks Edwin for all your help.  This is pretty close to what I ended up doing.  I created a concatenated key made up of empno and a numeric date on both tables and did a left join on the time table.  Then read it in as a resident and where the length of hours worked was greater than zero I used hours worked otherwise I made it zero.

[edwin · Accepted solution]

and you just needed to know what days there are missing entries rt?

[tmumaw]

Yep....Thanks for your help again.