CODEWITHSUNDEEP
sqlsql-serverquery-optimization
user2623955
user2623955

Reputation: 175

Conditional Joining and query optimization when joining to the same table multiple times

We have two tables in our database. The primary table in the query in question is Asset. It has a one to many relationship with a table called AssetListing. The AssetListing table has several date and numeric fields in it, and we currently have a report that is set up to get one of each date, and in some cases, a numeric value from the AssetListing table where the date is not null. The caveat is that we want to get the date associated with the most recent AssetListingId (PK on AssetListing) in each case where that particular date is not null. The query currently looks like this:

SELECT A.AssetNumber
        , CLP.CurrentListPrice
, CLP.CurrentListPriceDate
, ListingExpirationDate = LXP.ExpirationDate
, LPC.ListPriceChange
, LPC.ListPriceChangeDate
FROM Asset A
LEFT JOIN
    (
        SELECT AssetNumber
            , AssetListingId
            , CurrentListPrice
            , CurrentListPriceDate
            , ROW_Num = ROW_NUMBER() OVER (PARTITION BY AssetNumber ORDER BY AssetListingId DESC)
        FROM [dbo].AssetListing WITH (NOLOCK)
        WHERE CurrentListPrice IS NOT NULL
    ) CLP ON CLP.AssetNumber = A.AssetNumber AND CLP.ROW_NUM = 1
LEFT JOIN
    (
        SELECT AssetNumber
            , AssetListingId
            , ExpirationDate
            , ROW_Num = ROW_NUMBER() OVER (PARTITION BY AssetNumber ORDER BY AssetListingId DESC)
        FROM [dbo].AssetListing WITH (NOLOCK)
        WHERE ExpirationDate IS NOT NULL
    ) LXP ON LXP.AssetNumber = A.AssetNumber AND LXP.ROW_NUM = 1
LEFT JOIN
    (
        SELECT AssetNumber
            , AssetListingId
            , CurrentListPrice
            , ListPriceChange
            , ListPriceChangeDate
            , ROW_Num = ROW_NUMBER() OVER (PARTITION BY AssetNumber ORDER BY AssetListingId DESC)
        FROM [dbo].AssetListing WITH (NOLOCK)
        WHERE ListPriceChange IS NOT NULL
    ) LPC ON LPC.AssetNumber = A.AssetNumber AND LPC.ROW_NUM = 1

There has to be a way to do this with a single join, but I am not sure how to go about doing it. We need a date from each of the joins, and in some cases, we also need a numeric value. How can you optimize this query?

Upvotes: 1

Views: 79

Answers (1)

T I
T I

Reputation: 9933

Try this, the idea is to take the max of what should only ever be one result per asset number based on the row number.

SELECT A.AssetNumber
    , CurrentListPrice      = MAX(CASE WHEN CPRN = 1 THEN CurrentListPrice END)
    , CurrentListPriceDate  = MAX(CASE WHEN CPRN = 1 THEN CurrentListPriceDate END)
    , ListingExpirationDate = MAX(CASE WHEN EPRN = 1 THEN ExpirationDate END)
    , ListPriceChange       = MAX(CASE WHEN EPRN = 1 THEN ExpirationDate END)
    , ListPriceChangeDate   = MAX(CASE WHEN EPRN = 1 THEN ExpirationDate END)
FROM Asset A
JOIN (
    SELECT AssetNumber
        , AssetListingId
        , CurrentListPrice
        , CurrentListPriceDate
        , ExpirationDate
        , ListPriceChange
        , ListPriceChangeDate
        , CPRN = CASE WHEN CurrentListPrice IS NOT NULL 
                      THEN ROW_NUMBER() OVER (PARTITION BY AssetNumber ORDER BY AssetListingId DESC) END
        , EPRN = CASE WHEN ExpirationDate IS NOT NULL 
                      THEN ROW_NUMBER() OVER (PARTITION BY AssetNumber ORDER BY AssetListingId DESC) END
        , LPRN = CASE WHEN ListPriceChange IS NOT NULL 
                      THEN ROW_NUMBER() OVER (PARTITION BY AssetNumber ORDER BY AssetListingId DESC) END
    FROM dbo.AssetListing WITH (NOLOCK)
) AL ON AL.AssetNumber = A.AssetNumber
GROUP BY A.AssetNumber

The join doesn't actually look necessary here as is but I've left it in just in case there is some simplification.

Upvotes: 1

Related Questions