Help With Subquerying - 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)

Help With Subquerying

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-16 : 13:25:25

Hello I am trying to create a query that references multiple queries. This is what I am doing:

Set DATEFORMAT ymd
(select Distinct person_id,first_name,last_name,DOB From
(select a.person_id,a.first_name,a.last_name,
cast(a.date_of_birth as datetime) as DOB, a.create_timestamp as PCD,
b.enc_timestamp as DOS
from person a
join patient_encounter b on a.person_id = b.person_id
where b.enc_timestamp >= '2008-01-01' and b.enc_timestamp <= '2008-05-16'
)t
where
t.DOB >= '2006-03-01' and t.DOB <= '2006-05-16' and
t.PCD <= '2008-03-01' )
select t.person_id,c.service_item_id as ServiceItem, count(*) as CountProc,
c.service_item_desc
from t
join patient_procedure c on t.person_id = c.person_id
where t.ServiceItem IN
('90744','90713','90701','90700','90707','90648','90716','90669')
order by t.person_id

The part I made bold is the part I am having trouble with. When I try to reference T which is the letter I gave my subquery it says that its invalid. Is there another way to reference the data in the first sub query. I just want to make sure that I only am getting the services that were for the distinct list of persons I got in the first part of my query.

Thanks in advance!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-16 : 13:28:10

Join to your subquery so that it is now a derived table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-16 : 13:29:22

Ok that makes sense but how do I set up that join? Thanks.

quote:
Originally posted by tkizer

Join to your subquery so that it is now a derived table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-16 : 13:29:25

Use a CTE like this:-

Set DATEFORMAT ymd
;
With Your_CTE (person_id,first_name,last_name,DOB) AS
(
select Distinct person_id,first_name,last_name,DOB From
(select a.person_id,a.first_name,a.last_name, 
cast(a.date_of_birth as datetime) as DOB, a.create_timestamp as PCD,
b.enc_timestamp as DOS
from person a
join patient_encounter b on a.person_id = b.person_id
where b.enc_timestamp >= '2008-01-01' and b.enc_timestamp <= '2008-05-16'
)t
where 
t.DOB >= '2006-03-01' and t.DOB <= '2006-05-16' and
t.PCD <= '2008-03-01' 
)
select t.person_id,c.service_item_id as ServiceItem, count(*) as CountProc,
c.service_item_desc
from Your_CTE t
join patient_procedure c on t.person_id = c.person_id
where t.ServiceItem IN 
('90744','90713','90701','90700','90707','90648','90716','90669') 
order by t.person_id

or join like this


select tmp.person_id,c.service_item_id as ServiceItem, count(*) as CountProc,
c.service_item_desc
from (select Distinct person_id,first_name,last_name,DOB From
(select a.person_id,a.first_name,a.last_name, 
cast(a.date_of_birth as datetime) as DOB, a.create_timestamp as PCD,
b.enc_timestamp as DOS
from person a
join patient_encounter b on a.person_id = b.person_id
where b.enc_timestamp >= '2008-01-01' and b.enc_timestamp <= '2008-05-16'
)t
where 
t.DOB >= '2006-03-01' and t.DOB <= '2006-05-16' and
t.PCD <= '2008-03-01')tmp
join patient_procedure c on tmp.person_id = c.person_id
where tmp.ServiceItem IN 
('90744','90713','90701','90700','90707','90648','90716','90669') 
order by t.person_id

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-16 : 13:31:20

When I run the CTE code exactly as you gave me I get this error:

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "t.person_id" could not be bound.

Was I supposed to change something?

quote:

Originally posted by visakh16

Use a CTE like this:-

Set DATEFORMAT ymd
;
With Your_CTE (person_id,first_name,last_name,DOB) AS
(
select Distinct person_id,first_name,last_name,DOB From
(select a.person_id,a.first_name,a.last_name, 
cast(a.date_of_birth as datetime) as DOB, a.create_timestamp as PCD,
b.enc_timestamp as DOS
from person a
join patient_encounter b on a.person_id = b.person_id
where b.enc_timestamp >= '2008-01-01' and b.enc_timestamp <= '2008-05-16'
)t
where 
t.DOB >= '2006-03-01' and t.DOB <= '2006-05-16' and
t.PCD <= '2008-03-01' 
)
select t.person_id,c.service_item_id as ServiceItem, count(*) as CountProc,
c.service_item_desc
from Your_CTE
join patient_procedure c on t.person_id = c.person_id
where t.ServiceItem IN 
('90744','90713','90701','90700','90707','90648','90716','90669') 
order by t.person_id

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-16 : 13:32:46

If I understand what you are doing, you need to replace T in the bold query with the derived table T fromthe first query. For example:

select 
	t.person_id,
	c.service_item_id as ServiceItem, 
	count(*) as CountProc,
	c.service_item_desc
from
	(
		SELECT 
			a.person_id,
			a.first_name,
			a.last_name, 
			cast(a.date_of_birth as datetime) as DOB, 
			a.create_timestamp as PCD,
			b.enc_timestamp as DOS
	FROM 
		person a
	JOIN 
		patient_encounter b 
		ON a.person_id = b.person_id
	WHERE 
		b.enc_timestamp >= '2008-01-01' 
		AND b.enc_timestamp <= '2008-05-16'
	) AS T
JOIN 
	patient_procedure c 
	on t.person_id = c.person_id
where 
	t.ServiceItem IN ('90744','90713','90701','90700','90707','90648','90716','90669') 
order by 
	t.person_id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-16 : 13:33:26

quote:
Originally posted by sross81

