Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Statementally Stumped

Statementally Stumped

Statementally Stumped

Ok, I have been working on this for the last two days and it shouldn't be that hard, i must be missing something.

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.

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.

What I tried was:

SELECT CompanyID, PlanID, Age, Max(EffectiveDate), Premium FROM Premiums GROUP BY CompanyID, PlanID, Age;

But, the result i get is not quite correct.

Assume that Company 1 has three years of data for plan 1 for a 65 year old person:

CompanyID PlanID Age EffectiveDate Premium
1 1 65 1/1/1996 $400.00
1 1 65 1/1/1997 $450.00
1 1 65 1/1/1998 $500.00

What i need is a result that looks like this:

CompanyID PlanID Age EffectiveDate Premium
1 1 65 1/1/1998 $500.00

but what i get is this:

CompanyID PlanID Age EffectiveDate Premium
1 1 65 1/1/1998 $400.00

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!

RE: Statementally Stumped

Got it with the help of the SQL forum.

Thanks anyway for listening.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close