A thought this would be simple...

I had originally thought a full outer join would solve this, but alas I'm a roadblock. Perhaps it's because it's a Friday!

The goal is to create the most efficient query that will display results of the daily calls and sales as
this:

Date EmpID Calls Sales
7/1/2006 1 20 5
7/1/2006 2 25 7
7/1/2006 3 NULL 1
7/1/2006 4 10 NULL

The problem is a simple full outer join ends up ignoring EmpID 3 who has no Calls in t1, but I still want that row displayed in the results. Any ideas? TIA

create table t1 (Date smalldatetime, EmpID int, Calls int)

create table t2 (Date smalldatetime, EmpID int, Sales int)

insert into t1
values ('7/1/2006', 1, 20)

insert into t1
values ('7/1/2006', 2, 25)

insert into t1
values ('7/1/2006', 4, 10)

insert into t2
values ('7/1/2006', 1, 5)

insert into t2
values ('7/1/2006', 2, 7)

insert into t2
values ('7/1/2006', 3, 1)

what about this?

select coalesce(t1.date,t2.date) as date,coalesce(t1.empid,t2.empid) as empid,calls,sales
from t1 full outer join t2 on t1.empid =t2.empid

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks for the response.

The problem I was running into is I need to include both EmpID and Date in the full outer join since the results need to be report only for a single date. So, when I exec this it does not return EmpID 3...

select coalesce(t1.date,t2.date) as date,coalesce(t1.empid,t2.empid) as empid,calls,sales
from t1 full outer join t2 on t1.empid =t2.empid and t1.date = t2.date

Any thoughts? TIA

|||

are you sure?

I get this back with both queries

2006-07-01 00:00:00 1 20 5
2006-07-01 00:00:00 2 25 7
2006-07-01 00:00:00 3 NULL 1
2006-07-01 00:00:00 4 10 NULL

|||

You're right!

I was doing this...

select coalesce(t1.date,t2.date) as date,coalesce(t1.empid,t2.empid) as empid,calls,sales
from t1 full outer join t2 on t1.empid =t2.empid and t1.date = t2.date where t1.date = '7/1/2006'

When I should have had the WHERE clause this...

select coalesce(t1.date,t2.date) as date,coalesce(t1.empid,t2.empid) as empid,calls,sales
from t1 full outer join t2 on t1.empid =t2.empid and t1.date = t2.date where t1.date = '7/1/2006' or t2.date = '7/1/2006'

That seems to work. Thanks!