Combining data from 2 tables - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Combining data from 2 tables

Author

Topic

alanlambert
Starting Member

26 Posts

Posted - 2009-06-08 : 10:50:20

I have 2 tables, 1 containg the number of days that various employees are forecast to work on various projects each week and the other containing the actual number of days they worked on each project each week.

I need to combine these two tables to show the forecast and actual number of days for each project, employee and week. However, I need the full results from each table even if data is missing from the other. (I'm only interested in weeks that match but each table may contain employees and projects not in the other and these must always be shown.)

I'm sure this shouldn't be as hard as I'm finding it but I can't figure out how to do this given the apparent need for inner and outer joins between the same tables at the same time and it's driving me mad!

I'm using SQL Server 2005

Sample data and expected output is:


DECLARE @Forecast table
(
	ProjectCode nvarchar(9),	
	EmployeeCode nvarchar(5),
	[Week] datetime,
	ForecastDays int
)

DECLARE @Actuals table
(
	ProjectCode nvarchar(9),	
	EmployeeCode nvarchar(5),
	[Week] datetime,
	ActualDays int
)

INSERT INTO @Forecast (ProjectCode, EmployeeCode, [Week], Days)
SELECT 'P1', 'E1', '2009-06-01', 2 UNION
SELECT 'P2', 'E1', '2009-06-01', 1 UNION
SELECT 'P1', 'E1', '2009-06-08', 2 UNION
SELECT 'P2', 'E1', '2009-06-08', 3 UNION
SELECT 'P1', 'E2', '2009-06-01', 1 UNION
SELECT 'P2', 'E2', '2009-06-01', 1 UNION
SELECT 'P1', 'E2', '2009-06-08', 1 UNION
SELECT 'P2', 'E2', '2009-06-08', 1 UNION
SELECT 'P3', 'E2', '2009-06-08', 1 UNION
SELECT 'P3', 'E3', '2009-06-08', 3

INSERT INTO @Actuals (ProjectCode, EmployeeCode, [Week], Days)
SELECT 'P1', 'E1', '2009-06-01', 1 UNION
SELECT 'P2', 'E1', '2009-06-01', 2 UNION
SELECT 'P1', 'E1', '2009-06-08', 1 UNION
SELECT 'P3', 'E1', '2009-06-08', 3 UNION
SELECT 'P2', 'E2', '2009-06-01', 1 UNION
SELECT 'P1', 'E2', '2009-06-08', 2 UNION
SELECT 'P2', 'E2', '2009-06-08', 3 UNION
SELECT 'P1', 'E4', '2009-06-08', 1 UNION
SELECT 'P2', 'E4', '2009-06-08', 1 UNION
SELECT 'P4', 'E4', '2009-06-08', 1

Expected Results
================
ProjectCode   EmployeeCode   Week         ForecastDays   ActualDays
P1            E1             2009-06-01   2              1
P2            E1             2009-06-01   1              2
P1            E1             2009-06-08   2              1
P2            E1             2009-06-08   3              0
P1            E2             2009-06-01   1              0
P2            E2             2009-06-01   1              1
P1            E2             2009-06-08   1              2
P2            E2             2009-06-08   1              3
P3            E2             2009-06-08   1              0
P3            E3             2009-06-08   3              0
P3            E1             2009-06-08   0              3
P1            E4             2009-06-08   0              1
P2            E4             2009-06-08   0              1
P4            E4             2009-06-08   0              1

Many thanks for your help.

Alan

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-08 : 11:03:37

I'm guessing at how your tables join, but this should get you going.

Jim


SELECT 
    [ProjectCode] = coalesce(f.ProjectCode,a.projectcode)
    ,[EmployeeCode] = coalesce(f.EmployeeCode,a.Employeecode)
    ,[Week] = coalesce(f.Week,a.Week)
    ,[ForeCastDays] = ISNULL(f.days,0)
    ,[ActualDays]   = ISNULL(a.days,0) 

FROM
   @forecast f
FULL JOIN
   @actuals a
ON
    f.projectcode  = a.projectcode
and f.employeecode = a.employeecode
and f.week         = a.week

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-08 : 11:08:46

[code]

DECLARE @Forecast TABLE
(
ProjectCode nvarchar(9),
EmployeeCode nvarchar(5),
[Week] datetime,
ForecastDays int
)

DECLARE @Actuals TABLE
(
ProjectCode nvarchar(9),
EmployeeCode nvarchar(5),
[Week] datetime,
ActualDays int
)

INSERT INTO @Forecast (ProjectCode, EmployeeCode, [Week], ForecastDays)
SELECT 'P1', 'E1', '2009-06-01', 2 UNION
SELECT 'P2', 'E1', '2009-06-01', 1 UNION
SELECT 'P1', 'E1', '2009-06-08', 2 UNION
SELECT 'P2', 'E1', '2009-06-08', 3 UNION
SELECT 'P1', 'E2', '2009-06-01', 1 UNION
SELECT 'P2', 'E2', '2009-06-01', 1 UNION
SELECT 'P1', 'E2', '2009-06-08', 1 UNION
SELECT 'P2', 'E2', '2009-06-08', 1 UNION
SELECT 'P3', 'E2', '2009-06-08', 1 UNION
SELECT 'P3', 'E3', '2009-06-08', 3

INSERT INTO @Actuals (ProjectCode, EmployeeCode, [Week], ActualDays)
SELECT 'P1', 'E1', '2009-06-01', 1 UNION
SELECT 'P2', 'E1', '2009-06-01', 2 UNION
SELECT 'P1', 'E1', '2009-06-08', 1 UNION
SELECT 'P3', 'E1', '2009-06-08', 3 UNION
SELECT 'P2', 'E2', '2009-06-01', 1 UNION
SELECT 'P1', 'E2', '2009-06-08', 2 UNION
SELECT 'P2', 'E2', '2009-06-08', 3 UNION
SELECT 'P1', 'E4', '2009-06-08', 1 UNION
SELECT 'P2', 'E4', '2009-06-08', 1 UNION
SELECT 'P4', 'E4', '2009-06-08', 1

SELECT ProjectCode = coalesce(f.ProjectCode, a.ProjectCode),
EmployeeCode = coalesce(f.EmployeeCode, a.EmployeeCode),
[Week] = coalesce(f.[Week], a.[Week]),
ForecastDays = coalesce(f.ForecastDays, 0),
ActualDays = coalesce(a.ActualDays, 0)
FROM @Forecast f
FULL OUTER JOIN @Actuals a ON f.ProjectCode = a.ProjectCode
AND f.EmployeeCode = a.EmployeeCode
AND f.[Week] = a.[Week]
ORDER BY ProjectCode, EmployeeCode, [Week]

/*
ProjectCode EmployeeCode Week ForecastDays ActualDays
----------- ------------ ----------- ------------ -----------
P1 E1 2009-06-01 2 1
P1 E1 2009-06-08 2 1
P1 E2 2009-06-01 1 0
P1 E2 2009-06-08 1 2
P1 E4 2009-06-08 0 1
P2 E1 2009-06-01 1 2
P2 E1 2009-06-08 3 0
P2 E2 2009-06-01 1 1
P2 E2 2009-06-08 1 3
P2 E4 2009-06-08 0 1
P3 E1 2009-06-08 0 3
P3 E2 2009-06-08 1 0
P3 E3 2009-06-08 3 0
P4 E4 2009-06-08 0 1

(14 row(s) affected)
*/
[/code]

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

alanlambert
Starting Member

26 Posts

Posted - 2009-06-08 : 11:14:14

Ah! I didn't know about the full join. That makes perfect sense.

Thank you both very much

Alan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources