Count employees per month with rollup, Sql Server

Count employees per month with rollup

Language: Layout:

DECLARE @A table (
eid int identity,
estart datetime,
eend datetime,
ecode int);
insert into @A (estart,eend,ecode) VALUES ('02/01/2016','03/31/2019',56),
('05/01/1995','01/06/2017',13),
('03/04/1995','10/20/2005',4),
('03/04/1995','06/19/2016',2),
('03/04/1995','12/15/2016',2847),
('03/04/1995',null,          1),
('03/04/1995',null,          5),
('03/04/1995',null,          3),
('02/06/1995','03/07/2009',9),
('02/06/1995',null,           8),
('02/06/1995',null,           7),
('02/06/1995',null,           6),
('09/15/1995','05/16/2017',34),
('09/15/1995',null,           33);

WITH mnths as (
  select CAST('01/15/1995' as date) as mnth UNION ALL
  select DATEADD(month,1,mnth) FROM mnths WHERE mnth<'12/15/2019'
), edata AS (
  SELECT mnth,
  (SELECT count(ecode) FROM @A A WHERE mnth between estart AND COALESCE(eend,getdate())) ecnt
  FROM mnths
)
SELECT year(mnth) yr, month(mnth) mn, AVG(ecnt+0.) emplcount
FROM edata 
group by year(mnth), month(mnth) 
WITH ROLLUP
OPTION (MAXRECURSION 1000)

View schema
Execution time: 0,03 sec, rows selected: 326, rows affected: 14, absolute service time: 0,3 sec, absolute service time: 0,3 sec

	yr	mn	emplcount
