Join Query assitance - 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)

Join Query assitance

Author

Topic

sunilsi
Starting Member

21 Posts

Posted - 2008-12-15 : 23:39:01

Friends,

I've the following tables out of which have to get some rows based on the condition given below.

Utilization
--------------
Asso_ID
Prj_ID
Available_Bill_Hr
Month

Allocation
--------------
Asso_ID
Prj_ID
Ass_Name
Prj_Name
Type

AdditionalDetails
------------------------
Asso_ID
Prj_ID
TotalHr

I need to select Asso_ID, Prj_ID, Available_Bill_Hr, ( Available_Bill_Hr - TotalHr ) from the above tables satisfying the conditions (Utilization.Month = 11 && Allocation.Type = 'Contract').. I tried it some, but couldn't able to make it.

Please help.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-15 : 23:50:05

select u.Asso_ID,u.Prj_ID,u.Available_Bill_Hr,( u.Available_Bill_Hr - ad.totalhr) FROM Utilization u
JOIN
Allocation a on a.Asso_ID = u.Asso_ID and a.Prj_ID = u.Prj_ID AND a.type = 'contract'
JOIN
AdditionalDetails ad on ad.Asso_ID = u.Asso_ID and ad.Prj_ID = u.Prj_ID
where
u.month = 11

sunilsi
Starting Member

21 Posts

Posted - 2008-12-16 : 00:20:12

Thanks, one more clarification...

If I need to get the sum of Available_Bill_Hr of the Asso_ID (i.e for the billable hr os all the projects that Asso_ID is assigned with) in additional to the above fields, how can be the query?

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-16 : 00:29:05

select u.Asso_ID,u.Prj_ID,u.Available_Bill_Hr,(u.Available_Bill_Hr - ad.TotalHr )
from Utilization as u
inner join AdditionalDetails as ad on ( ad.Asso_ID = u.Asso_ID )
inner join Allocation as a on ( a.asso_id = u.asso_id )
where
u.month = 11 and a.type = 'Contract'

I Struggle For Excellence

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-16 : 00:29:39

quote:
Originally posted by sunilsi

Thanks, one more clarification...

If I need to get the sum of Available_Bill_Hr of the Asso_ID (i.e for the billable hr os all the projects that Asso_ID is assigned with) in additional to the above fields, how can be the query?

select u.Asso_ID,u.Prj_ID,SUM(u.Available_Bill_Hr),( u.Available_Bill_Hr - ad.totalhr) FROM Utilization u
JOIN
Allocation a on a.Asso_ID = u.Asso_ID and a.Prj_ID = u.Prj_ID AND a.type = 'contract'
JOIN
AdditionalDetails ad on ad.Asso_ID = u.Asso_ID and ad.Prj_ID = u.Prj_ID
where
u.month = 11
group by u.Asso_ID,u.Prj_ID,( u.Available_Bill_Hr - ad.totalhr)

sunilsi
Starting Member

21 Posts

Posted - 2008-12-16 : 04:35:39

yes, thanks friend... it is working :-)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-16 : 05:04:48

quote:
Originally posted by sunilsi

yes, thanks friend... it is working :-)

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources