SQL select each event connected to another event by person, date range only

Question

I have a SQL Server 2008 R2 database with tables that track client enrollment, service events delivered to those clients, and a single later billing event for each enrollment.

I am having trouble generating a list of billing outcomes for each service event. Any guidance would be a godsend!

Simplified tables are:

Enrollment:

enrollment_rec_id
person_id
program_id
enrollment_start_date
enrollment_end_date

Service:

service_rec_id
person_id
date_of_service
program_id 

(Note: service_rec_id is not referenced in any other table).

The service.date_of_service must be between enrollment.enrollment_start_date and enrollment.enrollment_end_date.

Every billing event record must be associated with at least 1 service record but each service record can be associated with many or 0 billing event records.)

Billing:

 billing_rec_id
 person_id
 billing date
 number_of services_billed_for
 date_of_one_of_those_services
 amount_billed
 amount_received
 enrollment_start_date
 enrollment_end_date
 enrollment_rec_id

(the service for date_of_one_of_those_services is apparently chosen randomly by the person who enters these records from among the several services included. This is the only direct reference to a services record. They then enter a count of services billed and the aggregate dollar amount billed and received for that person/enrollment. ALSO, there can be multiple billing event records for the same person/enrollment. One might show 2 services billed and another 3 services billed when an enrollment had 5 services. ALSO there are many enrollments that have no billing event records at all, or have billing event records that list fewer services billed than service events in the enrollment date range)

Requested output includes which service.service_rec_id -- by person -- were billed for and a comparison of the number of service.service_rec_id s associated by date range/person with this billing.billing_rec_id. Did the number of service.service_rec_id in date range for each person = billing.number_of service_billed_for).

Output desired is something like

service.person_id, 
enrollment.program_id, 
enrollment.enrollment_start_date, 
enrollment.enrollment_end_date, 
service.service_rec_id, 
billing.billing date, 
billing.number_of services_billed_for,  
calculate_number_of_service_rec_id_for_this_billing_rec_id

Can anyone suggest a good strategy to pursue?

Answer 1

Onward into the fray, lets make some test data:

DECLARE @Enrollment TABLE
(   
    enrollment_rec_id INT,
    person_id INT,
    program_id INT,
    enrollment_start_date DATETIME,
    enrollment_end_date DATETIME
)

DECLARE @Service TABLE
(
    service_rec_id INT,
    person_id INT,
    date_of_service DATETIME,
    program_id INT
)

DECLARE @Billing TABLE
(
    billing_rec_id INT,
    person_id INT,
    billingdate DATETIME,
    number_of_services_billed_for INT,
    date_of_one_of_those_services DATETIME,
    amount_billed DECIMAL(19,6),
    amount_received DECIMAL(19,6),
    enrollment_start_date DATETIME,
    enrollment_end_date DATETIME,
    enrollment_rec_id INT
)

INSERT INTO @Enrollment
( enrollment_rec_id, person_id, program_id, enrollment_start_date, enrollment_end_date )
VALUES
( 1, 1, 1, '01-01-2014', '12-31-2014' ),
( 2, 1, 2, '01-01-2014', '12-31-2014' ),
( 3, 2, 3, '01-01-2014', '12-31-2014' );

INSERT INTO @Service
( service_rec_id, person_id, date_of_service, program_id )
VALUES
( 1, 1, '02-01-2014', 1 ),
( 2, 2, '03-01-2014', 1 ),
( 3, 1, '04-01-2014', 1 ),
( 4, 2, '05-01-2014', 2 ),
( 5, 1, '06-01-2014', 2 ),
( 6, 2, '06-01-2014', 2 );

INSERT INTO @Billing
( billing_rec_id, person_id, billingdate, number_of_services_billed_for, date_of_one_of_those_services, amount_billed, amount_received, enrollment_start_date, enrollment_end_date, enrollment_rec_id )
VALUES
( 1, 1, '10-01-2014', 3, '02-01-2014', 300.00, 150.00, '01-01-2014', '12-31-2014', 1),
( 2, 2, '10-01-2014', 3, '03-01-2014', 500.00, 333.00, '01-01-2014', '12-31-2014', 2);

Now lets join our billing and enrollment tables, when we will try to join the first service record and also get a max count of service records:

SELECT 
    s.person_id,
    e.program_id,
    e.enrollment_start_date,
    e.enrollment_end_date,
    s.service_rec_id,
    b.billingdate,
    b.number_of_services_billed_for,
    ns.Num_Of_Services_For_Program_Id
FROM @Billing b
INNER JOIN @Enrollment e
    ON e.enrollment_rec_id = b.enrollment_rec_id
OUTER APPLY 
(
    SELECT TOP 1 * FROM @Service s 
    WHERE s.program_id = e.program_id
        AND s.date_of_service BETWEEN e.enrollment_start_date AND e.enrollment_end_date
) s
OUTER APPLY
(
    SELECT COUNT(*) Num_Of_Services_For_Program_Id FROM @Service s 
    WHERE s.program_id = e.program_id
        AND s.date_of_service BETWEEN e.enrollment_start_date AND e.enrollment_end_date
) ns

Here is the output:

person_id   program_id  enrollment_start_date   enrollment_end_date service_rec_id  billingdate number_of_services_billed_for   Num_Of_Services_For_Program_Id
1   1   2014-01-01 00:00:00.000 2014-12-31 00:00:00.000 1   2014-10-01 00:00:00.000 3   3
2   2   2014-01-01 00:00:00.000 2014-12-31 00:00:00.000 4   2014-10-01 00:00:00.000 3   3

Maybe a starting point for working on your problem?