1	1995	1	0,000000
2	1995	2	4,000000
3	1995	3	10,000000
4	1995	4	10,000000
5	1995	5	11,000000
6	1995	6	11,000000
7	1995	7	11,000000
8	1995	8	11,000000
9	1995	9	13,000000
10	1995	10	13,000000
11	1995	11	13,000000
12	1995	12	13,000000
13	1995	NULL	10,000000
14	1996	1	13,000000
15	1996	2	13,000000
16	1996	3	13,000000
17	1996	4	13,000000
18	1996	5	13,000000
19	1996	6	13,000000
20	1996	7	13,000000
21	1996	8	13,000000
22	1996	9	13,000000
23	1996	10	13,000000
24	1996	11	13,000000
25	1996	12	13,000000
26	1996	NULL	13,000000
27	1997	1	13,000000
28	1997	2	13,000000
29	1997	3	13,000000
30	1997	4	13,000000
31	1997	5	13,000000
32	1997	6	13,000000
33	1997	7	13,000000
34	1997	8	13,000000
35	1997	9	13,000000
36	1997	10	13,000000
37	1997	11	13,000000
38	1997	12	13,000000
39	1997	NULL	13,000000
40	1998	1	13,000000
41	1998	2	13,000000
42	1998	3	13,000000
43	1998	4	13,000000
44	1998	5	13,000000
45	1998	6	13,000000
46	1998	7	13,000000
47	1998	8	13,000000
48	1998	9	13,000000
49	1998	10	13,000000
50	1998	11	13,000000
51	1998	12	13,000000
52	1998	NULL	13,000000
53	1999	1	13,000000
54	1999	2	13,000000
55	1999	3	13,000000
56	1999	4	13,000000
57	1999	5	13,000000
58	1999	6	13,000000
59	1999	7	13,000000
60	1999	8	13,000000
61	1999	9	13,000000
62	1999	10	13,000000
63	1999	11	13,000000
64	1999	12	13,000000
65	1999	NULL	13,000000
66	2000	1	13,000000
67	2000	2	13,000000
68	2000	3	13,000000
69	2000	4	13,000000
70	2000	5	13,000000
71	2000	6	13,000000
72	2000	7	13,000000
73	2000	8	13,000000
74	2000	9	13,000000
75	2000	10	13,000000
76	2000	11	13,000000
77	2000	12	13,000000
78	2000	NULL	13,000000
79	2001	1	13,000000
80	2001	2	13,000000
81	2001	3	13,000000
82	2001	4	13,000000
83	2001	5	13,000000
84	2001	6	13,000000
85	2001	7	13,000000
86	2001	8	13,000000
87	2001	9	13,000000
88	2001	10	13,000000
89	2001	11	13,000000
90	2001	12	13,000000
91	2001	NULL	13,000000
92	2002	1	13,000000
93	2002	2	13,000000
94	2002	3	13,000000
95	2002	4	13,000000
96	2002	5	13,000000
97	2002	6	13,000000
98	2002	7	13,000000
99	2002	8	13,000000
100	2002	9	13,000000
101	2002	10	13,000000
102	2002	11	13,000000
103	2002	12	13,000000
104	2002	NULL	13,000000
105	2003	1	13,000000
106	2003	2	13,000000
107	2003	3	13,000000
108	2003	4	13,000000
109	2003	5	13,000000
110	2003	6	13,000000
111	2003	7	13,000000
112	2003	8	13,000000
113	2003	9	13,000000
114	2003	10	13,000000
115	2003	11	13,000000
116	2003	12	13,000000
117	2003	NULL	13,000000
118	2004	1	13,000000
119	2004	2	13,000000
120	2004	3	13,000000
121	2004	4	13,000000
122	2004	5	13,000000
123	2004	6	13,000000
124	2004	7	13,000000
125	2004	8	13,000000
126	2004	9	13,000000
127	2004	10	13,000000
128	2004	11	13,000000
129	2004	12	13,000000
130	2004	NULL	13,000000
131	2005	1	13,000000
132	2005	2	13,000000
133	2005	3	13,000000
134	2005	4	13,000000
135	2005	5	13,000000
136	2005	6	13,000000
137	2005	7	13,000000
138	2005	8	13,000000
139	2005	9	13,000000
140	2005	10	13,000000
141	2005	11	12,000000
142	2005	12	12,000000
143	2005	NULL	12,833333
144	2006	1	12,000000
145	2006	2	12,000000
146	2006	3	12,000000
147	2006	4	12,000000
148	2006	5	12,000000
149	2006	6	12,000000
150	2006	7	12,000000
151	2006	8	12,000000
152	2006	9	12,000000
153	2006	10	12,000000
154	2006	11	12,000000
155	2006	12	12,000000
156	2006	NULL	12,000000
157	2007	1	12,000000
158	2007	2	12,000000
159	2007	3	12,000000
160	2007	4	12,000000
161	2007	5	12,000000
162	2007	6	12,000000
163	2007	7	12,000000
164	2007	8	12,000000
165	2007	9	12,000000
166	2007	10	12,000000
167	2007	11	12,000000
168	2007	12	12,000000
169	2007	NULL	12,000000
170	2008	1	12,000000
171	2008	2	12,000000
172	2008	3	12,000000
173	2008	4	12,000000
174	2008	5	12,000000
175	2008	6	12,000000
176	2008	7	12,000000
177	2008	8	12,000000
178	2008	9	12,000000
179	2008	10	12,000000
180	2008	11	12,000000
181	2008	12	12,000000
182	2008	NULL	12,000000
183	2009	1	12,000000
184	2009	2	12,000000
185	2009	3	11,000000
186	2009	4	11,000000
187	2009	5	11,000000
188	2009	6	11,000000
189	2009	7	11,000000
190	2009	8	11,000000
191	2009	9	11,000000
192	2009	10	11,000000
193	2009	11	11,000000
194	2009	12	11,000000
195	2009	NULL	11,166666
196	2010	1	11,000000
197	2010	2	11,000000
198	2010	3	11,000000
199	2010	4	11,000000
200	2010	5	11,000000
201	2010	6	11,000000
202	2010	7	11,000000
203	2010	8	11,000000
204	2010	9	11,000000
205	2010	10	11,000000
206	2010	11	11,000000
207	2010	12	11,000000
208	2010	NULL	11,000000
209	2011	1	11,000000
210	2011	2	11,000000
211	2011	3	11,000000
212	2011	4	11,000000
213	2011	5	11,000000
214	2011	6	11,000000
215	2011	7	11,000000
216	2011	8	11,000000
217	2011	9	11,000000
218	2011	10	11,000000
219	2011	11	11,000000
220	2011	12	11,000000
221	2011	NULL	11,000000
222	2012	1	11,000000
223	2012	2	11,000000
224	2012	3	11,000000
225	2012	4	11,000000
226	2012	5	11,000000
227	2012	6	11,000000
228	2012	7	11,000000
229	2012	8	11,000000
230	2012	9	11,000000
231	2012	10	11,000000
232	2012	11	11,000000
233	2012	12	11,000000
234	2012	NULL	11,000000
235	2013	1	11,000000
236	2013	2	11,000000
237	2013	3	11,000000
238	2013	4	11,000000
239	2013	5	11,000000
240	2013	6	11,000000
241	2013	7	11,000000
242	2013	8	11,000000
243	2013	9	11,000000
244	2013	10	11,000000
245	2013	11	11,000000
246	2013	12	11,000000
247	2013	NULL	11,000000
248	2014	1	11,000000
249	2014	2	11,000000
250	2014	3	11,000000
251	2014	4	11,000000
252	2014	5	11,000000
253	2014	6	11,000000
254	2014	7	11,000000
255	2014	8	11,000000
256	2014	9	11,000000
257	2014	10	11,000000
258	2014	11	11,000000
259	2014	12	11,000000
260	2014	NULL	11,000000
261	2015	1	11,000000
262	2015	2	11,000000
263	2015	3	11,000000
264	2015	4	11,000000
265	2015	5	11,000000
266	2015	6	11,000000
267	2015	7	11,000000
268	2015	8	11,000000
269	2015	9	11,000000
270	2015	10	11,000000
271	2015	11	11,000000
272	2015	12	11,000000
273	2015	NULL	11,000000
274	2016	1	11,000000
275	2016	2	12,000000
276	2016	3	12,000000
277	2016	4	12,000000
278	2016	5	12,000000
279	2016	6	12,000000
280	2016	7	11,000000
281	2016	8	11,000000
282	2016	9	11,000000
283	2016	10	11,000000
284	2016	11	11,000000
285	2016	12	11,000000
286	2016	NULL	11,416666
287	2017	1	9,000000
288	2017	2	9,000000
289	2017	3	9,000000
290	2017	4	9,000000
291	2017	5	9,000000
292	2017	6	8,000000
293	2017	7	8,000000
294	2017	8	8,000000
295	2017	9	8,000000
296	2017	10	8,000000
297	2017	11	8,000000
298	2017	12	8,000000
299	2017	NULL	8,416666
300	2018	1	8,000000
301	2018	2	8,000000
302	2018	3	8,000000
303	2018	4	8,000000
304	2018	5	8,000000
305	2018	6	8,000000
306	2018	7	8,000000
307	2018	8	8,000000
308	2018	9	8,000000
309	2018	10	8,000000
310	2018	11	8,000000
311	2018	12	8,000000
312	2018	NULL	8,000000
313	2019	1	8,000000
314	2019	2	8,000000
315	2019	3	8,000000
316	2019	4	7,000000
317	2019	5	7,000000
318	2019	6	0,000000
319	2019	7	0,000000
320	2019	8	0,000000
321	2019	9	0,000000
322	2019	10	0,000000
323	2019	11	0,000000
324	2019	12	0,000000
325	2019	NULL	3,166666
326	NULL	NULL	11,360000