• Luis Cazares - Wednesday, May 24, 2017 1:13 PM

    PiMané - Wednesday, May 24, 2017 9:52 AM

    My problem is that this has to work on older SQL versions that don't have PARTITION BY....
    How can I write this without partition by?

    Thanks in advance,
    Pedro

    Do you seriously have systems on SQL Server 2000? Otherwise, the PARTITION BY clause is still available from 2005 to current versions.

    I wish... Do you recall MSDE.... Yep... 

    I've figured out another way of doing but it can be a bit "heavy"

    CREATE VIEW vw_data AS
    SELECT     
    t0.id id_agent,    t0.name agent,    t1.*,    t2.id_product id_p_c,    t2.number
    FROM
    agents t0
    CROSS JOIN (    SELECT t0.id id_credentialing, t0.name credentialing, t1.id_product, t2.name product FROM credentialings t0 INNER JOIN credentialings_products t1 ON t0.id = t1.id_credentialing INNER JOIN products t2 ON t2.id = t1.id_product) t1
    LEFT JOIN agents_credentialings t2 ON t2.id_agent = t0.id AND t2.id_credentialing = t1.id_credentialing AND (t2.id_product IS NULL OR t2.id_product = t1.id_product)
    GO

    SELECT * FROM (
    SELECT * FROM vw_data t0 WHERE id_p_c IS NOT NULL
    UNION ALL
    SELECT * FROM vw_data t1 WHERE id_p_c IS NULL AND NOT EXISTS (SELECT 1 FROM vw_data t2 WHERE t1.id_agent = t2.id_agent AND t2.id_credentialing = t1.id_credentialing AND t1.id_product = t2.id_p_c AND t2.id_p_c IS NOT NULL)
    ) t



    If you need to work better, try working less...