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!
