Nested Select Statement Top (1)

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)

Nested Select Statement Top (1)

Author

Topic

dmarsden
Starting Member

13 Posts

Posted - 2011-09-30 : 14:59:06

Hi. I have been asked to develop Access and Utilization reports for our center. I am trying to write a report that pulls out a client's "Intake" date and date of next service. I have tried writing a SQL stored procedure that creates a temp table from which I can write a report. I am having trouble getting the dates I need. Here are my insert and select statements:

insert into #Access(Client_OID, Client_ID, Client_LName, Client_FName, Client_Bdate, Client_SSN, Epis_AdmitDate, Epis_DCDate,
CC_FName, CCG_AdmitDate, CCG_DCDate, IntakeDate)

select Client_OID = Client_OID, Client_ID = Client_ID, Client_LName = Client_LName, Client_FName = Client_FName,
Client_BDate = Client_BDate, Client_SSN = Client_SSN, Epis_AdmitDate = Epis_AdmitDate, Epis_DCDate = Epis_DCDate, CC_FName = CC_FName, CCG_AdmitDate = CCG_AdmitDate, CCG_DCDate = CCG_DCDate, IntakeDate = (
select top (1) RS_StartTime from PMHC_DM_V_Client_To_RecSer
where RS_SerItem = 'Intake' and RS_VoidDate is null and Client_OID = PMHC_DM_V_Client_To_RecSer.Client_OID
order by RS_StartTime desc),
NextAppt = (select top (1) RS_StartTime from PMHC_DM_V_Client_To_RecSer
where RS_SerItem <> 'Intake' and RS_VoidDate is null and Client_OID = PMHC_DM_V_Client_To_RecSer.Client_OID
order by RS_StartTime asc)

from PMHC_DM_V_Client_To_RecSer

select * from #access

The select statement returns the very last "Intake" overall and the very first service overall. I was looking for it to return the very last "Intake" for each particular client and the very first service for each client. I hope that makes sense. Any help would be appreciated. Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-09-30 : 16:07:55

What is the error message you are getting? it looks like your insert list has fewer columns than your select list. You need to add a NextAppt date column to #Access.

dmarsden
Starting Member

13 Posts

Posted - 2011-09-30 : 16:20:34

Yeah. Sorry. I pulled it out while I was trying to get the query to work. My issue is the data it returns. In particular the two nested select statements:

IntakeDate = (
select top (1) RS_StartTime from PMHC_DM_V_Client_To_RecSer
where RS_SerItem = 'Intake' and RS_VoidDate is null and Client_OID = PMHC_DM_V_Client_To_RecSer.Client_OID
order by RS_StartTime desc),

NextAppt = (
select top (1) RS_StartTime from PMHC_DM_V_Client_To_RecSer
where RS_SerItem <> 'Intake' and RS_VoidDate is null and Client_OID = PMHC_DM_V_Client_To_RecSer.Client_OID
order by RS_StartTime asc)

I had expected them to return values that were linked to the Client_OID. What happens is the query returns the very last "Intake" in the table and the very first service, regardless of the client. It just repeats these two dates throughout the rows. In other words, it selects the Top (1) regardless of the other data in the row. I had thought adding

where Client_OID = PMHC_DM_V_Client_To_RecSer.OID

would have it select top (1) relative to the Client_OID. Thanks again for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-01 : 15:08:45

give an alias and try


insert into #Access(Client_OID, Client_ID, Client_LName, Client_FName, Client_Bdate, Client_SSN, Epis_AdmitDate, Epis_DCDate,
CC_FName, CCG_AdmitDate, CCG_DCDate, IntakeDate)

select Client_OID = Client_OID, Client_ID = Client_ID, Client_LName = Client_LName, Client_FName = Client_FName,
Client_BDate = Client_BDate, Client_SSN = Client_SSN, Epis_AdmitDate = Epis_AdmitDate, Epis_DCDate = Epis_DCDate, CC_FName = CC_FName, CCG_AdmitDate = CCG_AdmitDate, CCG_DCDate = CCG_DCDate, IntakeDate = (
select top (1) RS_StartTime from PMHC_DM_V_Client_To_RecSer
where RS_SerItem = 'Intake' and RS_VoidDate is null and Client_OID = a.Client_OID
order by RS_StartTime desc), 
NextAppt = (select top (1) RS_StartTime from PMHC_DM_V_Client_To_RecSer
where RS_SerItem <> 'Intake' and RS_VoidDate is null and Client_OID = a.Client_OID
order by RS_StartTime asc)


from PMHC_DM_V_Client_To_RecSer a

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dmarsden
Starting Member

13 Posts

Posted - 2011-10-02 : 10:55:51

Thanks. That did it. Just for future referrence, why did it work? Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-02 : 13:44:42

because i gave the outer table alias a and used it in subquery it was clear to do filtering based on Client_OID value returned from main query. In your original query the lack of alias caused ambiguity and it regarded Client_OID = PMHC_DM_V_Client_To_RecSer.Client_OID both side column to be from same table given inside subquery and hence that became a trivial condition like 1=1 and resulted in returned wrong values which is always top 1 from table PMHC_DM_V_Client_To_RecSer

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dmarsden
Starting Member

13 Posts

Posted - 2011-10-03 : 09:14:56

Got it. Thanks for the exp[lanation. Makes sense. Have a good one.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 12:06:08

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources