Forum Discussion

Mike22's avatar
Mike22
Icon for Helper III rankHelper III
7 years ago

Calculate a working week date range inside a broader day range considering different calendar reason

Dear Friends, I am trying to check if a date range is included in another range of dates from another table. I have two tables Actual travel – Which shows the actual dates a “person id” was travel...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Mike22 ,

It seems like your table contains multiple same user request, I'd like to suggest you add index column to these table to define each request.

After these steps, I think you can use index and user id to find out correspond records to compare with these records to check if actual date range are into request ranges.
Then you can if statement with weekday function(use WeekDay on start/end date to extract weekday) to compare with conditions and return 'IN' result.

Regards,

Xiaoxin Sheng

  • Mike22's avatar
    Mike22
    Icon for Helper III rankHelper III
    7 years ago

    Hello Anonymous 

     

    Thank you for your reply. Is it possible to give me a bit more detail?

     

    I can create an index table with unique person IDs but how do I then check all the dates to see if there is a  "travel requested" entry covering the the "actual travel" period. 

     

    Thanks,

    Mike

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Mike22 

       

      Can you explain step by step  how you arrived at the final table.

      Like what you compared with what ?

       

      Cheers

       

      CheenuSing