3 Replies Latest reply: May 6, 2011 5:33 AM by Reignfire RSS

    getting the right values for the right code and time period

      Hi,

       

      i'm having some troubles linking 2 tables. This is the situation:

       

      Table1:

      - PrestationCode
      - RequestTS

       

      Table2:

      - PrestationCode

      - startTS

      - endTS

      - Value

       

      Each PrestationCode can be used multiple times and has values for a certain time period (startTS - endTS). I first just created a concatenated link PrestationCode&'-'&monthstart(RequestTS) with PrestationCode&'-'&startTS. It worked in must cases cause startTS was in most cases the first of the month but sometimes it's not the first and i can't just set startTS to the first of the month cause then i'll get wrong values.

      example:

       

      code: 5000 startTS: 1/07/2007 endTS: 31/12/2007 values: 10, 20
      code: 5000 startTS: 11/07/2007 endTS: 30/06/2008 values: 5

      code: 1000 startTS: 1/01/2007  endTS: 31/12/2009 values: 15

       

      if i select a code and startTS than i'll get a unique endTS so i don't think i need an intervalmatch... I just need to see if there is more then one startTS in that month.

       

      I hope you guys understand my situation and can help me