Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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'
;
and you just needed to know what days there are missing entries rt?
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?
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
take a look at this and hope it gives you an idea
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.
and you just needed to know what days there are missing entries rt?
Yep....Thanks for your help again.