Ok, I have been working on this for the last two days and it shouldn't be that hard, i must be missing something.<br>
<br>
I have a table of Insurance Premiums that contains the CompanyID, the PlanID, the Age of the person requesting the plan, the rate for that plan from that company, and the date that the rate became active. Now, there are several years of information in the table. So, as a company changes its rates, there is simply a new row with the same CompanyID, PlanID, and Age but with different EffectiveDates and Rates.<br>
<br>
My problem is, i need to select the premiums that correspond to the most recent date in the table for each company, plan, and age.<br>
<br>
What I tried was:<br>
<br>
SELECT CompanyID, PlanID, Age, Max(EffectiveDate), Premium FROM Premiums GROUP BY CompanyID, PlanID, Age;<br>
<br>
But, the result i get is not quite correct.<br>
<br>
Assume that Company 1 has three years of data for plan 1 for a 65 year old person:<br>
<br>
CompanyID PlanID Age EffectiveDate Premium<br>
1 1 65 1/1/1996 $400.00<br>
1 1 65 1/1/1997 $450.00<br>
1 1 65 1/1/1998 $500.00<br>
<br>
What i need is a result that looks like this:<br>
<br>
CompanyID PlanID Age EffectiveDate Premium<br>
1 1 65 1/1/1998 $500.00<br>
<br>
but what i get is this:<br>
<br>
CompanyID PlanID Age EffectiveDate Premium<br>
1 1 65 1/1/1998 $400.00<br>
<br>
It takes the max effectivedate just fine, but it doesn't return the premium associated with that date.. just some other premium (i think the first one it finds). Any ideas on how to rewrite the SQL to make it work? I know, it's a lot of thought, but i'm stumped and the deadline is approaching quickly! Thanks!<br>
<br>
I have a table of Insurance Premiums that contains the CompanyID, the PlanID, the Age of the person requesting the plan, the rate for that plan from that company, and the date that the rate became active. Now, there are several years of information in the table. So, as a company changes its rates, there is simply a new row with the same CompanyID, PlanID, and Age but with different EffectiveDates and Rates.<br>
<br>
My problem is, i need to select the premiums that correspond to the most recent date in the table for each company, plan, and age.<br>
<br>
What I tried was:<br>
<br>
SELECT CompanyID, PlanID, Age, Max(EffectiveDate), Premium FROM Premiums GROUP BY CompanyID, PlanID, Age;<br>
<br>
But, the result i get is not quite correct.<br>
<br>
Assume that Company 1 has three years of data for plan 1 for a 65 year old person:<br>
<br>
CompanyID PlanID Age EffectiveDate Premium<br>
1 1 65 1/1/1996 $400.00<br>
1 1 65 1/1/1997 $450.00<br>
1 1 65 1/1/1998 $500.00<br>
<br>
What i need is a result that looks like this:<br>
<br>
CompanyID PlanID Age EffectiveDate Premium<br>
1 1 65 1/1/1998 $500.00<br>
<br>
but what i get is this:<br>
<br>
CompanyID PlanID Age EffectiveDate Premium<br>
1 1 65 1/1/1998 $400.00<br>
<br>
It takes the max effectivedate just fine, but it doesn't return the premium associated with that date.. just some other premium (i think the first one it finds). Any ideas on how to rewrite the SQL to make it work? I know, it's a lot of thought, but i'm stumped and the deadline is approaching quickly! Thanks!<br>