join tables by date - 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

General SQL Server Forums

New to SQL Server Programming

join tables by date

Author

Topic

shaharru
Yak Posting Veteran

72 Posts

Posted - 2010-08-10 : 11:53:02

How do i join the following 2 tables.

Orders Table:

CustomerID | Name |Date
1 | order1 | 2010-01-01
2 | order2 | 2010-01-01
2 | order3 | 2010-04-01

Calls Table:

CustomerID | Name |Date
1 | call1 | 2010-01-01
3 | call2 | 2010-06-01
2 | call3 | 2010-05-01

I want to join the two tables by date , Result:

Date | Orders | Calls
2010-01-01 | 2 | 1
2010-04-01 | 1 | 0
2010-05-01 | 0 | 1
2010-06-01 | 0 | 1

how do i join them? thx!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-10 : 12:14:33

You need a full outer join.

select coalesce(a.Date,b.Date) as Date
,coalesce(a.cnt,0) as Orders
,coalesce(b.cnt,0) as Calls
from
(select Date, Count(*) as cnt from Orders group by Date) a
full outer join (select Date, Count(*) as cnt from Calls group by Date) b on a.Date = b.Date

shaharru
Yak Posting Veteran

72 Posts

Posted - 2010-08-10 : 13:14:41

thx

maybe it will be wise to change the table structure?

if so any idea which structure will make it easier to get the results i wanted?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-10 : 13:16:26


SELECT Date,
COUNT(Order) AS Orders,
COUNT(Call) AS Calls
FROM
( 
SELECT Date,Name AS Order,CAST(NULL AS varchar(100)) AS  Call
FROM Orders
UNION ALL
SELECT Date,NULL,Name
FROM Calls
)t
GROUP BY Date

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources