>Hi,
>
>I am working on an Insurance system where I need to work out a count of people enrolled in different plans on a monthly basis (for billing purposes).
>
>A person can be enrolled in one of three types of plans (Plan1, 2 & 3)
>They can move from one plan to another with the date of movement always the start of a month.
>
>So for instance I can enroll on 1st Jan 2011 in Plan1, then move to Plan2 as of 1st Mar 2011.
>
>Now I need to get a count of those enrolled in each plan for each of Jan, Feb and Mar 2011. So I would show up as belonging to Plan 1 in Jan and Feb and then in Plan 2 in March.
>
>The table is like this:
>
>Enrollment
>enr_Pk - Primary Key
>enr_EmpFk - foreign key to employee table
>enr_PlnFk - foreign key to Plan table
>enr_start - date of start of enrollment
>enr_end - date of end of enrollment
>
>Here's some code:
>
>

CREATE CURSOR Enrollment;
>(enr_Pk i,;
>enr_EmpFk i,;
>enr_PlnFk i,;
>enr_start d,;
>enr_end d null)
>
>INSERT INTO Enrollment VALUES (1, 1, 1, DATE(2011,1,1), DATE(2011,2,28))
>INSERT INTO Enrollment VALUES (2, 1, 2, DATE(2011,3,1), NULL)

>
>
>I also have a calendar table which has every date that could possibly be used in this system from 1st January 2000 to 31st December 2050.
>
>So if I let the user choose the start and end date (1st Jan to 31st March 2011 in this case), how would I get a return cursor like this:
>
>

Month    Plan Count
>Jan 2011 1    1
>Jan 2011 2    0
>Feb 2011 1    1
>Feb 2011 2    0
>Mar 2011 1    0
>Mar 2011 2    1

;with Combos as (select * from (select enr_PlnFk  from DistinctListOfPlans) X 
Cross JOIN (select The_Date from Calendar where day(The_Date) = 1
and The_Date between @StartDate and @EndDate) Y),

Info as (select C.*, T.enr_EmpFk  from Combos C LEFT JOIN Enrollment T 
ON C.enr_PlnFk  = T.enr_PlnFk  and C.The_Date between T.enr_start and coalesce(T.enr_End, '30000101'))

select datename(month, The_Date) as [Month], enr_PlnFk , count(enr_EmpFk ) as [Count] from Info
GROUP BY [The_Date], Plan

The above is from the top of my head - should give you an idea.