Hello,
I am using SQL Svr 2005 and looking to get the first instance of an admit reqardless of how many may be out there for particular clients who meet my criteria.
Below is my query.
The results include an entry for someone twice and I only want the first instance. How do I make this happen?
ID, Client, MinNum, Agency, AdmissionDate
4 Duck, Donald 172711 SoupsOn 11/3/2012 0:00
4 Duck, Donald 172730 SoupsOn 11/5/2012 0:00
I am using SQL Svr 2005 and looking to get the first instance of an admit reqardless of how many may be out there for particular clients who meet my criteria.
Below is my query.
Code:
select distinct ac.ID, (dg.Last+ ' '+dg.First)as 'Client', min(adm.AdmitNum)MinNum,
ag.Agency, adm.AdmitDate
from tAgClient ac
inner join tAgency ag on ac.AgNum = ag.AgNum
inner join dbo.tDemog dg on ac.ClientNum = dg.ClientNum
inner join dbo.tDemogAddr dga on dg.ClientNum = dga.ClientNum
inner join tSaAdmit adm on ac.ClientNum = adm.ClientNum
where dga.ValidFrom >= 10/1/2012'
and ag.TestAgency = 0
and dg.SBClient = 1
and adm.AdmissionDate >= '10/1/2012'
group by ac.ID, dg.Last, dg.First, adm.AdmitNum, Agency, adm.AdmitDate
The results include an entry for someone twice and I only want the first instance. How do I make this happen?
ID, Client, MinNum, Agency, AdmissionDate
4 Duck, Donald 172711 SoupsOn 11/3/2012 0:00
4 Duck, Donald 172730 SoupsOn 11/5/2012 0:00