sql-test

sql-test.sql

--CREATE TABLE #MyTable (EventNname varchar(20), EventDate date, PublishedDate date)
 INSERT INTO #MyTable
SELECT 'Bake Sale', '1/1/2014','1/1/2014' UNION ALL
SELECT 'Movie Night', '1/2/2014','1/2/2014' UNION ALL
SELECT 'Dance Night', '1/3/2014','1/2/2014' UNION ALL
SELECT 'Flea Market', '1/4/2014','1/3/2014' UNION ALL
SELECT 'Dance Night', '1/5/2014','1/3/2014' UNION ALL
SELECT 'Marathon', '1/6/2014','1/3/2014' UNION ALL
SELECT 'Daance', '1/3/2014','1/2/2014'


select Events.TheDate as TheDate, Events.EventCount as EventCount, Published.PublishedCount as PublishedCount from
(
select count(*) as EventCount, eventDate as TheDate from 
#MyTable group by EventDate
) as Events

full Outer Join
(
select count(*) as PublishedCount, PublishedDate as TheDate from 
#MyTable group by PublishedDate
) as Published
on Events.TheDate = Published.TheDate