Hi. I want to get the count of clients enrolled grouped by month and year. Having the user defined function as a view will make it easier to report on for me and having this is query form will give me a better understanding of the function:

The output I want to achieve Should look like this:

Total_Clients_Enrolled Month Year

252 1 2013

247 2 2013

303 3 2013

Here is an example of the table which is used:

CREATE TABLE B2H_STATUS (

CLT_NBR int,

EFFECT_DT datetime

B2H_STATUS int

)

INSERT INTO CLT_NBR EFFECT_DT B2H_STATUS VALUES

('14082','4/27/2009',1),

('14082','7/14/2009',2),

('14082','8/10/2009',3),

('14082','8/12/2009',4),

('9164','3/5/2009', 1),

('8990','3/5/2009', 1),

('8990','5/8/2009', 2),

('8990','6/8/2009', 3),

('8990','6/12/2009',4),

('13991','4/2/2009', 1),

('13991','5/21/2009',2),

('13991','6/9/2009', 3),

('13991','7/13/2009',4)

the ECMS.dbo.ufn_B2H_STATUSES_THROUGH_DT brings back this

CLT_NBRB2H_STATUSMax_Effect_DT

140824 8/12/2009

8990 4 6/12/2009

139914 7/13/2009

So the output I'm looking for would look like this:

Total_Clients_Enrolled Month Year

1 8 2009

1 6 2009

1 7 2009