Joining to aggregated set - 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 2012 Forums

Transact-SQL (2012)

Joining to aggregated set

Author

Topic

SQLS33ker
Starting Member

16 Posts

Posted - 2015-01-08 : 13:55:09

Hi there,

I am trying to join my main set to an aggregated set(top part). Please see code below:


select concat(subquery.pataccountnumber, subquery.last_visit) test2 from

(SELECT a.[PatAccountNumber]      
         ,max(b.Sequence) last_Visit
       
  FROM [Visits] as a  
  inner join  [ServiceChange] as b      
  on a.URN = b.URN and a.SYS_SystemID = b.SYS_SystemID 
  where a.DisTimeDateTime between '12-01-2014' and '01-06-2015'      and a.Fac in ('AAA')      
  group by a.PatAccountNumber) as subquery

  inner join 

  (SELECT 
         a.[PatAccountNumber] 
         ,b.Sequence
         ,CONCAT(a.PatAccountNumber, b.Sequence) as linked_keyyy
       
  FROM [Visits] as a  
  inner join  [ServiceChange] as b      
  on a.URN = b.URN and a.SYS_SystemID = b.SYS_SystemID 
   
  where a.DisTimeDateTime between '12-01-2014' and '01-06-2015'      
  and a.Fac in ('AAA')) as subquery2

  on subquery.test2 = subquery2.linked_keyyy

It fails by saying test2 is an invalid column. I wonder if I am not representing the first aggregated set as a table properly.

Can anyone help?

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-08 : 14:23:33

your subquery does not have a column called 'test2'. Your subquery only select two columns: PatAccountNumber and last_Visit.

SQLS33ker
Starting Member

16 Posts

Posted - 2015-01-08 : 16:06:36

quote:
Originally posted by gbritton

your subquery does not have a column called 'test2'. Your subquery only select two columns: PatAccountNumber and last_Visit.

how do I make a join to my subsequent main set from this?


select concat(subquery.pataccountnumber, subquery.last_visit) test2 from

(SELECT a.[PatAccountNumber]      
         ,max(b.Sequence) last_Visit
       
  FROM [Visits] as a  
  inner join  [ServiceChange] as b      
  on a.URN = b.URN and a.SYS_SystemID = b.SYS_SystemID 
  where a.DisTimeDateTime between '12-01-2014' and '01-06-2015'      and a.Fac in ('AAA')      
  group by a.PatAccountNumber) as subquery

?

I need to join the test2 to subquery2.linked_keyyy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-08 : 16:50:28

JOIN occurs before SELECT, so test2 doesn't exist yet.

This should work:
on subquery.pataccountnumber + subquery.last_visit = subquery2.linked_keyyy

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources