asked on

sql join with aggregate column in the join clause

I am trying to self join the source table in the worksheet attached to get to the format in the target in attached worksheet.
Basically I want the close date for each open and reopen record, but I get two close dates with each Open and Reopen. I know we can restrict this by using min (Transaction_Data) where C_Status is 'Close' with a group by and a having clause, but can't figure out how.

Please help.
TestData.xls

Scott Pletcher

SELECT
s_open2.stg_CFT_ID, s_close.CF_ID, s_open2.C_status, s_open2.Transaction_Date,
s_close.CFT_ID, s_close.C_status, s_close.Transaction_Date
FROM source s_close
CROSS APPLY (
SELECT TOP (1) s_open1.*
FROM source s_open1
WHERE
s_open1.CF_ID = s_close.CF_ID AND
s_open1.Transaction_Date <= s_close.Transaction_Date AND
s_open1.C_status IN ('OPEN', 'RE-OPEN')
ORDER BY
s_open1.Transaction_Date DESC
) AS s_open2
WHERE
s_close.C_status = 'CLOSE'

Performance will depend on the specific index(es) available. If not fast enough, let me know what indexes are available and we can re-work the query.

patd1

ASKER

Thank you Scott for a quick response.
This works as long as there are no Open or Re-Open records with a corresponding close date, but I need a null for close date if there is no corresponding close date for any open or re-open record. Can we do this with a left out Join?

I have added more data to the test file to check this.
TestData.xls

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

patd1

ASKER

Thank you again Scott. This one works for my test data.
I am guessing that you are using apply instead of join to be able to use the function top(1). Is that right?
Is there a way to accomplish the same with left outer Join?

Scott Pletcher

No, not directly. I guess you could use a LEFT OUTER JOIN to a correlated subquery if you really don't want to use OUTER APPLY for some reason.

patd1

ASKER

Thank you.