select * from
(Select koms.,b. from koms
left join pathfinder b
on koms."CALL_START_TIME"::DATE = B."pf_start_cst"::date
and koms."ORIG_CORP_HOUSE_CUST" = b."acct_nbr"
where "CALL_START_TIME"::date=date'7/24/19'
and "REGION" in ('Alexandria','Arkansas','Central','East Texas','Eureka','Greater Oklahoma',
'North Carolina','West Texas','West Virginia') and "MEDIA_NAME"= 'Voice'
and "INBOUND_OUTBOUND_CALL_FLAG"='Inbound')t
left join roster r
on t."AGENT_NETWORK_ID" = r."NetworkID"
or T."tsr_empl_id" =r."PeopleSoftID"
or t."EMPLOYEE_ID" = r."PeopleSoftID"
Sorry the code is in PostgresSql but do not let that throw you. Field names are case sensitive and must be surrounded by double quotes. where clause is a timestamp field that is being cast to date only for the match to the supplied date. I have three tables each with more than 40 fields.
My first join is koms table to the pathfinder table. I then take the result set and do a join on the roster table which has employee data in it. the koms table has an id field "AGENT_NETWORK_ID"
and is looking for a match in the roster table on the field "NetworkID. that join condition works ok. but then I need for it to check the pathfinder fields "tsr_empl_id" or "EMPLOYEE_ID" comparing it to the roster "PeopleSoftID" field for matches. I then need all the fields from all three tables returned in the result set.
I must be doing something wrong on my joins because it does no do any matching on the fields in the pathfinder table to the roster table.
I know this is a t-sql forum, however the joins should work the same, so please shout out if you see someplace I do not know what I am doing.
The roster table has about 15 million records, pathfinder is a daily data pull I do from Hadoop of about 150k records. The koms table is a daily pull I do from Oracle of about 250k records