sql - Find a YTD Employee Average -

i'm looking find average number of employees first half of 2015. thats head count of each month, jan-jun / 6 (months). number desired result.

for example, lets 3 months simplicity's sake. jan had 100, feb had 105, , mar had 103. 308/3 = 102.7 average employees.

unfortunately i've been left few columns , i'd generate clean code make simple complete task. not sure how complete task though information have.

code:

 select   distinct a.personidno 'personid', a.[lasthiredate], a.[terminationdate],  --count(distinct a.personidno)      case    when a.employmentstatus = 'regular full time' 'rft'             when a.employmentstatus = 'prn' 'prn'             when a.employmentstatus = 'regular part time' 'rpt'             else a.employmentstatus     end 'empstatus'   --into #tmp_ytd_hc_avg   [employeetable]      a.orgcodeidno = '69' ,   (a.[terminationdate] >= '2015-01-01 00:00:00' , a.[terminationdate] <= '2015-06-30 23:59:59')   or (a.[terminationdate] null , a.employeestatus = 'active') 

sample data:

personid    lasthiredate            terminationdate         empstatus 19          2012-07-30 00:00:00.000 null                    rft 20          2010-01-01 00:00:00.000 null                    rft 21          2010-10-01 00:00:00.000 null                    rft 24          1994-06-28 00:00:00.000 null                    rft 25          2002-12-11 00:00:00.000 null                    rft 26          2011-03-21 00:00:00.000 null                    rft 27          2010-01-01 00:00:00.000 null                    rft 30          2010-06-29 00:00:00.000 null                    prn 34          2008-12-16 00:00:00.000 null                    rft 35          2010-01-01 00:00:00.000 null                    rft 36          2014-02-27 00:00:00.000 null                    rft 37          2009-03-01 00:00:00.000 null                    prn 39          2012-06-25 00:00:00.000 null                    rft 40          2012-01-01 00:00:00.000 null                    rft 42          2011-08-01 00:00:00.000 null                    rft 44          2014-02-27 00:00:00.000 2014-09-27 00:00:00.000 rft --hired before 2015-01-01 , leaves before 2015-01-01 54          2014-02-27 00:00:00.000 2015-05-15 00:00:00.000 rft --hired before 2015-01-01 , leaves before 2015-06-30 676         2015-02-27 00:00:00.000 2015-06-15 00:00:00.000 rft --hired after 2015-01-01 , leaves before 2015-06-30 3012        2015-03-20 00:00:00.000 2015-07-03 00:00:00.000 rft --hired after 2015-01-01 , leaves after 2015-06-30 5125        2015-07-11 00:00:00.000 null                    rpt 5127        2015-07-07 00:00:00.000 null                    rft 5129        2015-07-09 00:00:00.000 null                    prn 5131        2015-07-07 00:00:00.000 null                    prn 5133        2015-07-09 00:00:00.000 null                    prn 5136        2015-07-13 00:00:00.000 null                    rft 

here sql fiddle updated sample data. there 2 queries there: first returns 1 average number, second returns daily numbers understand how works. follow dates , can see how number changes people come , go.

---

for each person need know 2 dates: when hired , when left. hope lasthiredate , terminationdate mean. assume null terminationdate means person has not left yet, still employed.

when calculate similar reports calculate number of people employed each day in given range (rather month). can average daily numbers further needed.

i use calendar table. table has list of dates several decades.

create table [dbo].[calendar](     [dt] [date] not null,  constraint [pk_calendar] primary key clustered  (     [dt] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary] ) on [primary] 

in system has few columns, such [islastdayofmonth], [islastdayofquarter], useful in reports, in case need date column. there many ways populate such table.

for example, 100k rows (~270 years) 1900-01-01:

insert dbo.calendar (dt) select top (100000)      dateadd(day, row_number() on (order s1.[object_id])-1, '19000101') dt sys.all_objects s1 cross join sys.all_objects s2 option (maxdop 1); 

once have calendar table, here how use it:

with cte_employedpeople -- how many people employed on each day in given period (     select         dbo.calendar.dt         ,cast(count(*) float) people -- without cast final average int             dbo.calendar         cross join employeetable             (dbo.calendar.dt >= '2015-01-01')         , (dbo.calendar.dt <= '2015-06-30')         , (dbo.calendar.dt >= employeetable.lasthiredate)         , (dbo.calendar.dt <= employeetable.terminationdate               or employeetable.terminationdate null)     group dbo.calendar.dt ) ,cte_daily -- if possible nobody employed on day -- left join previous results calendar table again 0 such days (     select         dbo.calendar.dt         ,isnull(cte_employedpeople.people, 0) people             dbo.calendar         left join cte_employedpeople on dbo.calendar.dt = cte_employedpeople.dt             (dbo.calendar.dt >= '2015-01-01')         , (dbo.calendar.dt <= '2015-06-30') ) -- simple average of daily numbers select avg(people) avgpeople cte_daily;