10 Replies Latest reply on Jul 28, 2017 11:55 AM by Jonathan Drummey

    Filling in the missing dates

    Ken Muir

      Hello

       

      Help needed! I have a set of data which details a product and it's location with the date it goes into the location and date that it goes out. A simplified table below. I would like to manipulate the data so I can see exactly what location the product is in at any date, not just the dates recorded in the original data source. Is there a way I can do it as a SQL custom query or any other way?

       

          

      Product Location InLocation OutLocation
      Product 118/06/1721/06/178
      Product 122/06/1723/06/1710
      Product 218/06/1723/06/1714

       

      Desired Output;

          

      DateProductLocationLocation InLocation Out
      18/06/17Product 1818/06/1721/06/17
      19/06/17Product 1818/06/1721/06/17
      20/06/17Product 1818/06/1721/06/17
      21/06/17Product 1818/06/1721/06/17
      22/06/17Product 11022/06/1723/06/17
      23/06/17Product 11022/06/1723/06/17
      18/06/17Product 21418/06/1723/06/17
      19/06/17Product 21418/06/1723/06/17
      20/06/17Product 21418/06/1723/06/17
      21/06/17Product 21418/06/1723/06/17
      22/06/17Product 21418/06/1723/06/17
      23/06/17Product 21418/06/1723/06/17

       

      I have attached a .txbx file with the original table on.