sql - how to query the count of records from first day to last day of the month -

i count of every day records table. example have table “employee” following fields id, empno, datehired. , have following records

id  empno       datehired 1   000001      3/2/2013 12:00:00 2   000002      3/14/2013 12:00:00 3   000003      3/14/2013 12:00:00 4   000004      3/21/2013 12:00:00 5   000005      4/2/2013 12:00:00 6   000006      4/3/2013 12:00:00 7   000007      4/3/2013 12:00:00 8   000008      4/3/2013 12:00:00 9   000009      4/3/2013 12:00:00 10  000010      4/4/2013 12:00:00 11  000011      4/5/2013 12:00:00 12  000012      5/1/2013 12:00:00 

and current month april, how can value:

count   day 0   4/1/2013 12:00:00 1   4/2/2013 12:00:00 4   4/3/2013 12:00:00 1   4/4/2013 12:00:00 1   4/5/2013 12:00:00 0   4/6/2013 12:00:00 0   4/7/2013 12:00:00 0   4/8/2013 12:00:00 0   4/30/2013 12:00:00 

you need create calendar whole month of april in order whole dates of month. aid of using common table expression, can want.

after creating calendar, join table employee using left join dates have no matches on table employee still included on result.

with april_calendar (   select cast('20130401' datetime) [date]   union   select dateadd(dd, 1, [date])     april_calendar    dateadd(dd, 1, [date]) <= '20130430' ) select a.date, count(b.datehired) totalcount   april_calendar        left join employee b           on a.date = b.datehired group  a.date order  a.date 

• sqlfiddle demo