January 31, 2012 at 7:01 am
This reply is some three months too late.
Using a Union Query to merge the data from the two tables will return all projects whether they have actuals and no forecast or forecast and no actuals or both. If there are no forecast and no actual then nothing will appear, however you could join the summary table back the the master projects table with an outer join.
The trick is to get All records with a Union query. Add dummy zero value fields for Actual Hours in the Forecast table and Forecast hours in the Actual table. Then sum the resulting records grouped by Project ID and Category.
Having got the summary table for the hours by project and category the rest of the fields in the other tables can be added to the query. Union All makes sure that all the records are included.
This is the example:
Select
ProjectId
,Category
,sum(ForecastHours) as ForecastTot
,Sum(ActualHours) as ActualTot
From
(select
ProjectId
,Category
,HoursQTY as ForecastHours
,0 as ActualHours
from ForecastTable
union all
select
,Proj
,Category
,0 as ForecastHours
,HoursQTY as ActualHours
from EmployeeTransTable) as TempTbl
Group by
ProjectId
,Category
January 31, 2012 at 7:30 am
TIMPARROTT (1/31/2012)
This reply is some three months too late.Using a Union Query to merge the data from the two tables will return all projects whether they have actuals and no forecast or forecast and no actuals or both. If there are no forecast and no actual then nothing will appear, however you could join the summary table back the the master projects table with an outer join.
The trick is to get All records with a Union query. Add dummy zero value fields for Actual Hours in the Forecast table and Forecast hours in the Actual table. Then sum the resulting records grouped by Project ID and Category.
Having got the summary table for the hours by project and category the rest of the fields in the other tables can be added to the query. Union All makes sure that all the records are included.
This is the example:
Select
ProjectId
,Category
,sum(ForecastHours) as ForecastTot
,Sum(ActualHours) as ActualTot
From
(select
ProjectId
,Category
,HoursQTY as ForecastHours
,0 as ActualHours
from ForecastTable
union all
select
,Proj
,Category
,0 as ForecastHours
,HoursQTY as ActualHours
from EmployeeTransTable) as TempTbl
Group by
ProjectId
,Category
This query will be a significant hit on performance versus using a simple full join with case statements or isnull. I only use UNION ALL when I have 2 result sets that cannot be queried in 1 statement.
Jared
CE - Microsoft
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply