getting the right values for the right code and time period

[#1]

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

[#2]

Its something like this, i select a Requestts and it needs to link with a date in startTS but as you can see for that time there isn't one so it should link to the value smaller and closest to the requestTS in this case: 01/01/2007. Just to make sure it's not always the first of the month for startTS it's just a coincidence.

[gandalfgray · Accepted solution]

Hi

I would do something like:

//find the correct startTS

Left Join (Table1)

Intervalmatch (RequestTS, PrestationCode)

Load startTS,

    endTS,

    PrestationCode

resident Table2;

// Join the Value

Left Join (Table1)

Load startTS,

    PrestationCode,

    Value

Resident Table2;

Drop Table Table2; // Drop when you finished with it, otherwise you get syntetic keys

You can also drop fieds startTS and endTS at this stage if you want to.

[#4]

Thanks for helping! It didn't fix the problem from the start cause i had some old data which was messing with my values but i did a where year(startTS) > '2005' and then i got the right values thanks