self join question

[Veejc]

I think what I need is a self join, I'm just uncertain how to structure this. Here is the english.

I have one table. From that table I want

member name
member rx label
EARLIEST RX date
LATEST PRESCRIBING DOC

this information is found in several different records in the same table, but in the end I want only one line per member per rx label with the earlies rx date and the latest prescribing doc

Like I said, I think I can accomplish this with a self join, but I'm not sure how. Can anyone help?

 

[DotNetGnat]

Try This:

SELECT T1.name,T1.label,T1.rxdate, T1.pdoc from mytable T1
WHERE t1.rxdate IN ( Select Top 1 T2.rxdate from mytable T2 WHERE T1.name=T2.name Order By t2.rxdate ASC)

How do you identify the latest doc???

-DNG

 

[Veejc]

by looking at all of the rx dates and returning the doc that corresponds to the max(rxdate), that is the part that I am having the most trouble with, so I could have a scenario like this

Joe Smith Aspirin 1/1/2005 Dr. Jones
Joe Smith Aspirin 1/5/2005 Dr. Smith
Joe Smith Aspirin 2/1/2005 Dr. Mary

So for the one record I'd like to see, it would be

Joe Smith Aspirin 1/1/2005 Dr. Mary

the earliest rx date and the doc that belongs to the latest rx date

does that make sense?

 

[JamesLean]

Try this:

SELECT name,
	label,
	MIN(date) AS earliestdate,
	(
		SELECT doc FROM table
		WHERE name = t1.name
			AND label = t1.label
			AND date = (
					SELECT MAX(date) FROM table
					WHERE name = t1.name
						AND label = t1.label
				)
	) AS latestdoc
FROM table t1
GROUP BY name, label

--James

 

[Veejc]

I just tried something very similar but I don't think I am accounting for all criteria that I need to be because I am seeing occassional dup meds for the same member... I've trimmed everything, too to make sure I'm not just seeing it b/c of extra spaces, etc...

select member.AALASTNM, member.aafname, rx.labelname as [memberrx],
--rx.labelname as [Current Medication],
min(dispdate) as earliest_date,
-- this gets the most recent deano
(select deano from pha.dbo.tbl_paidclaims where dispdate =
( select max(dispdate)
from pha.dbo.tbl_paidclaims where subsid = rx.subsid and persno = rx.persno
and labelname = rx.labelname
group by labelname )
and subsid = rx.subsid and persno= rx.persno
and labelname = rx.labelname
group by deano ) as [most recent ] ,
deamap.ppfname as [Prescribing First Name],
deamap.pplname as [Prescribing Last Name]
from pha.dbo.tbl_paidclaims as rx
left join nhpri_diam_ds01.dbo.tbl_CurrentMemberDimension as member
on left(rx.subsid,12) = member.aasubno and left(rx.persno,2) = member.aapersno
join nhpri_diam_ds01.dbo.rxphys as deamap
on deamap.deano = rx.deano
where member.eligibility_flag = 1
and rx.groupno in ('5100', '5211')
group by member.aalastnm, member.aafname, rx.subsid, rx.persno,
rx.labelname, rx.dob, rx.deano, deamap.ppfname,
deamap.pplname, rx.groupno

Can you tell me if you think you can see what I may be missing?

 

[Veejc]

ok, I've modified this a little bit to put the subquery in the ON clause instead.

Does this not makes sense to do?

select member.AALASTNM, member.aafname, rtrim(ltrim(rx.labelname)) as [memberrx],
--rx.labelname as [Current Medication],
min(dispdate) as earliest_date,
deamap.ppfname as [Prescribing First Name],
deamap.pplname as [Prescribing Last Name]
from pha.dbo.tbl_paidclaims as rx
left join nhpri_diam_ds01.dbo.tbl_CurrentMemberDimension as member
on left(rx.subsid,12) = member.aasubno and left(rx.persno,2) = member.aapersno
join nhpri_diam_ds01.dbo.rxphys as deamap
on deamap.deano =

HERE'S WHERE I NOW HAVE THE SUBQUERY--------------
(select deano from pha.dbo.tbl_paidclaims where dispdate =
( select max(dispdate)
from pha.dbo.tbl_paidclaims
where subsid = rx.subsid and persno = rx.persno
and rtrim(ltrim(labelname)) = rtrim(ltrim(rx.labelname))
group by rtrim(ltrim( labelname )))
and subsid = rx.subsid and persno= rx.persno
and rtrim(ltrim( labelname )) = rtrim(ltrim(rx.labelname))
group by deano )

----------------------------------END SUBQUERY

where member.eligibility_flag = 1
and rx.groupno in ('5100', '5211')
group by member.aalastnm, member.aafname, rx.subsid, rx.persno,
rtrim(ltrim( rx.labelname)), rx.dob, rx.deano, deamap.ppfname,
deamap.pplname, rx.groupno
order by member.aalastnm asc, member.aafname asc

 

[Catadmin]

Veejc,

Try swapping around your select statement so that the meds are first in the select list and use the DISTINCT keyword with it. If you're still getting duplicate name/meds records, the reason is you're pulling another field(s) that are unique, which makes the entire recordset unique, even though the name & med label are the same. At that point, the only way to get a truly distinct record set is to pare off the unique fields until you get the distinct name/meds combination you want.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[Veejc]

I had that in the beginning and that didn't make much difference in eliminating the dups, but here is what I did that DID give me the results that I was after, just in case someone is interested for any future issues...

select member.aalastnm, member.aafname, rx.labelname,
--rx.labelname as [Current Medication],
cast(min(dispdate) as varchar) as earliest_date,
deamap.ppfname as [Prescribing First Name],
deamap.pplname as [Prescribing Last Name],
member.abgrup as [Group Number],
member.absubno + '-' + member.abpersno as [Member ID],
member.aassan as [SSN],
cast(member.aadob as varchar) as [Date of Birth]

from pha.dbo.tbl_paidclaims as rx
left join nhpri_diam_ds01.dbo.tbl_CurrentMemberDimension as member
on left(rx.subsid,12) = member.aasubno and left(rx.persno,2) = member.aapersno
join nhpri_diam_ds01.dbo.rxphys as deamap
on deamap.deano =
(select deano from pha.dbo.tbl_paidclaims where dispdate =
( select max(dispdate)
from pha.dbo.tbl_paidclaims
where subsid = rx.subsid and persno = rx.persno
and rtrim(ltrim(labelname)) = rtrim(ltrim(rx.labelname))
group by rtrim(ltrim( labelname )))
and subsid = rx.subsid and persno= rx.persno
and rtrim(ltrim( labelname )) = rtrim(ltrim(rx.labelname))
group by deano )

where member.eligibility_flag = 1
and rx.groupno in ('5100', '5211')
group by member.AALASTNM, member.aafname, rx.labelname, deamap.ppfname,
deamap.pplname,
member.abgrup,
member.absubno, member.abpersno,
member.aassan,
member.aadob

order by member.AALASTNM + ', '+ member.aafname + ' ' + rtrim(ltrim(rx.labelname))

Thanks to everyone who repsonded, your input was valuable to me.