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?
 
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
 
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.