Joining Question - 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

Joining Question

Author

Topic

guygs18
Starting Member

4 Posts

Posted - 2012-01-10 : 08:10:47

Hi all

I have two queries, which I've stored as views, and would like to join them as follows:

dbo.cases_opened

Type Opened Date Count
General 14/10/2011 60
White Goods 14/10/2011 5
Fridges 17/10/2011 1
General 17/10/2011 79
White Goods 17/10/2011 17
General 18/10/2011 70

etc. There are 3 types.

dbo.cases_closed

Type Closed Date Count
General 14/10/2011 3
General 17/10/2011 13
General 18/10/2011 50
General 19/10/2011 59
White Goods 19/10/2011 9
Fridges 20/10/2011 1
General 20/10/2011 23
White Goods 20/10/2011 18
General 21/10/2011 30

etc. As you can see, not all types will have cases opened and closed on the same day, so nulls would suffice for blanks. Ideally I would like to present the query as such:

Type Date Opened Count Closed Count
General 14/10/2011 60 3
White Goods 14/10/2011 5 0
Fridges 14/10/2011 0 0
General 17/10/2011 79 13
White Goods 17/10/2011 17 0
Fridges 17/10/2011 1 0
General 18/10/2011 70 50
White Goods 18/10/2011 13 0
Fridges 18/10/2011 0 0
General 19/10/2011 53 59
White Goods 19/10/2011 9 9
Fridges 19/10/2011 0 0
General 20/10/2011 59 23
White Goods 20/10/2011 7 18
Fridges 20/10/2011 0 1

etc. I could then filter out those records where opened/closed are both 0. Is this possible? Any help would be greatly appreciated.
Thanks!

Guy Southcott

singularity
Posting Yak Master

153 Posts

Posted - 2012-01-10 : 09:01:31

[code]
select isnull(a.type,b.type) as type, isnull(a.opened_date,b.closed_date) as [date],
isnull(a.[count],0) as opened_count, isnull(b.[count],0) as closed_count
from cases_opened a
full join cases_closed b on a.type = b.type and a.opened_date = b.closed_date
[/code]

guygs18
Starting Member

4 Posts

Posted - 2012-01-10 : 09:21:05

Thanks singularity! Much appreciated.

Guy Southcott

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 09:26:01

quote:

Originally posted by singularity


select isnull(a.type,b.type) as type, isnull(a.opened_date,b.closed_date) as [date],
       isnull(a.[count],0) as opened_count, isnull(b.[count],0) as closed_count
from cases_opened a
full join cases_closed b on a.type = b.type and a.opened_date = b.closed_date

i prefer COALESCE over ISNULL due to following reasons

http://beyondrelational.com/blogs/madhivanan/archive/2007/10/04/isnull-or-coalesce.aspx

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources