Preventing 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)

Preventing duplicates

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2009-05-26 : 13:58:29

I have a query that links to a few tables where there are one to many relationships.

Example of one of my tables:
person (person_id)
hiv_flwsheet_ (enc_id (PK), person_id, dt_comp_lab_cbc, txt_lab_cbc_st)

In the hiv_flwsheet each person can have more than one entry. For each person I want only one row returned that shows the last dt_comp_lab_cbc (last date), and last txt_lab_cbc_st (last status). I tried just selecting the max result for each which works for the date but for the status it doesn't work quite right it will bring back the word "due" over the word "completed" instead of the actual last result so I don't think max works the same with a text field. Is there a way I can pull back all the results for the max enc_id which is the primary key of the hiv_flwsheet table and still get the invidual fields I want? Here is an example of my code:

select distinct a.first_name,a.last_name,cast(a.date_of_birth as datetime) as PtDOB,
max(b.dt_comp_lab_cbc) as LastCBCDate,
max(b.txt_lab_cbc_st) as LastCBCStatus
from person a
join hiv_flowsheet_ b on a.person_id = b.person_id
group by a.first_name,a.last_name,cast(a.date_of_birth as datetime)

Thanks so much in advance!!

Thanks in Advance!
Sherri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 14:04:34

[code]
select p.first_name,p.last_name,
cast(p.date_of_birth as datetime) as PtDOB,
t.dt_comp_lab_cbc as LastCBCDate,
t.txt_lab_cbc_st as LastCBCStatus
from person p
inner join (select h.*
from hiv_flwsheet_ h
inner join (select person_id, max(dt_comp_lab_cbc) as latest
from hiv_flwsheet_
group by person_id) h1
on h1.person_id=h.person_id
and h1.latest=h.dt_comp_lab_cbc
)t
on t.person_id=p.person_id
[/code]

sross81
Posting Yak Master

228 Posts

Posted - 2009-05-26 : 14:18:37

Thanks. I think I was able to get the rest of my code to intergrate back into this so far :)

quote:

Originally posted by visakh16


select p.first_name,p.last_name,
cast(p.date_of_birth as datetime) as PtDOB,
t.dt_comp_lab_cbc as LastCBCDate, 
t.txt_lab_cbc_st as LastCBCStatus
from person p
inner join (select h.*
            from hiv_flwsheet_ h
            inner join (select person_id, max(dt_comp_lab_cbc) as latest
                        from hiv_flwsheet_
                        group by person_id) h1
            on h1.person_id=h.person_id
            and h1.latest=h.dt_comp_lab_cbc
            )t
on t.person_id=p.person_id

Thanks in Advance!
Sherri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 14:19:10

so both tables have date fields and you want one with latest date for each person_id?

sross81
Posting Yak Master

228 Posts

Posted - 2009-05-26 : 14:35:56

Here is an example of another table and columns I haved added in where I need the same thing the max date and the status on that date. How can I make it so I also only get the one entry?

select p.first_name,p.last_name,
cast(p.date_of_birth as datetime) as PtDOB,
cast(MAX(CASE WHEN c.appt_date <= getdate() THEN c.appt_date ELSE NULL END) as datetime) as dateoflastvisit,
cast(MIN(CASE WHEN c.appt_date > getdate() THEN c.appt_date ELSE NULL END) as datetime) as dateofnextvisit,
t.dt_comp_lab_cbc as LastCBCDate,
t.txt_lab_cbc_st as LastCBCStatus,
e.last_pap as LastPapDate,
t.txt_lab_pap_st as LastPapStatus
from person p
inner join (select h.*
from hiv_flowsheet_ h
inner join (select person_id, max(dt_comp_lab_cbc) as latest
from hiv_flowsheet_
group by person_id) h1
on h1.person_id=h.person_id
and h1.latest=h.dt_comp_lab_cbc
)t
on t.person_id=p.person_id
join appointments c on t.person_id = c.person_id
join provider_mstr d on c.rendering_provider_id = d.provider_id
join health_maint_ e on t.person_id = e.person_id and t.enc_id =e.enc_id
join disease_mngt_ f on t.person_id = f.person_id and t.enc_id = f.enc_id
where d.description like '%applin%'
group by p.first_name,p.last_name,cast(p.date_of_birth as datetime),
t.dt_comp_lab_cbc,t.txt_lab_cbc_st,e.last_pap,t.txt_lab_pap_st

quote:
Originally posted by visakh16

so both tables have date fields and you want one with latest date for each person_id?

Thanks in Advance!
Sherri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 14:42:05

you need to replace each table with a derived table as shown below


inner join (select h.*
            from yourtable h
            inner join (select person_id, max(datefield) as latest
                        from yourtable
                        group by person_id) h1
            on h1.person_id=h.person_id
            and h1.latest=h.datefield
            )t
on t.person_id=p.person_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources