First Instance 1

[LT2]

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.

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

 

[gmmastros]

Try this:

Select ID, Client, MinNum, Agency, AdmitDate
From   (
       select ac.ID, 
              (dg.Last+ ' '+dg.First)as 'Client',  
			  adm.AdmitNum As MinNum,
       	      ag.Agency, 
       	      adm.AdmitDate,
       	      Row_Number() Over (Partition By ac.ID Order By adm.AdmitDate) As RowId
       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'
       ) As Results
Where  Results.RowId = 1

If this works, and you would like me to explain, let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[LT2]

Yes George, this worked! Please explain.

Lynn

 

[gmmastros]

Lynn,

The key to this query is this line:

Row_Number() Over (Partition By ac.ID Order By adm.AdmitDate) As RowId

This line will return a Row Number for each row. Row Numbers start at 1 and increase by 1 for each row.

"Partition By ac.ID" indicates that the row numbering should restart every time the ac.ID value changes.

"Order By adm.AdmitDate" means that the value with the lowest (earliest) adm.AdmitDate should be 1. The next admit date would be 2, etc.....

To get a better picture of how this works, run just the inner query (the derived table), with an order by clause added in.

       select ac.ID, 
              (dg.Last+ ' '+dg.First)as 'Client',  
			  adm.AdmitNum As MinNum,
       	      ag.Agency, 
       	      adm.AdmitDate,
       	      Row_Number() Over (Partition By ac.ID Order By adm.AdmitDate) As RowId
       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'
       Order By ac.ID, adm.AdmitDate

You'll see that there are multiple rows for some of the ac.ID's, but in each case, there will be a RowId column that starts at 1 and increments by 1 for each ac.ID.

The last bit is just a little kludgy workaround for the language. You cannot directly filter on the RowId column so you need to use a derived table to allow the filter on RowId = 1.

Does it make sense now?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[LT2]

Yes! This is really AWESOME! I have been struggling with giving people admission stats without being able to segregate. Thanks so much!

Now I've got another question to make this more complex and I don't know if this is doable. By left joining tSaDisch dis on adm.AdmitNum = dis.AdmitNum to get dis.DishNum and dis.DishDate. Is there a way to show the first instance of admit with the last instance of discharge?

Typically, a consumer will have many admits and discharges as they move up and down in modalities. This is more like entering in and out of programs. What I would like to be able to do is gather data from the first admit and the very last discharge to show the outcomes of an episode.

Does this make any sense?

Lynn

 

[gmmastros]

I'm having a problem visualizing how the data relates to each other. Can you provide some sample data and expected results?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[LT2]

ID Client MinNum Agency AdmitDate Program DishNum DishDate
1 Mutt, Tim 173038 TomCat 11/28/2012 Detox 12345 12/1/2012
2 Pluto, Jim 172161 HawkInn 10/2/2012 Outpatient 78965 11/30/2012
3 Mouse, Mickey 172948 BeaverDen 11/19/2012 Res-ST NULL NULL
4 Duck, Donald 172711 SoupsOn 11/3/2012 Detox 96325 11/5/2012
4 Duck, Donald 172730 SoupsOn 11/5/2012 Res-ST 87512 12/13/2012

Donald has two admits and in the results I would like to show the details of the first instance admit and the details of the last discharge (not the one in between) so that only one line appears for each client.

I apologize, I do not know how to make these columns clean for you.

Lynn

 

[gmmastros]

For donald duck, we have:

4	Duck, Donald	172711	SoupsOn	 11/3/2012 Detox	96325	11/5/2012
4	Duck, Donald	172730	SoupsOn	 11/5/2012 Res-ST	87512	12/13/2012

Looks like Program = Detox for one row, and Res-ST for another row. You want only 1 line for each client, so what should be returned for Program?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[LT2]

George,

This post was extremely valuable to me, so thank you!!!!