>>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.

Thanks Naomi,

I haven't tried your solution yet as I got it working another way but will try it out when I get the chance.