Assign value based on criteria in anothe table

[EORTIZ]

I have 2 tables:

Claims table- claim ID, Member_ID, service_from_da te, service_end_dat e.
Plan table- Member_ID, Plan_ID, Plan_from_date, Plan_end_date. The same member_id can have multiple Plan_ID with different dates.

I need to assign the Plan_ID to each claim ID. The criteria is that the service_from_da te has to be greater than or equal the Plan_start_date and equal or lower than the Plan_end_date. For claims that are left without the Plan ID, because they are out of range, assign the Plan_ID with Plan_from_date that is lower and closer to the service_from_da te.

[EORTIZ]

I have 2 tables:

Claims table- claim ID, Member_ID, service_from_da te, service_end_dat e.
Plan table- Member_ID, Plan_ID, Plan_from_date, Plan_end_date. The same member_id can have multiple Plan_ID with different dates.

I need to assign the Plan_ID to each claim ID. The criteria is that the service_from_da te has to be greater than or equal the Plan_start_date and equal or lower than the Plan_end_date. For claims that are left without the Plan ID, because they are out of range, assign the Plan_ID with Plan_from_date that is lower and closer to the service_from_da te.

Additional explanation of question:

If service date is between the plan period the statement would be:
if service_from_da te>=plan_from_d ate and service_from_da te<=plan_end_da te

But, if the service date is not in the range of the plan dates,

for example: Service_from_da te=1/15/2008,and let's say that the member has

Plan_ID=1 for Plan_from_date= 12/1/2007 to Plan_end_date=1 2/31/2007

Plan_ID=2,Plan_ from_date=1/1/2008 to Plan_end_date=1/31/2008

Plan_ID=3,Plan_ from_date=2/1/2008 to Plan_end_date=3/31/2008

I need it assigns Plan_ID=2, which is the plan that was just before the date of service.

[Atli]

So, basically, you need something like:
[code=sql]
SELECT plan.ID FROM plan
INNER JOIN claim
ON claim.ID = <someID>
AND claim.from_date BETWEEN plan.start_date AND plan.end_date
[/code]
Would that work?

A question...
Are the claims meant to be linked to a plan?
If so, then why is there no reference in the claim table to the plan table?

[EORTIZ]

It does not work completely, buy partially. Ideally, none of the claim service_date, should be outside the range. However, it happens. So in the case that the service_date falls outside the Plan's coverage periods and because the member can change Plan_ID from time to time, I need to assign to the claim the Plan_ID that was just before the service_date. See example I presented above.

[Atli]

Ok, but in your example, the claim date falls perfectly within the second plan..
1/1/2008 < 1/15/2008 < 1/31/2008
right?

So... if your claim start date does not fall within the plan, you want the closes thing selected?

You could try:
[code=sql]
SELECT claimID, planID, diff
FROM (
SELECT
plan.ID as planID, claim.ID AS claimID,
ABS(TIMEDIFF(pl an.start, claim.start)) as diff
FROM plan INNER JOIN claim
ORDER BY claimID, diff ASC, planID
) AS dynTbl
GROUP BY claimID
[/code]
It basically calculates the distance between all of the claim and plan start times and returns the one with the lowest value.

P.S.
Why do people use 'month/day/year' format for dates?
Using 'year/month/day' makes much more sense :P

[EORTIZ]

Ok, but in your example, the claim date falls perfectly within the second plan..
1/1/2008 < 1/15/2008 < 1/31/2008
right?

So... if your claim start date does not fall within the plan, you want the closes thing selected?

You could try:
[code=sql]
SELECT claimID, planID, diff
FROM (
SELECT
plan.ID as planID, claim.ID AS claimID,
ABS(TIMEDIFF(pl an.start, claim.start)) as diff
FROM plan INNER JOIN claim
ORDER BY claimID, diff ASC, planID
) AS dynTbl
GROUP BY claimID
[/code]
It basically calculates the distance between all of the claim and plan start times and returns the one with the lowest value.

P.S.
Why do people use 'month/day/year' format for dates?
Using 'year/month/day' makes much more sense :P

Well, how could I combine the two conditions to be able to obtain a Plan Id for each claim? If the service falls in the plan date range, or if it is outside to the range. The objective is to assign only one Plan ID to each claim.

P.S. Maybe it is of nature to complicate things more than they are :-) Nos ahogamos en un vaso de agua.

[Atli]

I think the best way to go about all this is to create a function that finds the best fitted plan ID.

You could have it take a TimeStamp as a parameter and return the ID of the plan that fits best:
[code=mysql]
CREATE FUNCTION getPlanID(stamp TimeStamp)
RETURNS int
[/code]

You could start by having it search for an exact match
[code=mysql]
SELECT plan.ID INTO returnValue
FROM plan
WHERE stamp BETWEEN plan.start_date AND plan.end_date
LIMIT 1;
[/code]

And if that doesn't work, look for the closes thing to it:
[code=mysql]
IF returnValue IS NULL THEN
SELECT plan.ID INTO returnValue FROM plan
ORDER BY ABS(TIMEDIFF(pl an.start_date, stamp)) ASC
LIMIT 1;
END IF;
[/code]

Then you could call the function in another query and get the plan ID for each Claim:
[code=sql]
SELECT claim.ID AS claimID, getPlanID(claim .start_date) AS planID FROM claim;
[/code]

Check out the code in this page if your having trouble putting it all together.