lundi 21 septembre 2015

Return newest row when several columns are the same

I have a very complex Union query that returns up to two rows. If the second through the 15th columns are identical in both records, I want to return the results of the second record. Otherwise, return the results of the first record which is the newest record. The goal is to return the newest name and address with a pending flag only if the data is not the same.

SELECT  TOP 1 
                        1 AS updatePending,
                        a.entity_number,
                        a.name_title,
                        a.name_first,
                        a.name_middle,
                        a.name_last,
                        a.name_suffix,
                        LTRIM(RTRIM(REPLACE(
                        LTRIM(RTRIM(ISNULL(a.name_first, ''))) +
                        CASE WHEN LEN(LTRIM(RTRIM(ISNULL(a.name_first, '')))) = 1 THEN '. ' ELSE ' ' END +
                        LTRIM(RTRIM(ISNULL(a.name_middle, ''))) +
                        CASE WHEN LEN(LTRIM(RTRIM(ISNULL(a.name_middle, '')))) = 1 THEN '. ' ELSE ' ' END +
                        LTRIM(RTRIM(ISNULL(a.name_last, ''))) + ' ' +
                        LTRIM(RTRIM(ISNULL(a.name_suffix, '')))
                        ,'  ',' '))) AS name_full,
                        NULLIF(LTRIM(RTRIM(
                        LTRIM(RTRIM(ISNULL(a.company, ''))) +
                        LTRIM(RTRIM(ISNULL(a.firm_name, ''))))),'') AS company,
                        a.address1,
                        a.mailing_address,
                        a.city,
                        a.state,
                        a.zip_code AS zipcode,
                        a.internet_address AS email_address,
                        a.time_stamp
        FROM        statebar.dbo.STAGING_Address_Change_Request a
            INNER JOIN Member m ON m.entity_number = a.entity_number
        WHERE       a.entity_number = (
SELECT m.entity_number
FROM Member m
    INNER JOIN  Named_Entity ne ON  (ne.entity_number = m.entity_number)
WHERE   ne.name_last = 'Park'
    AND m.birth_year = '1958'
    AND m.barno = '12345'
    )
            AND a.time_stamp > m.time_stamp

        UNION ALL

        SELECT TOP 1 
                0 AS updatePending,
                ne.entity_number,
                ne.name_title,
                ne.name_first,
                ne.name_middle,
                ne.name_last,
                ISNULL(ne.name_suffix, ''),
                LTRIM(RTRIM(REPLACE(
                LTRIM(RTRIM(ISNULL(ne.name_first, ''))) +
                CASE WHEN LEN(LTRIM(RTRIM(ISNULL(ne.name_first, '')))) = 1 THEN '. ' ELSE ' ' END +
                LTRIM(RTRIM(ISNULL(ne.name_middle, ''))) +
                CASE WHEN LEN(LTRIM(RTRIM(ISNULL(ne.name_middle, '')))) = 1 THEN '. ' ELSE ' ' END +
                LTRIM(RTRIM(ISNULL(ne.name_last, ''))) + ' ' +
                LTRIM(RTRIM(ISNULL(ne.name_suffix, '')))
                ,'  ',' '))) AS name_full,
                NULLIF(LTRIM(RTRIM(
                LTRIM(RTRIM(ISNULL(ne.company, ''))) +
                LTRIM(RTRIM(ISNULL(ne.firm_name, ''))))),'') AS company,
                ISNULL(ne.address1, ''),
                ne.mailing_address,
                ne.city,
                ne.state,
                ne.zip_code,
                ne.internet_address AS email_address,
                m.time_stamp
        FROM    Member m
            INNER JOIN  Named_Entity ne ON  (ne.entity_number = m.entity_number)
            LEFT JOIN   statebar.dbo.STAGING_Address_Change_Request a ON a.entity_number = m.entity_number
        WHERE   ne.entity_number = (
                                    SELECT m.entity_number
                                    FROM Member m
                                        INNER JOIN  Named_Entity ne ON  (ne.entity_number = m.entity_number)
                                    WHERE   ne.name_last = 'Park'
                                        AND m.birth_year = '1958'
                                        AND m.barno = '12345'
                                        )
            AND m.time_stamp > a.time_stamp
        ORDER BY updatePending DESC, a.time_stamp DESC

enter image description here

Aucun commentaire:

Enregistrer un commentaire