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.
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, HoursAnd a budget table with columns: JobPhase, Budget, BudgetHoursI want to create a result sets:Timecards: grouped by jobphaseBudget info: grouped by Jobphaseand 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) tinner 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] |
|
|
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 |
|
|
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 |
|
|
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] |
|
|
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 BudgetHoursfrom(Select JobPhase, Date, Hours,null as Budget,null as BudgetHoursfrom Timecardsunion allSelect JobPhase, null,null,Budget, BudgetHoursfrom [Budget info])tgroup by JobPhase |
|
|
michaelwhaley
Starting Member
5 Posts |
Posted - 2008-07-25 : 11:16:13
|
Damn you guys are quick. That was a big help. Thank you. |
|
|
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 |
|
|
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 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-07-25 : 12:08:11
|
[code]DROP TABLE timeCardDROP TABLE budgetCREATE 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 = 44864DECLARE @count INT SET @count = 0WHILE @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 @countEND[/code]JOIN subqueries[code]DECLARE @then DATETIMEDECLARE @now DATETIMESELECT @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.jobSELECT @now = GetDate()SELECT DATEDIFF(MS, @then, @now)[/code]Av time 1598.1 ms (averaged over 20 runs)UNION[code]DECLARE @then DATETIMEDECLARE @now DATETIMESELECT @then = getDate()select JobPhase,sum(Hours) AS TotalHours,sum(Budget) AS Budget,sum(BudgetHours) AS BudgetHoursfrom(Select JobPhase, Date, Hours,null as Budget,null as BudgetHoursfrom Timecardunion allSelect JobPhase, null,null,Budget, BudgetHoursfrom [Budget])tgroup by JobPhaseSELECT @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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-26 : 14:08:59
|
quote: Originally posted by Transact Charlie
DROP TABLE timeCardDROP TABLE budgetCREATE 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 = 44864DECLARE @count INT SET @count = 0WHILE @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 @countEND JOIN subqueriesDECLARE @then DATETIMEDECLARE @now DATETIMESELECT @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.jobSELECT @now = GetDate()SELECT DATEDIFF(MS, @then, @now) Av time 1598.1 ms (averaged over 20 runs)UNIONDECLARE @then DATETIMEDECLARE @now DATETIMESELECT @then = getDate()select JobPhase,sum(Hours) AS TotalHours,sum(Budget) AS Budget,sum(BudgetHours) AS BudgetHoursfrom(Select JobPhase, Date, Hours,null as Budget,null as BudgetHoursfrom Timecardunion allSelect JobPhase, null,null,Budget, BudgetHoursfrom [Budget])tgroup by JobPhaseSELECT @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 |
|
|
|
|
|
|
|