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
Aucun commentaire:
Enregistrer un commentaire