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:

       

      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