When I run the CTE code exactly as you gave me I get this error:

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "t.person_id" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "t.ServiceItem" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "t.ServiceItem" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "t.ServiceItem" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "t.ServiceItem" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "t.ServiceItem" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "t.ServiceItem" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "t.ServiceItem" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "t.ServiceItem" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "t.person_id" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "t.person_id" could not be bound.

Was I supposed to change something?

quote:
Originally posted by visakh16

Use a CTE like this:-
Set DATEFORMAT ymd
;
With Your_CTE (person_id,first_name,last_name,DOB) AS
(
select Distinct person_id,first_name,last_name,DOB From
(select a.person_id,a.first_name,a.last_name,
cast(a.date_of_birth as datetime) as DOB, a.create_timestamp as PCD,
b.enc_timestamp as DOS
from person a
join patient_encounter b on a.person_id = b.person_id
where b.enc_timestamp >= '2008-01-01' and b.enc_timestamp <= '2008-05-16'
)t
where
t.DOB >= '2006-03-01' and t.DOB <= '2006-05-16' and
t.PCD <= '2008-03-01'
)
select t.person_id,c.service_item_id as ServiceItem, count(*) as CountProc,
c.service_item_desc
from Your_CTE
join patient_procedure c on t.person_id = c.person_id
where t.ServiceItem IN
('90744','90713','90701','90700','90707','90648','90716','90669')
order by t.person_id

i missed out an alias . I've edited the earlier post.

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-16 : 13:35:20

On this one I get an error like this:

Msg 207, Level 16, State 1, Line 28
Invalid column name 'ServiceItem'.

That is the name so I am confused

quote:

Originally posted by Lamprey

If I understand what you are doing, you need to replace T in the bold query with the derived table T fromthe first query. For example:

select 
	t.person_id,
	c.service_item_id as ServiceItem, 
	count(*) as CountProc,
	c.service_item_desc
from
	(
		SELECT 
			a.person_id,
			a.first_name,
			a.last_name, 
			cast(a.date_of_birth as datetime) as DOB, 
			a.create_timestamp as PCD,
			b.enc_timestamp as DOS
	FROM 
		person a
	JOIN 
		patient_encounter b 
		ON a.person_id = b.person_id
	WHERE 
		b.enc_timestamp >= '2008-01-01' 
		AND b.enc_timestamp <= '2008-05-16'
	) AS T
JOIN 
	patient_procedure c 
	on t.person_id = c.person_id
where 
	t.ServiceItem IN ('90744','90713','90701','90700','90707','90648','90716','90669') 
order by 
	t.person_id

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-16 : 13:39:51

Ok that fixed the first error I got but now I am getting this error:

Msg 207, Level 16, State 1, Line 21
Invalid column name 'ServiceItem'.

The name is ServiceItme and the only place that references it says t.ServiceItem right at the very end of the query. I tried changing the alias to a c or having no alias but still get the same error.

quote:

Originally posted by visakh16

quote:

Originally posted by sross81

When I run the CTE code exactly as you gave me I get this error:

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "t.person_id" could not be bound.

Was I supposed to change something?

[quote]Originally posted by visakh16

Use a CTE like this:-

Set DATEFORMAT ymd
;
With Your_CTE (person_id,first_name,last_name,DOB) AS
(
select Distinct person_id,first_name,last_name,DOB From
(select a.person_id,a.first_name,a.last_name, 
cast(a.date_of_birth as datetime) as DOB, a.create_timestamp as PCD,
b.enc_timestamp as DOS
from person a
join patient_encounter b on a.person_id = b.person_id
where b.enc_timestamp >= '2008-01-01' and b.enc_timestamp <= '2008-05-16'
)t
where 
t.DOB >= '2006-03-01' and t.DOB <= '2006-05-16' and
t.PCD <= '2008-03-01' 
)
select t.person_id,c.service_item_id as ServiceItem, count(*) as CountProc,
c.service_item_desc
from Your_CTE
join patient_procedure c on t.person_id = c.person_id
where t.ServiceItem IN 
('90744','90713','90701','90700','90707','90648','90716','90669') 
order by t.person_id

i missed out an alias . I've edited the earlier post.
[/quote

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-16 : 13:42:27

ServiceItem isn't included in the derived table, so you can't use it with the T alias. You also can't use it as ServiceItem from c since ServiceItem is an alias. You could use it in an ORDER BY though, but that's not what you want. So you can either add the column to the derived table or reference the actual column name for c alias.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-16 : 13:46:11

Ok I fixed the Service Item reference as follows if anyone wants to see the finished product its working now. I may still need to do more work with it once I try to add the next piece....theres always something! :)

Set DATEFORMAT ymd
;
With Your_CTE (person_id,first_name,last_name,DOB) AS
(
select Distinct person_id,first_name,last_name,DOB From
(select a.person_id,a.first_name,a.last_name,
cast(a.date_of_birth as datetime) as DOB, a.create_timestamp as PCD,
b.enc_timestamp as DOS
from person a
join patient_encounter b on a.person_id = b.person_id
where b.enc_timestamp >= '2008-01-01' and b.enc_timestamp <= '2008-05-16'
)t
where
t.DOB >= '2006-03-01' and t.DOB <= '2006-05-16' and
t.PCD <= '2008-03-01'
)
select t.person_id,t.first_name,t.last_name,t.DOB,c.service_item_id as ServiceItem, count(*) as CountProc,
c.service_item_desc
from Your_CTE t
join patient_procedure c on t.person_id = c.person_id
where c.service_item_id IN
('90744','90713','90701','90700','90707','90648','90716','90669')
group by t.person_id,t.first_name,t.last_name,t.DOB,c.service_item_id,c.service_item_desc
order by t.person_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources