Sum and Compare 2 columns in 2 Tables

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

General SQL Server Forums

New to SQL Server Programming

Sum and Compare 2 columns in 2 Tables

Author

Topic

spc2000uk
Starting Member

3 Posts

Posted - 2011-04-26 : 05:09:20

I've been struggling with this query for some time and, hopefully, someone can assist me in obtaining the query I seek.....

I have two tables, 'project timesheets' and 'project budgets'. I am trying to write in one query the code that will sum the 'hours' columns in each table and compare the sums to return only projects that exceed their budget.

A reduced structure of the tables look like this:

'Project Timesheets'

Project ID, Worked Hours
1 10
1 5
2 12
1 2

'Project Budgets'

Project ID, Budgeted Hours
1 5
1 6
2 20

What I'm looking for from the query is:

Project ID, Worked Hours, Budget Hours
1 17 11
2 12 20 ***

*** This row would not be returned as the budget hours are greater that the worked hours.

Any help appreciated

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-26 : 05:59:21

select timesheets.ProjectId,timesheets.WorkedHours,budgets.BudgetedHours
from
(select ProjectId, sum(WorkedHours) as WorkedHours from ProjectTimesheets group by ProjectId)timesheets
join
(select ProjectId, sum(BudgetedHours) as BudgetedHours from ProjectBudgets group by ProjectId)budgets
on timesheets.ProjectId = budgets.ProjectId
where timesheets.WorkedHours > budgets.BudgetedHours

No, you're never too old to Yak'n'Roll if you're too young to die.

spc2000uk
Starting Member

3 Posts

Posted - 2011-04-26 : 06:12:22

quote:
Originally posted by webfred

select timesheets.ProjectId,timesheets.WorkedHours,budgets.BudgetedHours
from
(select ProjectId, sum(WorkedHours) as WorkedHours from ProjectTimesheets group by ProjectId)timesheets
join
(select ProjectId, sum(BudgetedHours) as BudgetedHours from ProjectBudgets group by ProjectId)budgets
on timesheets.ProjectId = budgets.ProjectId
where timesheets.WorkedHours > budgets.BudgetedHours

No, you're never too old to Yak'n'Roll if you're too young to die.

Fantastic.........thanks webfred....you've saved my sanity....

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-26 : 07:27:36

create table
#Project_Timesheets (Project_ID int,Worked_Hours int)

insert into #Project_Timesheets
select 1, 10 union all
select 1, 5 union all
select 2, 12 union all
select 1, 2

create table #Project_Budgets (Project_id int, Budgetd_Hours int)
insert into #Project_Budgets
select 1,5 union all
select 1,6 union all
select 2,20

With Cte as
(
Select Project_id,Sum(Worked_Hours) as Worked_Hours from #Project_Timesheets group by Project_id
),Cte1 as
(
Select Project_id,Sum(Budgetd_Hours) as Budgetd_Hours from #Project_Budgets group by Project_id
)
select C.Project_id,C.Worked_Hours,C2.Budgetd_Hours From
Cte as C,Cte1 as C2 where C.Project_id=C2.Project_id and C.Worked_Hours>C2.Budgetd_Hours

Raghu' S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources