2 Replies Latest reply on Oct 26, 2016 11:26 PM by willy Anderson

    Using holiday table

    willy Anderson

      Hi All,

       

      I have search (through google) for my question but I didn't find any solution, so I hope to find it here.

       

      I have several tables that have start and end date. For example I have order table with structure like below:

       

      Order IdStatusOrder Date
      Delivered date
      O001D28 December 20155 January 2016

       

      I also have another table that contain all holiday, including weekend. Below are the structure for the holiday table:

      DateDescriptionNotes
      31 December 2015New Year Eve
      1 January 2016New Year
      2 January 2016Saturday
      3 January 2016Sunday

       

      What I want to find is the number of record on holiday table between the order date and delivered date so I can count how many days it take to deliver order 001 (it should be 5 days -28-30 December then 4-5 January).

       

      Really appreciate the help and thanks before

        • 1. Re: Using holiday table
          Patrick A Van Der Hyde

          Hello willy,

           

          The difference between two dates is one thing and that can be calcualted per row but it appears you are trying to do more than that.  I want to clarify the request - you are trying to count the number of events in the holiday table that occurred in between the two dates and there could be multiple events (Christmas Eve, Christmas, New Years Eve, New Years = 4)  for a range of 12/20 to 1/4.  Is that right? 

           

          Considering the data, I think you would need  a row in that first table for every single day between start and end to match on but maybe a few brighter minds than mine might have an idea.

           

          Rody Zakovich  - I always think of you and Date tricks..  any other ideas come to mind?  Willy - if you can clarify that I understood the issue correctly, that would help too.

           

          Thanks

           

          Patrick 

          • 2. Re: Using holiday table
            willy Anderson

            Hi Patrick,

             

            Essentially you're right. I need to find out how many holiday (Taken from holiday table that I already have) between two date. Please note that I already include the weekend in the holiday table (hoping that this make things easier)