How can I include all dates in a query when there is no data for a given column

Hi,  I am trying to generate a list of activity counts for given departments based on the month and date of a given year, but I need all months to be included even if the count is 0 for that month.  I have one query that generates the following output...

DEPT_COUNTS
mnthYear,Count,Department
01-2017,1,Dept_A
07-2017,1,Dept_A
07-2017,62,Dept_B
08-2017,2,Dept_B

And I tried join it a number of ways to the following data, but couldn't get it return what I needed
TABLE_MONTHS
mYr,Cnt
01-2017,0
02-2017,0
03-2017,0
04-2017,0
05-2017,0
06-2017,0
07-2017,0
08-2017,0
09-2017,0
10-2017,0
11-2017,0
12-2017,0

The query was something like the following
select tm.*,dep.* from (select * from TABLE_MONTHS) tm
LEFT OUTER JOIN
(select * from DEP_COUNTS) dep on dep.mnthYear = tm.mYr

What I need to have is...
mnthYear,Count,Department
01-2017,1,Dept_A
02-2017,0,Dept_A
03-2017,0,Dept_A
04-2017,0,Dept_A
05-2017,0,Dept_A
06-2017,0,Dept_A
07-2017,0,Dept_A
08-2017,0,Dept_A
09-2017,0,Dept_A
10-2017,0,Dept_A
11-2017,0,Dept_A
12-2017,0,Dept_A
01-2017,1,Dept_B
02-2017,0,Dept_B
03-2017,0,Dept_B
04-2017,0,Dept_B
05-2017,0,Dept_B
06-2017,0,Dept_B
07-2017,62,Dept_B
08-2017,2,Dept_B
09-2017,0,Dept_B
10-2017,0,Dept_B
11-2017,0,Dept_B
12-2017,0,Dept_B

Is there a LEFT or RIGHT JOIN query that can do this?

I hope someone can help!
cbrydonAsked:
Who is Participating?
 
Snarf0001Commented:
You need to combine the distinct departments from the DEPT table and join that with the dates first.
Then do a left join against the full table to get the results you want.

select x.mYr, isnull(d.Count, 0) as Count, x.Department
from (
	select mYr, Department
	from TABLE_MONTHS, (
		select distinct Department
		from DEPT_COUNTS
	) x
) x
left join DEPT_COUNTS d on x.mYr = d.mnthYear and x.Department = d.Department

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
If your TABLE_MONTHS has all months, this query will return all months even those not having data in the DEP_COUNTS table:
select tm.*,dep.* 
from TABLE_MONTHS as tm
LEFT JOIN DEP_COUNTS as dep 
on dep.mnthYear = tm.mYr

Open in new window

0
 
cbrydonAuthor Commented:
Thanks Éric,

Thats close, but not quite what I'm after.  If I run the following query....

select tm.*,dep.*,COALESCE(tm.mYr,dep.mnthYear) as MonthYear, CASE WHEN Count IS NULL THEN Cnt ELSE Count END as Dept_Count
from sample.dbo.TABLE_MONTHS tm
LEFT JOIN sample.dbo.DEPT_COUNTS as dep
on dep.mnthYear = tm.mYr

I get the following results...
mYr,Cnt,mnthYear,Count,Department,MonthYear,Dept_Count
01-2017,0,01-2017,1,Dept_A,01-2017,1
02-2017,0,NULL,NULL,NULL,02-2017,0
03-2017,0,NULL,NULL,NULL,03-2017,0
04-2017,0,NULL,NULL,NULL,04-2017,0
05-2017,0,NULL,NULL,NULL,05-2017,0
06-2017,0,NULL,NULL,NULL,06-2017,0
07-2017,0,07-2017,1,Dept_A,07-2017,1
07-2017,0,07-2017,62,Dept_B,07-2017,62
08-2017,0,08-2017,2,Dept_B,08-2017,2
09-2017,0,NULL,NULL,NULL,09-2017,0
10-2017,0,NULL,NULL,NULL,10-2017,0
11-2017,0,NULL,NULL,NULL,11-2017,0
12-2017,0,NULL,NULL,NULL,12-2017,0

What I need is the Dept field also filled in with a count of 0 for each month - like what is listed below...
mYr,Cnt,mnthYear,Count,Department,MonthYear,Dept_Count
01-2017,0,01-2017,1,Dept_A,01-2017,1
02-2017,0,NULL,NULL,Dept_A,02-2017,0
03-2017,0,NULL,NULL,Dept_A,03-2017,0
04-2017,0,NULL,NULL,Dept_A,04-2017,0
05-2017,0,NULL,NULL,Dept_A,05-2017,0
06-2017,0,NULL,NULL,Dept_A,06-2017,0
07-2017,0,07-2017,1,Dept_A,07-2017,1
08-2017,0,NULL,NULL,Dept_A,08-2017,0
09-2017,0,NULL,NULL,Dept_A,09-2017,0
10-2017,0,NULL,NULL,Dept_A,10-2017,0
11-2017,0,NULL,NULL,Dept_A,11-2017,0
12-2017,0,NULL,NULL,Dept_A,12-2017,0
01-2017,0,NULL,NULL,Dept_B,01-2017,1
02-2017,0,NULL,NULL,Dept_B,02-2017,0
03-2017,0,NULL,NULL,Dept_B,03-2017,0
04-2017,0,NULL,NULL,Dept_B,04-2017,0
05-2017,0,NULL,NULL,Dept_B,05-2017,0
06-2017,0,NULL,NULL,Dept_B,06-2017,0
07-2017,0,07-2017,62,Dept_B,07-2017,62
08-2017,0,08-2017,2,Dept_B,08-2017,2
09-2017,0,NULL,NULL,Dept_B,09-2017,0
10-2017,0,NULL,NULL,Dept_B,10-2017,0
11-2017,0,NULL,NULL,Dept_B,11-2017,0
12-2017,0,NULL,NULL,Dept_B,12-2017,0

I guess I don't really need the COALESCE as I have the month and year from the first column, but I do need one line for each department and each month.  I guess I didn't really explain that very well in my question.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Éric MoreauSenior .Net ConsultantCommented:
That means that you need to do a CROSS JOIN between your dates and your departments first:

select tm.*,dep.* 
from (
   select A.mYr, B.Department FROM TABLE_MONTHS as A CROSS JOIN DEP_COUNTS as B)
) as tm
LEFT JOIN DEP_COUNTS as dep 
on dep.mnthYear = tm.mYr
and dep.Department = tm.Department

Open in new window

0
 
Snarf0001Commented:
You can't use a cross join against the entire table, or you'll have too many rows.  You need to cross against distinct departments as I outlined above.
0
 
cbrydonAuthor Commented:
Éric's works if you add a GROUP BY Clause at the end, but I decided to go with Snarf0001's suggestion.  Thanks also for showing the IsNull function instead of using a CASE WHEN statement!

Appreciate the help!!
0
 
cbrydonAuthor Commented:
Thanks for the help!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.