Problem joining tables in SQL

Question

SELECT MID, FAD.FirstOpenedDate ,LCD.LastCloseDate
FROM mwMaster.dbo.Merchant M
JOIN        (
            SELECT MerchID, MIN(moddate) AS FirstOpenedDate
            FROM mwMaster.dbo.MerchantStatusHistory
            GROUP BY MerchID
            )   FAD ON FAD.MerchID = M.MerchID 
LEFT JOIN   (
            SELECT MerchID, MAX(moddate) AS LastCloseDate
            FROM mwMaster.dbo.MerchantStatusHistory
            GROUP BY MerchID
            )   LCD ON LCD.MerchID = M.MerchID
JOIN        (
            SELECT merchid ,avg(Transactions) ,avg(Profit) 
            FROM mwMaster.dbo.ResidualSummary RS
            WHERE RS.Date_Processed < LCD.LastCloseDate
            GROUP BY Merchid    
            )   R ON R.MerchID = M.MerchID 

I am having trouble performing the following join. I have run into this problem before and used temp tables but would like to find out what I am doing wrong. Basically the line that is not working is the 3rd to last. The "< LCD.LastClostDate" says that it cannot be bound. Is it possible to use the value from LCD which I created in a nested query above (in that query I used the M table in a similar way but I didnt run into any issue)? I am thinking becasue the LCD table is dynamically created here it cannot be used in the nested query but this is just my guess.

Any ideas?

On a side note I have also seen people using a CROSS and OVER. Not to farmiliar with how this works but may be applicable here?

Milky

Can you explain in plain English what you're trying to achieve here'? – Nonet

Join in a table by using a nested query and use one of the columns in the table created above in a where clause in the select statment – Milky

@marc - looks like the issue is that he wants to use a reference to a subquery from within another subquery, basically. – Laurellaurella

Is the last join even needed? I don't see you referencing any columns from the R subquery. – Heerlen

Is there some reason you can't combine the first two subselects? they select from the same relation and group the same column. – Fcc

@Joe Stefanelli Since its an INNER JOIN technically there is a reference – Nickinickie

The actual query is much more complicated I just took out a small piece to try and paint a picture – Milky

@JNK, this guy has a good idea of what I am trying to do – Milky

@Conrad Frix: Right, but if the columns aren't needed, we can eliminate the overhead of calculating the aggregates and possibly improve things with a WHERE EXISTS... instead of a JOIN. – Heerlen

You have to wrap it to make the whole thing a subquery to use LCD.LastCloseDate – Disject

Whats do you mean by wrap it? – Milky

Answer 1

I think though haven't tested you can just change your JOIN to a CROSS APPLY in SQL 2005+

SELECT MID, FAD.FirstOpenedDate ,LCD.LastCloseDate
FROM mwMaster.dbo.Merchant M
JOIN        (
            SELECT MerchID, MIN(moddate) AS FirstOpenedDate
            FROM mwMaster.dbo.MerchantStatusHistory
            GROUP BY MerchID
            )   FAD ON FAD.MerchID = M.MerchID 
LEFT JOIN   (
            SELECT MerchID, MAX(moddate) AS LastCloseDate
            FROM mwMaster.dbo.MerchantStatusHistory
            GROUP BY MerchID
            )   LCD ON LCD.MerchID = M.MerchID
CROSS APPLY(
        SELECT merchid ,avg(Transactions) ,avg(Profit) 
        FROM mwMaster.dbo.ResidualSummary RS
        WHERE RS.Date_Processed < LCD.LastCloseDate
        GROUP BY Merchid    
        )   R ON R.MerchID = M.MerchID 

But it might be easier to use CTEs

 WITH LCD AS (SELECT MerchID, MAX(moddate) AS LastCloseDate
        FROM mwMaster.dbo.MerchantStatusHistory
        GROUP BY MerchID),
  R AS (
              SELECT merchid ,avg(Transactions) ,avg(Profit) 
              FROM mwMaster.dbo.ResidualSummary RS
                   INNER JOIN LCD on 
                   LCD.MERCHID = RS.MERCHID
              WHERE RS.Date_Processed < LCD.LastCloseDate
              GROUP BY Merchid    
            )

SELECT MID, FAD.FirstOpenedDate ,LCD.LastCloseDate
FROM mwMaster.dbo.Merchant M
JOIN        (
            SELECT MerchID, MIN(moddate) AS FirstOpenedDate
            FROM mwMaster.dbo.MerchantStatusHistory
            GROUP BY MerchID
            )   FAD ON FAD.MerchID = M.MerchID 
LEFT JOIN LCD ON LCD.MerchID = M.MerchID
LEFT JOIN R ON R.MerchID = M.MerchID 

Nickinickie

Agreed, I generally find CTEs the easiest/simplest way to do this. Although, why not include the remaining sub-select (MIN(moddate)) in the LCD CTE? The join criteria is the same (same table and columns, LEFT JOIN LCD pointless as a JOIN is previously required), can be part of the same table/index lookup. – Moretta

Answer 2

I can't really test this without your data, but here's one way you could do it:

SELECT MID,
       MIN(moddate) OVER (PARTITION BY MerchID) as FirstOpenedDate,
       MAX(moddate) OVER (PARTITION BY MerchID) as LastCloseDate
FROM mwMaster.dbo.Merchant
HAVING DateProcessed < LastCloseDate

Cinchonism