1 Reply Latest reply: Sep 16, 2016 3:47 PM by Stan Bennett RSS

    Trying to create flag based on muliple load statements and criteria

    Andrew Moberg

      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:



      LOAD LOG_ID, IN_TIME, OUT_TIME, EMP_ID, ServDate, Location

      FROM Case.qvd;




      FROM Serv.qvd;



      LOAD SERVICE, DayOfWeek, WeekNum, StartDate, EndDate, StartTime, EndTime,BlockLoc

      FROM Block.qvd;



      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?