Need two different children on one row

[TruthSeeker]

I need to output data from one parent record and two different child records on one row.

Parent table - primary key X

Child table - primary key (X, Effective Date) - there will be one to "n" of these per parent
                - other column is Rate

Input Start Date
Input End Date

I need to output
- X
- largest effective date that is less than StartDate
        (there might not be one)
- matching Rate
- largest effective date that is less than EndDate
        (there might not be one)
        (might be the same record as found above, might not)
- matching Rate
- (math for amount the Rate changed - this one is easy).

I can find one child or the other, but I'm not sure how to include two different children on one row.

I feel like I've done this sort of thing before, but I haven't worked in Cognos for a while and I'm sure I'm just having a brain cramp. Any pointers are welcome.
Thanks for any help
Paul

[Francis aka khayman]

looks like you need to create a set


c1 = [child1]
c2 = [child2]


[2childs] = set([child1];[child2])

your crosstab will be:

                             [column1]
[whatever1]
[2childs]
[other rows]