I have three tables as shown below:
1. kit_test tk
tk.id tk.first_name tk.last_name tk.dob tk.registered_dt SD007 Aarushi Sharma 2/23/1987 10/5/2020 SD008 Camden Howard 8/22/1993 10/6/2020
2. employee_badged emp
emp.firstname emp.lastname emp.ssno emp.empid Aarushi Sharma 5175 570 Camden Howard 23513 804
3. event_badged events
events.empid events.event_time_utc 570 10/5/2020 570 10/6/2020 570 10/7/2020 570 10/8/2020 804 10/15/2020 804 10/16/2020
This is my desired Output.
regname firstname lastname registered_date enterdate count Aarushi Sharma Aarushi Sharma 10/5/2020 10/5/2020 1 Aarushi Sharma Aarushi Sharma 10/5/2020 10/6/2020 1 Aarushi Sharma Aarushi Sharma 10/5/2020 10/7/2020 1 Aarushi Sharma Aarushi Sharma 10/5/2020 10/8/2020 1 Aarushi Sharma Aarushi Sharma 10/5/2020 10/9/2020 Aarushi Sharma Aarushi Sharma 10/5/2020 10/10/2020 Camden Howard Camden Howard 10/6/2020
This is the query I have so far:
select distinct tk.first_name + ' ' + tk.last_name as "regname", EMP.FIRSTNAME, EMP.LASTNAME,
trunc(tk.registered_dt) as registered_date,
trunc(EVENTS.EVENT_TIME_UTC) as enterdate
FROM kit_test as tk
left join employee_badged emp
ON tk.first_name + ' ' + tk.last_name=emp.FIRSTNAME + ' ' + emp.LASTNAME
left join event_badged events
ON EVENTS.EMPID=EMP.ID
where
date(tk.registered_dt) between '2020-10-05' and '2020-10-10'
and (trunc(EVENTS.EVENT_TIME_UTC) between '2020-10-05' and '2020-10-10') ;
I am performing this task on RedShift/Dbeaver/PostgreSQL.
Advertisement
Answer
I believe you should FIRST left join the 2 events tables, THEN left join the tested employees with the subquery of results.
something like this (i can’t test it so maybe some correction coul be done on this query)
select distinct tk.first_name + ' ' + tk.last_name as "regname", emp_events.FIRSTNAME, emp_events.LASTNAME,
trunc(tk.registered_dt) as registered_date,
trunc(emp_events.EVENT_TIME_UTC) as enterdate
FROM kit_test as tk
left join (
select emp_badges.firstname, emp_badges.lastname events.event_time_utc
from employee_badged emp_badges
left join event_badged events
ON EMP_badges.ID=EVENTS.EMPID
where
(trunc(EVENTS.EVENT_TIME_UTC) between '2020-10-05' and '2020-10-10')
) emp_events
ON tk.first_name + ' ' + tk.last_name=emp_events.FIRSTNAME + ' ' + emp_events.LASTNAME
where
date(tk.registered_dt) between '2020-10-05' and '2020-10-10';
Maybe you can’t do it, but adding the employee_id in the kit_test table would be improving a lot the table and query design