| Author |
 Topic  |
|
|
michaelwhaley
Starting Member
USA
5 Posts |
 Posted - 07/25/2008 : 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)
Singapore
16769 Posts |
Posted - 07/25/2008 : 11:03:23
|
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 |
 |
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3448 Posts |
Posted - 07/25/2008 : 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 |
Edited by - Transact Charlie on 07/25/2008 11:11:04 |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3448 Posts |
Posted - 07/25/2008 : 11:06:13
|
khtan, I think he needs the total budget as well.
-------------
Charlie |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 07/25/2008 : 11:07:56
|
Oops, i just cut & paste 
KH
Time is always against us
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48064 Posts |
Posted - 07/25/2008 : 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 |
|
|
|
michaelwhaley
Starting Member
USA
5 Posts |
Posted - 07/25/2008 : 11:16:13
|
| Damn you guys are quick. That was a big help. Thank you. |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3448 Posts |
Posted - 07/25/2008 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48064 Posts |
Posted - 07/25/2008 : 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  |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3448 Posts |
Posted - 07/25/2008 : 12:08:11
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48064 Posts |
Posted - 07/26/2008 : 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 |
|
|
| |
Topic |
|
|
|
Join totals
