I'm developing a system to process recurring billing for members. Items to be purchased can be grouped together for a special package rate, or purchased individually for a higher, stand-alone rate. The portion of my database schema that determines the amount to be paid for recurring items consists of the following 4 tables:
MEMBER_RECUR_GROUP
member_id (PK)
recur_group_id (PK)
RECUR_GROUP
recur_group_id (PK)
RECUR_GROUP_ITEM
recur_group_id (PK)
recur_item_id (PK)
recur_amount
RECUR_ITEM
recur_item_id (PK)
item_code
recur_term
Basically, items are attached to recur groups and then associated with members. The same item can appear in multiple recur groups for a given member.
For example, let's say we have 3 simple items: apple, orange, and banana. There can be a recur group containing all 3 (individual item prices in this group would be the cheapest, say $1 each). There can also be a recur group for just 2 items, say orange and banana. And there can be a recur group for just apple (it's stand-alone price, say $2, would higher than it's "packaged" price within the other groups), another for just orange, and another for just banana.
Given a member_id and one or many item_codes (probably a CSV, something like "apple, banana"), I would like a query to return the recur_term and recur_amount for each item_code from the recur_group that contains all of the item_codes. If there is not a recur_group that contains all of the items, then the recur_term and recur_amount values from the stand-alone recur_group for the individual items should be returned. Is this possible to achieve in a single query?
I am using SQL Server 2005 if implementation matters. Thank you!
Here is the sample data, with expected result below.
MEMBER_RECUR_GROUP
1, 1
1, 3
1, 4
1, 5
RECUR_GROUP
1
2
3
4
5
RECUR_GROUP_ITEM
1, 1, 1.00
1, 2, 1.00
1, 3, 1.00
2, 2, 1.50
2, 3, 1.50
3, 1, 2.00
4, 2, 2.00
5, 3, 2.00
RECUR_ITEM
1, apple, 3
2, orange, 3
3, banana, 3
If member ID 1 is auto-billed for apple, orange, and banana, the pricing for recur group ID 1 is used.
If member ID 1 is auto-billed for apple and orange, the combined pricing for recur group ID 3 and 4 is used. (Because member ID 1 doesn't have a recur group with apple and orange.)
If member ID 1 is auto-billed for orange and banana, the pricing for recur group ID 2 is used.
If member ID 1 is auto-billed for apple, the pricing for recur group ID 3 is used.
If member ID 1 is auto-billed for orange, the pricing for recur group ID 4 is used.
If member ID 1 is auto-billed for banana, the pricing for recur group ID 5 is used.