SubQuery on Same table - 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 2008 Forums

Transact-SQL (2008)

SubQuery on Same table

Author

Topic

dnagahawatte
Starting Member

24 Posts

Posted - 2011-02-14 : 12:42:46

Guys
I have a query runs on a join.
Personnel Table and Cost Table, there is a group by clause too.
I want do do a Subqueries with different WHERE clauses so i can get different kinds of COSTS.
So how can i pass the Persnnel_Name to the sub query so it aligns with the correct Personnel correctly.
========================
Something like ........

select Personnel.Pers_Name ,sum(Cost1) as 'cost1',

(select sum(cost1) as 'cost2'

FROM Costs right outer join Personnel
ON Costs.Allocated_To = Personnel.Pers_Ref

WHERE
Pers_Leaver=0 and Pers_Department_Code='LON'
and Costs.t in ('R')
and PP1.Pers_Name =PP2.Pers_Name

) AS 'LON COST'

FROM Costs right outer join Personnel
ON Costs.Allocated_To = Personnel.Pers_Ref

group by Personnel.Pers_Name

==========================
PP1.Pers_Name =PP2.Pers_Name LINE is what i cant figure out....

Appreciate your help..

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-14 : 13:22:02

You don't need subqueries. Use Case when to sum the value based on conditions:



select   p.Pers_Name 
		,sum(Cost1) as Cost1,
		,Sum(Case WHEN Pers_Leaver = 0 and 
					   Pers_Department_Code = 'LON'
					   c.t = 'R'
				  THEN cost1 else 0 end) as Cost2
					 
FROM Costs c right outer join  
ON  c.Allocated_To = p.Pers_Ref

group by p.Pers_Name

Poor planning on your part does not constitute an emergency on my part.

dnagahawatte
Starting Member

24 Posts

Posted - 2011-02-15 : 10:11:42

Lovely! Thank you, All sorted.
I now have the challenge of getting another figure from
another table(NON-Productive) join up with Personnel Table

How can i possibly do that? I can do a Union but then too much work at the Report Level.

Can you please help?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-15 : 11:16:26

You can join personnel table to another table if they have a valid link.

Poor planning on your part does not constitute an emergency on my part.

dnagahawatte
Starting Member

24 Posts

Posted - 2011-02-15 : 12:34:23

Can i do it as a subquery as i have initially suggested? Appreciate ur help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources