Duplicates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Duplicates

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-06-13 : 16:50:09

Hi,

Is there a way to find duplicates in one field? For example my query has person_nbr and for each person_nbr on one day they could have used multiple payer_names. I want to be able to count each person_nbr one time but also I want to group by description(which is the name of the provider) and by payer name to see how many person's that the provider seen with each payer. My problem is that if the person had more than one payer they are counted twice. Is there some type of aggregate function to use the first payer in the list??

With PersonMIA (person_id,person_nbr,first_name,last_name,date_of_birth) as
(
select distinct person_id,person_nbr,first_name,last_name,date_of_birth from
(select count(*) as countenc,a.person_id,a.person_nbr,
a.first_name,a.last_name,
a.date_of_birth
from person a
join patient_encounter b on a.person_id = b.person_id
group by a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth )tmp
where tmp.countenc <=1
)
select person_nbr,payer_name,first_name,last_name,description,year(create_timestamp),create_timestamp
from
(
select distinct c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,
tmp.last_name,tmp.date_of_birth,d.payer_name,b.create_timestamp
from PersonMIA tmp
join person a on a.person_id = tmp.person_id
join patient_encounter b on a.person_id = b.person_id
join provider_mstr c on b.rendering_provider_id = c.provider_id
join person_payer d on tmp.person_id = d.person_id
where c.description = 'Leon MD, Enrique'
group by c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,tmp.last_name,
tmp.date_of_birth,d.payer_name,b.create_timestamp
)tmp2
where year(create_timestamp) IN (2005,2006)
group by person_nbr,payer_name,first_name,last_name,description,create_timestamp

Thanks in Advance!
Sherri

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-13 : 17:29:17

Replace PK with the Primary key of the patient_encounter table..


With PersonMIA (person_id,person_nbr,first_name,last_name,date_of_birth) as
(
	select distinct person_id,person_nbr,first_name,last_name,date_of_birth 
	from
	(
		select count(*) as countenc,a.person_id,a.person_nbr,
		a.first_name,a.last_name,
		a.date_of_birth
		from person a
		join patient_encounter b on a.person_id = b.person_id
		Where b.[PK]  =
				 (
					Select Max(C.PK) From patient_encounter c Where b.PersonId =c.PersonId
				 )
		group by a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth 
	)tmp
	where tmp.countenc <=1
)

Chirag

http://www.chirikworld.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 11:30:46

Try like this:-

With PersonMIA (person_id,person_nbr,first_name,last_name,date_of_birth) as
(
select distinct person_id,person_nbr,first_name,last_name,date_of_birth from
(select count(*) as countenc,a.person_id,a.person_nbr,
a.first_name,a.last_name,
a.date_of_birth
from person a
join patient_encounter b on a.person_id = b.person_id
group by a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth )tmp
where tmp.countenc <=1
)
select person_nbr,payer_name,first_name,last_name,description,year(create_timestamp),create_timestamp
from
(
select distinct c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,
tmp.last_name,tmp.date_of_birth,d.payer_name,b.create_timestamp
from PersonMIA tmp 
join person a on a.person_id = tmp.person_id
join patient_encounter b on a.person_id = b.person_id
join provider_mstr c on b.rendering_provider_id = c.provider_id
cross apply(select top 1 payer_name
            from person_payer 
            where person_id = tmp.person_id
            order by payer_id) d 
where c.description = 'Leon MD, Enrique'
group by c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,tmp.last_name,
tmp.date_of_birth,d.payer_name,b.create_timestamp
)tmp2
where year(create_timestamp) IN (2005,2006)
group by person_nbr,payer_name,first_name,last_name,description,create_timestamp

I'm assuming payer_id is pk of person_payer table. replace it with your actual column.

sross81
Posting Yak Master

228 Posts

Posted - 2008-06-16 : 11:34:18

Thanks visakh16 as always you are such a big help. I knew there had to be a way to do that. I am definitely saving that code for future reference :).

quote:

Originally posted by visakh16

Try like this:-

With PersonMIA (person_id,person_nbr,first_name,last_name,date_of_birth) as
(
select distinct person_id,person_nbr,first_name,last_name,date_of_birth from
(select count(*) as countenc,a.person_id,a.person_nbr,
a.first_name,a.last_name,
a.date_of_birth
from person a
join patient_encounter b on a.person_id = b.person_id
group by a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth )tmp
where tmp.countenc <=1
)
select person_nbr,payer_name,first_name,last_name,description,year(create_timestamp),create_timestamp
from
(
select distinct c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,
tmp.last_name,tmp.date_of_birth,d.payer_name,b.create_timestamp
from PersonMIA tmp 
join person a on a.person_id = tmp.person_id
join patient_encounter b on a.person_id = b.person_id
join provider_mstr c on b.rendering_provider_id = c.provider_id
cross apply(select top 1 payer_name
            from person_payer 
            where person_id = tmp.person_id
            order by payer_id) d 
where c.description = 'Leon MD, Enrique'
group by c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,tmp.last_name,
tmp.date_of_birth,d.payer_name,b.create_timestamp
)tmp2
where year(create_timestamp) IN (2005,2006)
group by person_nbr,payer_name,first_name,last_name,description,create_timestamp

I'm assuming payer_id is pk of person_payer table. replace it with your actual column.

Thanks in Advance!
Sherri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 11:38:58

quote:

Originally posted by sross81

Thanks visakh16 as always you are such a big help. I knew there had to be a way to do that. I am definitely saving that code for future reference :).

quote:

Originally posted by visakh16

Try like this:-

With PersonMIA (person_id,person_nbr,first_name,last_name,date_of_birth) as
(
select distinct person_id,person_nbr,first_name,last_name,date_of_birth from
(select count(*) as countenc,a.person_id,a.person_nbr,
a.first_name,a.last_name,
a.date_of_birth
from person a
join patient_encounter b on a.person_id = b.person_id
group by a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth )tmp
where tmp.countenc <=1
)
select person_nbr,payer_name,first_name,last_name,description,year(create_timestamp),create_timestamp
from
(
select distinct c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,
tmp.last_name,tmp.date_of_birth,d.payer_name,b.create_timestamp
from PersonMIA tmp 
join person a on a.person_id = tmp.person_id
join patient_encounter b on a.person_id = b.person_id
join provider_mstr c on b.rendering_provider_id = c.provider_id
cross apply(select top 1 payer_name
            from person_payer 
            where person_id = tmp.person_id
            order by payer_id) d 
where c.description = 'Leon MD, Enrique'
group by c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,tmp.last_name,
tmp.date_of_birth,d.payer_name,b.create_timestamp
)tmp2
where year(create_timestamp) IN (2005,2006)
group by person_nbr,payer_name,first_name,last_name,description,create_timestamp

I'm assuming payer_id is pk of person_payer table. replace it with your actual column.

Thanks in Advance!
Sherri

You're always welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources