select	mt.Market
	,mm.Month
	,mt.datepart(year, myDateCol) as year
	,isNull(myValue, 0) as value
from	myTable mt

--left join to derived table of months to include zeros
left join (select 1 as month union
	select 2 union
	select 3 union
	select 4 union
	select 5 union
	select 6 union
	select 7 union
	select 8 union
	select 9 union
	select 10 union
	select 11 union
	select 12) mm
		on mm.month = datepart(month, myDateCol)

--Get results from previous year of @year parameter
where	myDateCol >= convert(varchar, @year-1) + '-1-1'
and	myDateCol < convert(varchar, @year) + '1-1'

from	(select 1 as month union
         ...

--left join to derived table of months to include zeros
left join myTable mt
            on datepart(month, mt.myDateCol) = mm.month

create table #temp
(
	market	int default 1,
	mth	int,
	yr	int,
	value	int default 10
)

insert into #temp(mth, yr)
select	1, 2004 union all
select  2, 2004 union all
select	3, 2004 union all
select  5, 2004 union all
select  1, 2005 union all
select  2, 2005 union all
select  3, 2005 union all
select  4, 2005

--select * from #temp

declare @year	int
select  @year = 2005  -- user input
select 	isnull(t.market, 1) as Market, y.mth as [Month], y.yr as [Year], isnull(t.value, 0) as [Value]
from
(
	select	mth, yr
	from
	(
		select  1 as mth union all select 2 union all select 3 union all
		select  4 union all select  5 union all select  6 union all
		select  7 union all select  8 union all select  9 union all
		select 10 union all select 11 union all select 12
	) as m
	cross join
	(
		select	(@year - 1) as yr union all select @year
	) as y
) as y left join #temp t
on 	y.mth	= t.mth
and	y.yr	= t.yr
order by [Year], [Month]

drop table #temp

SELECT	ISNULL(COUNT(DISTINCT e.fk_PersonID),0) AS 	Value
	,mm.Month -- Month(e.EnrollmentDate)
	,YEAR(e.EnrollmentDate) Year
FROM	(
	select 1 as month union
	select 2 union
	select 3 union
	select 4 union
	select 5 union
	select 6 union
	select 7 union
	select 8 union
	select 9 union
	select 10 union
	select 11 union
	select 12
	) as mm
left join tbl_Enrollment e
		on datepart(month, e.EnrollmentDate) = mm.month
where	Year(e.EnrollmentDate) >= convert(DateTime,convert(varchar(10), @year-1) + '-1-1')
and	Year(e.EnrollmentDate) < convert(DateTime,convert(varchar(10), @year) + '-1-1')
GROUP BY mm.Month
	,YEAR(e.EnrollmentDate)

quote:

---

Originally posted by jsmith8858

TG -- the cross join is the way to go, as khtan demonstrates. YOu need to cross join the list of all months with the list of all years that you wish to consider, and from there left outer join to the data. In addition, if there is more than 1 market to display, you will need to cross join a distinct list of those as well.

See http://weblogs.sqlteam.com/jeffs/archive/2005/09/12/7755.aspx for more info on cross joins and how they help solve problems like this quite nicely.

---

quote:

---

95% scored higher (more nerdy), and
5% scored lower (less nerdy).

What does this mean? Your nerdiness is:

Definitely not nerdy, you are probably cool.

---