Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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.
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