0

So... I came across the following view. Aside for table design (some names and variables changed to protect the innocent), joins etc. I would like to simplify the query by lifting the two nested SELECT DISTINCT PARTITION BY queries, but my SQL capabilities regarding this are sadly (for the time being) lacking.

I was thinking that perhaps I could just grab the tables in the nested queries, join them with the rest of the tables, and do the MAX/PARTITION BY only once in the SELECT, but I'm having hard time to figuring out how to lift such a windowing function from a join in this particular case or in general. The inner queries are also almost the same.

I'm not sure if bringing this query without tables and example data is all right, but any pointers how to solve this, if possible, with this information, would be gratefully received. This looks deceptively simple, perhaps it is. Spending a bit time in search engines bring similarish ponderings from various places, but not quite anything like this, or in a manner I'm able to proceed, let alone comprehend.

SELECT
    TableAAA.Number, 
    TableCCC.AID, 
    REPLACE(TableCCC.BusinessID, '-', '') AS BusinessID,
    ISNULL(accountAddress.StreetAddress, cccAddress.StreetAddress) AS StreetAddress,
    ISNULL(accountAddress.StreetAddress2, cccAddress.StreetAddress2) AS StreetAddress2,
    ISNULL(accountAddress.StreetAddress3 ,cccAddress.StreetAddress3) AS StreetAddress3,
    ISNULL(accountAddress.PostalCode, cccAddress.PostalCode) AS PostalCode,
    ISNULL(accountAddress.PostOffice, cccAddress.PostOffice) AS PostOffice,
    ISNULL(accountAddress.CountryCode, cccAddress.CountryCode) AS CountryCode,
    TableBBB.ReferenceValue, 
    ISNULL(accBillInfo.BillingInfoID, cccBillingInfo.WayOfBillingID) AS WayOfBillingID,
    ISNULL(accBillInfo.EBillingAddress, cccBillingInfo.EBillingAddress) AS EBillingAddress,
    ISNULL(accBillInfo.OVT, cccBillingInfo.OVT) AS OVT,
    CASE WHEN ac.TypeC = 1 THEN 'K' ELSE 'E' END AS TypeCC, 
    ac.ProductName,
    ISNULL(ac.C, 0) AS Actives
FROM    
    TableAAA
    INNER JOIN TableCCC ON TableAAA.Id = TableCCC.Id
    LEFT JOIN [dbo].[Ac] AS ac ON ac.AccountID = TableAAA.AccountID
    LEFT JOIN TableBBB ON TableAAA.AccountID = TableBBB.AccountID  
    LEFT JOIN dbo.BillingInfo AS accBillInfo ON TableAAA.BillingInfoID = accBillInfo.BillingInfoID      
    LEFT JOIN dbo.BillingInfo_Link_Address AS abla ON abla.BillingInfoID = accBillInfo.BillingInfoID
        AND abla.Address_TypeID = 2
        AND abla.StartTime <= SYSDATETIMEOFFSET()
        AND ISNULL(abla.EndTime, DATEADD(month, 1, SYSDATETIMEOFFSET())) >= SYSDATETIMEOFFSET()
    LEFT JOIN dbo.Address AS accountAddress ON abla.AddressID = accountAddress.AddressID
    -- Company's Billing address information.
    LEFT JOIN dbo.BillingInfo AS cccBillingInfo ON TableCCC.BillingInfoID = cccBillingInfo.BillingInfoID
    LEFT JOIN dbo.BillingInfo_Link_Address AS cbla ON cbla.BillingInfoID = cccBillingInfo.BillingInfoID
        AND cbla.Address_TypeID = 2
        AND cbla.StartTime <= SYSDATETIMEOFFSET()
        AND ISNULL(cbla.EndTime, DATEADD(month, 1, SYSDATETIMEOFFSET())) >= SYSDATETIMEOFFSET()
    LEFT JOIN dbo.Address AS cccAddress ON cbla.AddressID = cccAddress.AddressID
    LEFT JOIN
    (
        SELECT DISTINCT Max(CAST(al.ABlock AS tinyint)) OVER (PARTITION BY acc.Number) AS block, acc.Number
        FROM    
            TableAAA_Limitation AS al
            INNER JOIN TableAAA_Link_Limitation AS aLinkLimit ON al.TableAAA_LimitationID = aLinkLimit.TableAAA_LimitationID 
                AND aLinkLimit.StartTime <= SYSDATETIMEOFFSET() 
                AND ISNULL(aLinkLimit.EndTime, DATEADD(month, 1, SYSDATETIMEOFFSET())) >= SYSDATETIMEOFFSET()
            INNER JOIN TableAAA AS acc ON acc.TableAAAID = aLinkLimit.TableAAAID 

    ) AS ablock ON ablock.Number = TableAAA.Number 
    LEFT JOIN
    (
        SELECT DISTINCT Max(CAST(cl.ABlock AS tinyint)) OVER (PARTITION BY ccc.AID) AS block, ccc.AID
        FROM    
            dbo.Company_Limitation AS cl
            INNER JOIN dbo.TableCCC_Link_Limitation AS cLinkLimit ON cl.CCC_LimitationID = cLinkLimit.TableCCC_LimitationID 
                AND cLinkLimit.StartDate <= SYSDATETIMEOFFSET()                     
                AND (ISNULL(cLinkLimit.EndDate, DATEADD(month, 1, SYSDATETIMEOFFSET())) >= SYSDATETIMEOFFSET())
            INNER JOIN TableCCC AS ccc ON ccc.Id = cLinkLimit.Id 
    ) AS cccBlock ON cccBlock.AID = TableCCC.AID
WHERE   
    (ISNULL(ablock.block, 0) < 1) 
    AND (ISNULL(cccBlock.block, 0) < 1)
2

SELECT DISTINCT MAX(x) OVER (PARTITION BY y), y, when you're not returning any other columns is the same as SELECT MAX(x), y ... GROUP BY y. I'd suggest you start by doing that, and you may find your abilities are well capable of doing further refactoring.

  • Thanks, I'll try that as soon I'll get some spare time. Just a note that I intended to work on this during the weekend... – Veksi Feb 14 '15 at 15:24
  • Took a bit more than I thought (family matters). I think I got it, I hope to update the question in the future with more details, but this tip was most helpful, thanks. – Veksi Feb 18 '15 at 6:54
  • Glad it could help. Post in the comments here so I see them. :) – Rob Farley Feb 18 '15 at 7:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.