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 2000 Forums
 Transact-SQL (2000)
 Join totals

Author  Topic 

michaelwhaley
Starting Member

5 Posts

Posted - 2008-07-25 : 10:57:23
I need to join the results of 2 total queries.

For example: I have a timecard table for construction work with columns: JobPhase, Date, Hours

And a budget table with columns: JobPhase, Budget, BudgetHours

I want to create a result sets:

Timecards: grouped by jobphase
Budget info: grouped by Jobphase

and then join them by jobphase so I can compare the total hours by phase against the budgeted hours by phase. A job can have an original budget and additional budgets for changes so all the budgets need to be included thats why there is a budget column after the jobphase column in the budget table.

Thanks in advance for the help.

Mike

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-25 : 11:03:23
[code]select *
from
(
select JobPhase, Total_Hours = sum(Hours)
from TimeCard
group by JobPhase
) t
inner join
(
select JobPhase, Total_BudgetHours = sum(BudgetHours)
from Budget
group by JobPhase
) b on t.JobPhase = b.JobPhase[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-25 : 11:04:59
try...


SELECT
a.job AS Jobphase
, a.total_hours AS [Total Hours]
, b.budgeted_Hours AS [Budgeted Hours]
, b.total_budget AS [Total Buget]
FROM
(
SELECT
jobPhase AS job
, SUM(Hours) AS total_hours
FROM
timecard
GROUP BY
jobPhase
)
a

INNER JOIN (
SELECT
jobPhase AS job
, SUM(Budget) AS total_budget
, SUM(BudgetHours) AS budgeted_Hours
FROM
budget
GROUP BY
jobPhase
)
b ON a.job = b.job


-------------
Charlie
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-25 : 11:06:13
khtan, I think he needs the total budget as well.

-------------
Charlie
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-25 : 11:07:56
Oops, i just cut & paste


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 11:11:43

select JobPhase,
sum(Hours) AS TotalHours,
sum(Budget) AS Budget,
sum(BudgetHours) AS BudgetHours
from
(
Select JobPhase, Date, Hours,null as Budget,null as BudgetHours
from Timecards
union all
Select JobPhase, null,null,Budget, BudgetHours
from [Budget info]
)t
group by JobPhase
Go to Top of Page

michaelwhaley
Starting Member

5 Posts

Posted - 2008-07-25 : 11:16:13
Damn you guys are quick. That was a big help. Thank you.
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-25 : 11:17:26
Hi Visakh16.

Is your solution any better / faster from building two sub-queries and joining them?

I'm sure the end results will be the same but your query is kinda.... cooler somehow. I would never think of doing it that way.

-------------
Charlie
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 11:33:34
quote:
Originally posted by Transact Charlie

Hi Visakh16.

Is your solution any better / faster from building two sub-queries and joining them?

I'm sure the end results will be the same but your query is kinda.... cooler somehow. I would never think of doing it that way.

-------------
Charlie


Not sure buddy. i just gave another way of doing it. You could test it and give us results from both approaches. I'm at home and dont have SQL box here to test
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-25 : 12:08:11
[code]
DROP TABLE timeCard
DROP TABLE budget

CREATE TABLE timeCard (
[jobPhase] INT
, [hours] DECIMAL (10,2)
, [date] DATETIME
)

CREATE TABLE budget (
[jobPhase] INT
, [budget] MONEY
, [budgetHours] DECIMAL(10,2)
)

DECLARE @top INT SET @top = 44864
DECLARE @count INT SET @count = 0

WHILE @count < @top BEGIN

INSERT INTO timeCard
SELECT
@count
, RAND()*1000
, '2008-01-01'

INSERT INTO budget
SELECT
@count
, RAND() * 100
, RAND() * 1000

SET @count = @count + 1
PRINT @count
END
[/code]

JOIN subqueries
[code]
DECLARE @then DATETIME
DECLARE @now DATETIME

SELECT @then = getDate()

SELECT
a.job AS Jobphase
, a.total_hours AS [Total Hours]
, b.budgeted_Hours AS [Budgeted Hours]
, b.total_budget AS [Total Buget]
FROM
(
SELECT
jobPhase AS job
, SUM(Hours) AS total_hours
FROM
timecard
GROUP BY
jobPhase
)
a

INNER JOIN (
SELECT
jobPhase AS job
, SUM(Budget) AS total_budget
, SUM(BudgetHours) AS budgeted_Hours
FROM
budget
GROUP BY
jobPhase
)
b ON a.job = b.job


SELECT @now = GetDate()

SELECT DATEDIFF(MS, @then, @now)
[/code]

Av time 1598.1 ms (averaged over 20 runs)


UNION
[code]
DECLARE @then DATETIME
DECLARE @now DATETIME

SELECT @then = getDate()

select JobPhase,
sum(Hours) AS TotalHours,
sum(Budget) AS Budget,
sum(BudgetHours) AS BudgetHours
from
(
Select JobPhase, Date, Hours,null as Budget,null as BudgetHours
from Timecard
union all
Select JobPhase, null,null,Budget, BudgetHours
from [Budget]
)t
group by JobPhase

SELECT @now = GetDate()

SELECT DATEDIFF(MS, @then, @now)
[/code]

AV time : 1607.8 ms (average over 20 runs)


So, doesn't look like much if any difference (on tables with no indexs at least)

-------------
Charlie
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-26 : 14:08:59
quote:
Originally posted by Transact Charlie


DROP TABLE timeCard
DROP TABLE budget

CREATE TABLE timeCard (
[jobPhase] INT
, [hours] DECIMAL (10,2)
, [date] DATETIME
)

CREATE TABLE budget (
[jobPhase] INT
, [budget] MONEY
, [budgetHours] DECIMAL(10,2)
)

DECLARE @top INT SET @top = 44864
DECLARE @count INT SET @count = 0

WHILE @count < @top BEGIN

INSERT INTO timeCard
SELECT
@count
, RAND()*1000
, '2008-01-01'

INSERT INTO budget
SELECT
@count
, RAND() * 100
, RAND() * 1000

SET @count = @count + 1
PRINT @count
END


JOIN subqueries

DECLARE @then DATETIME
DECLARE @now DATETIME

SELECT @then = getDate()

SELECT
a.job AS Jobphase
, a.total_hours AS [Total Hours]
, b.budgeted_Hours AS [Budgeted Hours]
, b.total_budget AS [Total Buget]
FROM
(
SELECT
jobPhase AS job
, SUM(Hours) AS total_hours
FROM
timecard
GROUP BY
jobPhase
)
a

INNER JOIN (
SELECT
jobPhase AS job
, SUM(Budget) AS total_budget
, SUM(BudgetHours) AS budgeted_Hours
FROM
budget
GROUP BY
jobPhase
)
b ON a.job = b.job


SELECT @now = GetDate()

SELECT DATEDIFF(MS, @then, @now)


Av time 1598.1 ms (averaged over 20 runs)


UNION

DECLARE @then DATETIME
DECLARE @now DATETIME

SELECT @then = getDate()

select JobPhase,
sum(Hours) AS TotalHours,
sum(Budget) AS Budget,
sum(BudgetHours) AS BudgetHours
from
(
Select JobPhase, Date, Hours,null as Budget,null as BudgetHours
from Timecard
union all
Select JobPhase, null,null,Budget, BudgetHours
from [Budget]
)t
group by JobPhase

SELECT @now = GetDate()

SELECT DATEDIFF(MS, @then, @now)


AV time : 1607.8 ms (average over 20 runs)


So, doesn't look like much if any difference (on tables with no indexs at least)

-------------
Charlie


Cool
thanks for testing and posting results
Go to Top of Page
   

- Advertisement -