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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

É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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

É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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.