Advanced lookup transformation

Hi,

Here is my problem for a lookup transformation:

I have an input flow with dates and fields like this :

ID BEGINNING_DATE ENDING_DATE

1 12/01/2006 12/16/2006

and a reference table like this:

ID PRICE BEGINNING_PRICE_DATE ENDING_PRICE_DATE

1 400 11/28/2006 12/03/2006

1 500 12/03/2006 12/06/2006

1 600 12/06/2006 12/09/2006

I have to get the intersection periods between the two tables joining on ID. I would like to have this result flow :

ID BEGINNING_DATE ENDING_DATE PRICE

1 12/01/2006 12/03/2006 400

1 12/03/2006 12/06/2006 500

1 12/06/2006 12/09/2006 600

I'm using a lookup transformation and modifying the SQL instruction in advanced tab like this:

select *
from
(select * from [dbo].[Price]) as refTable
where [refTable].[ID] = ?
and (? between [refTable].[BEGINNING_PRICE_DATE] and [refTable].[ENDING_PRICE_DATE]
or ? between [refTable].[BEGINNING_PRICE_DATE] and [refTable].[ENDING_PRICE_DATE]
or ([refTable].[BEGINNING_PRICE_DATE] between ? and ?
and [refTable].[ENDING_PRICE_DATE] between ? and ?))

In that case I have to define 7 parameters:

ID : parameter0

BEGINNING_DATE : parameter1, parameter3, parameter5

ENDING_DATE : parameter2, parameter4, parameter6

My problem is that the transformation looks for only one matching element and outputs 0 or 1 row per input row... In that case the 3 rows of my lookup table are matching with the row in my input table, but I have only the first one.

How could I do to resolve my problem?

Thanks... I hope it's clear...

Arnaud.

The lookup transformation can only output 1 row per 1 input row.

You would probably be best to load the data into a table and then join the 2 tables together.

You can simplyfy the join to

import.startDate < lookup.enddate

and import.endDate > lookup.startDate

This will match all records that overlap

|||

I think I have to keep my join because with your constraints I only have the price periods included in the imported periods and not those that overlap...

On the other hand I think I will create a temporary table for the input flow and join it to the lookup table as you recommend.

I wanted to avoid creating temporary tables but it seems to be the best solution here

Thanks a lot!

Arnaud.

|||

The conditions do allow for overlap.

In english they A and B overlap if A finshes after B starts and also A must start before B finishes. Thus the 2 criteria I posted.

|||

Yes Simon you're right!

My eyes were closed, sorry...

It simplyfies my sql request and all works fine, thanks a lot!

Arnaud.

|||No problem, Its a common scenario that many don't believe can be simplified to a single AND statement