6 Replies Latest reply on Dec 7, 2013 9:26 AM by JulianJohnson

    Counting concurrent requirements

    JulianJohnson

      Hi There,

       

      I am grappling with a database design problem and wondered if anyone has any better ideas of approaching it......

       

      I am working on a database that holds a pool of stock (one table) of objects (another table). (The products are branded signage that are used for events.) One object has more than one identical physical item in stock, the object record describes what the item is and the stock records are the individual physical items.

       

      When the sales person selects a line item on the sale, my database generates a record in a table of stock requests that specifies what it is (object) and when it is required from and to. The idea is that it can then work out how many items of stock are required simultaneously and decide if there is enough in the pool of stock to fulfil the requirement.

       

      So, this is where I'm up to:

       

      From the objects table I set two global fields with the start and end date I am looking for.

       

      I have identified four potential combinations of stock requirements that I need to include and have a table occurence for each. They are:

      • A requirement that starts before the period and ends within it

      • A requirement that starts within the period and ends after it

      • A requirement that starts and ends within the period or on the same days as the period

      • A requirement that starts and ends outside of, but includes, the period.

       

      I then use a script step to add the number of requirements in each of the table occurences together to give me a total quantity of stock required within the period I'm testing. I have this working perfectly except for this problem:

       

      If one requirement within the period finishes before another one starts they count as two different requirements. However they should only really count as one as the same item of stock could be used for both.

       

      The only other way I can think of doing this is to divide the time in the period into slices (say one slice per day) and loop through calculating how many requirements exist for that slice then take the maximum of those quantitties. However, this will be messy, performance sapping and the client has mentioned wanting it accurate to times as well as dates that would require thousands of slices per day!

       

      Am I missing an obvious alternative way of doing this?

       

      Thanks in advance for any ideas.

       

      Kind Regards

      Jules

        • 1. Re: Counting concurrent requirements
          DavidJondreau

          To start, you need one table occurence, not four. A relationship of

           

          Request::EndDate >= Global::StartDate

          Request::StartDate <= Global::EndDate

           

          will cover all four scenarios. Changing those to timestamps will work too.

           

          What you're looking for is the max quantity of an item during a period. That quantity may actually fluctuate, right? I don't see a way of getting out of testing multiple periods, but you don't need to test every day or every minute if using times, just those that are the start or end of a Request.

           

          So...make a relationship between Requests and another TO also of Requests with the Dates as above and add ObjectID=ObjectID. Do a find on Requests for records in your Start and End Date. Loop through your found set and Sum() the related requests. Pick the highest of that Sum().

           

           

          Note this is only better than testing slices of a day, if the number of requests are less than the number of slices!

          • 2. Re: Counting concurrent requirements
            JulianJohnson

            Hi David,

             

            Thanks for your detailed response, I've been thinking it through and there were a couple of issues came up.

             

            Would this one table occurrence return all potential conflicts though? The reason I ended up with four TOs was that I need to find stock requirement records that conflict even if they start and/or end outside of the period in question. I did originally have a relationship the same as yours suggested but it only found requirement records that started and ended either at the same time or within the period.

             

            The other issue is that if I had two requirement records where one ended after the period started and the other started before the period ended but they didn't overlap each other within the period (ie the first ends before the second starts) my calculation would return two pieces of stock required where we'd actually only need one.

             

            Is what we're trying to achieve impossible do you think?

             

            Thanks very much.

             

            Jules

            • 3. Re: Counting concurrent requirements
              DavidJondreau

              Trust me, what's in my response will work. I understand what you're trying to do and the problems you've run into (I've built a couple rental inventory systems).

               

              If you try it out and there's a problem, feel free to ask, but take a stab at understanding it first.

               

              For the first, write down a bunch of pairs of times, at least one for each of the four overlaps and some that don't overlap. You'll see the conflicts all have a End Date greater than the Start Date and a Start Date less than the other End Date.

               

              For the second, re-read what I wrote. You have to look through each "Request" record in your date range. By doing that you're checking a different set of dates (presumably) for each record. The request records that conflict with the most other request records is the number you care about. And you could even find the request with the most days and the biggest conflict in case you need to, for example, sub-lease equipment.

              • 4. Re: Counting concurrent requirements
                JulianJohnson

                Hi David,

                 

                I'm so close! I have the relationship from the first part set up and it's finding the right records with no problem. I've tested it using a load of test records and get the same results as using my clunky four TOs so thanks for that.

                 

                I now also have a second TO of the stock requirements table and have a join where ObjectID = ObjectID, EndDate >= StartDate and StartDate <= EndDate.

                 

                I put a calculation field into the original stock requirements TO that sums the quanitites required in the new TO so I could see what was going on. So I now get a value of overlapping records for each of the stock requirement records. However, I'm not sure it's the maximum number I need.

                 

                I've illustrated the records I have below and my query is looking at the period 24/12/13 - 26/12/13:

                 

                Start                    End               Sum in second TO

                24/12/13     -     26/12/13          4

                24/13/13     -     24/12/13          3

                25/12/13     -     27/12/13          3

                20/12/13     -     30/12/13          4

                 

                Basically, in this scenario I'd need the calculation to return 3 as I've be using three stock items to fulfil it (as the second two requirements don't overlap. Do you see any issues with me using the minimum here instead of max?

                 

                Thanks so much for all your help. I think I'm tantalisingly close!

                 

                Thanks

                Jules

                • 5. Re: Counting concurrent requirements
                  DavidJondreau

                  Can you post the quantities for each of those request records? I'm guessing it's just 1 for each, but that's not clear. And I assume the Start date on the second line is a typo.

                   

                  Hmmm...I see the issue. On 12/24, you needed three, one 12/25, you had one 12/24 request return, and a new request starting. Off the top of my head, I'm not sure the best way to handle this except to do a loop for each date in the request period starting from the Objects table, like you thought to start. I'll think about it though.

                  • 6. Re: Counting concurrent requirements
                    JulianJohnson

                    Hi David,

                     

                    Nailed it! You were right about the looping script and also only sampling on the start point of each stock requirement record. I use GTRR to got the records affected and set a global with the start timestamp of each record and then sum the quantity required in a table occurrence that shows records that have the same objectID and fall across the timestamp in the global. To get the max I update a variable if it was larger than the existing value as it loops through. One thing to note is that when setting the start timestamp into the global I have to use the start timestamp of the record I'm on or start of the period I'm looking at, whichever is later, so it doesn't include any requirements from before my search period begins.

                     

                    There was an added complication that the client needs to know the requirements of stock for the current client and also requirements that aren;t for the client, in case he needs to borrow some. However, that's dealt with as the script runs the calculation twice using two variables & TOs.

                     

                    Thanks very much for your help.

                     

                    Kind Regards

                    Jules