9 Replies Latest reply: May 2, 2016 5:48 PM by John Witherspoon RSS

    Calculate the number of open orders for each date in calendar

    James Nimmo

      Hi all,

       

      Wondering if anyone can assist me, trying to figure out how to add to my Calendar table a field which I can use to show the number of open orders over time. I'm not sure why I can't figure it out but perhaps I'm overthinking it as it seems like it should be simple.

       

      I'm sure there must be lots of threads about it here but I haven't had any luck.

       

      Logically what I'm trying to achieve:

      Count Distinct Order ID Where [Order Date Created] <= [Date] AND [Order Date Closed] > [Date] As [Open Orders]

       

      Orders

      Calendar

      Order IDDate
      Order Date CreatedOpen Orders
      Order Date Closed


      Of course the same prinicpal could be applied to...

      Hotels - number of guests at point in time

      Hospital - number of patients admitted at point in time

      School - number of students enrolled

       

      Would appreciate any pointers

       

      Cheers

      James