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
patd1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
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.
0
patd1Author Commented:
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
0
Scott PletcherSenior DBACommented:
SELECT
     s_open.stg_CFT_ID, s_open.CF_ID, s_open.C_status, s_open.Transaction_Date,
     s_close2.CFT_ID, s_close2.C_status, s_close2.Transaction_Date
FROM source s_open
OUTER APPLY (
    SELECT TOP (1) s_close1.*
    FROM source s_close1
    WHERE
        s_close1.CF_ID = s_open.CF_ID AND
        s_close1.Transaction_Date >= s_open.Transaction_Date AND
        s_close1.C_status IN ('CLOSE')
    ORDER BY
        s_close1.Transaction_Date
) AS s_close2
WHERE
    s_open.C_status IN ('OPEN', 'RE-OPEN')
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

patd1Author Commented:
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?
0
Scott PletcherSenior DBACommented:
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.
0
patd1Author Commented:
Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.