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