SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Join totals
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

michaelwhaley
Starting Member

USA
5 Posts

Posted - 07/25/2008 :  10:57:23  Show Profile  Reply with Quote
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)

Singapore
17658 Posts

Posted - 07/25/2008 :  11:03:23  Show Profile  Reply with Quote
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



KH
Time is always against us


Edited by - khtan on 07/25/2008 11:08:40
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 07/25/2008 :  11:04:59  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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

Edited by - Transact Charlie on 07/25/2008 11:11:04
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 07/25/2008 :  11:06:13  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
khtan, I think he needs the total budget as well.

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

khtan
In (Som, Ni, Yak)

Singapore
17658 Posts

Posted - 07/25/2008 :  11:07:56  Show Profile  Reply with Quote
Oops, i just cut & paste


KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/25/2008 :  11:11:43  Show Profile  Reply with Quote

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

USA
5 Posts

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

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 07/25/2008 :  11:17:26  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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

India
52325 Posts

Posted - 07/25/2008 :  11:33:34  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 07/25/2008 :  12:08:11  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote

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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/26/2008 :  14:08:59  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000