Trying to filter duplicates
-
Hi,
I have this query that produces duplicates. I am trying to filter out based on the duplicate 'member address id' .
I tried using max(member address id) but that isn't working. I think I need the query to evaluate first and then give me only the row that has the max member address id - but can't figure out how to do that.
SELECT top 100 eligibility.eligibility_ud, MAX (member_address.member_address_id) AS [Member Address ID],member_address.address1,member_address.city, member_address.state, LEFT(member_address.zipcode, 5) AS [ZIP CODE], eligibility.term_date, employergroup.employergroup_id
FROM eligibility INNER JOIN
employergroup ON eligibility.employergroup_id = employergroup.employergroup_id INNER JOIN
member ON eligibility.member_id = member.member_id AND eligibility.subscriber_id = member.member_id INNER JOIN
member_address ON member.member_id = member_address.member_id
WHERE eligibility.term_date >= GETDATE() OR eligibility.term_date IS NULL
GROUP BY eligibility.eligibility_ud, employergroup.employergroup_id, member_address.address1,
member_address.city, member_address.state, member_address.zipcode, eligibility.term_date
order by eligibility.eligibility_ud
-
krypto69 (5/18/2015)
Hi,I have this query that produces duplicates. I am trying to filter out based on the duplicate 'member address id' .
I tried using max(member address id) but that isn't working. I think I need the query to evaluate first and then give me only the row that has the max member address id - but can't figure out how to do that.
SELECT top 100 eligibility.eligibility_ud, MAX (member_address.member_address_id) AS [Member Address ID],member_address.address1,member_address.city, member_address.state, LEFT(member_address.zipcode, 5) AS [ZIP CODE], eligibility.term_date, employergroup.employergroup_id
FROM eligibility INNER JOIN
employergroup ON eligibility.employergroup_id = employergroup.employergroup_id INNER JOIN
member ON eligibility.member_id = member.member_id AND eligibility.subscriber_id = member.member_id INNER JOIN
member_address ON member.member_id = member_address.member_id
WHERE eligibility.term_date >= GETDATE() OR eligibility.term_date IS NULL
GROUP BY eligibility.eligibility_ud, employergroup.employergroup_id, member_address.address1,
member_address.city, member_address.state, member_address.zipcode, eligibility.term_date
order by eligibility.eligibility_ud
Something like this:
with BaseData as (
SELECT --top 100
e.eligibility_ud,
ma.member_address_id AS [MemberAddressID],
ma.address1,
ma.city,
ma.state,
LEFT(ma.zipcode, 5) AS [ZipCode],
e.term_date,
eg.employergroup_id,
rn = row_number() over (partition by e.eligibility_ud order by ma.member_address_id desc)
FROM
dbo.eligibility e
INNER JOIN dbo.employergroup eg
ON e.employergroup_id = eg.employergroup_id
INNER JOIN dbo.member m
ON e.member_id = m.member_id AND
e.subscriber_id = m.member_id
INNER JOIN dbo.member_address ma
ON m.member_id = ma.member_id
WHERE
e.term_date >= GETDATE() OR e.term_date IS NULL
)
select
bd.eligibility_ud,
bd.MemberAddressID,
bd.address1,
bd.city,
bd.state,
bd.ZipCode,
bd.term_date,,
bd.employergroup_id
from
BaseData bd
where
bd.rn = 1;
-
Awesome. Thank you very much Lynn.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply