I am attempting to create a flag in my load statement but the flag has some complex logic. Here is some pseudo code on what I am attempting to do:
CASE:
LOAD LOG_ID, IN_TIME, OUT_TIME, EMP_ID, ServDate, Location
FROM Case.qvd;
SERV:
LOAD EMP_ID, SERVICE
FROM Serv.qvd;
BLOCK:
LOAD SERVICE, DayOfWeek, WeekNum, StartDate, EndDate, StartTime, EndTime,BlockLoc
FROM Block.qvd;
CAL:
created calendar table with different date element to tie together
I need to create one final table using some join statements to create a flag. Flag logic needs to be like the following:
if CASE.ServDate between BLOCK.StartDate and BLOCK.EndDate
and CASE.IN_TIME between BLOCK.StartTime and BLOCK.EndTime
and CASE.Location = BLOCK.BlockLoc
and WeekDay(CASE.ServDate) = BLOCK.DayOfWeek
and CAL.WeekNum = BLOCK.WeekNum
THEN 1 ELSE 0
I have tried doing some joins to create a staging table with the elements needed, then build the if logic on the staging table but I can't seem to get it to work.
Anyone have any suggestions on the best way to accomplish this?
Thanks
