Need SQL Help, Using MySQL

Android · Jul 30, 1999

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!

bitbrain · Aug 3, 1999

From what I can see, your problem is that you are mixing aggregate & non-aggregate columns in your query - you should only have one or the other. 
 
Try breaking the problem into pieces. Create a query that retrieves the company, plan, age, & max eff date (no premium). 
 
Then, write another query that joins the above query to the table and retrieves the premium. Join on company, plan, age, and date. 
 
This should work. There is probably a more elegant way of doing it in a single SQL statement. 
 
Good luck

calahans · Aug 3, 1999

Try the following: 
 
1 create view as follows: 
 
create view test1 
as 
select companyid , max(effectivedate)'effectivedate' from premiums 
group by companyid; 
 
2 Change your query to something as follows: 
select * from premiuns,test1 
where premiums.companyid=test1.companyid 
and premiums.effectivedate = test1.effectivedate ; 
 
This is a very rough effort. You will need to change the select * from .... to something like select premiums.companyid .. as you will have non unique column names. 
 
I think this should give you the desired results. Let me know if you need more info. 
 
C

Android · Aug 3, 1999

Ok! Sorry to say that I didn't do it either way. I added some new columns and rearranged things a bit so that instead of trying to look up the most recent update, the most recent up is actually listed so i just did the join where Premiums.EffectiveDate = Companies.LastUpdate. 
 
This cut out some of the ... i don't know... flexibility of the application as it only allows on most recent update. But since, all the companies update all their plans at the same time, it should hold water... for now.

Thanks for your help, btw. I can't use views in MySQL. it's one of the things they don't support, unfortunately. They do, however, support so many other things and have such good performance that it's worth it to me. 
 
Thanks again!

CRoberts · Aug 4, 1999

I have never used MySQL, just Oracle. However, if MySQL supports subqueries, then you could use the following query. This query will eliminate the need for either a view or scratch table. This example requires that some sort of Maximum function exists within MySQL that will work properly with a date column (Assuming EffectiveDate is a date datatype). 
 
SELECT CompanyID, PlanID, Age, EffectiveDate, Premium 
FROM Premiums 
WHERE (CompanyID, EffectiveDate) IN 
(SELECT CompanyID, MAX(EffectiveDate) 
FROM Premiums 
GROUP BY CompanyID)

calahans · Aug 5, 1999

On the above suggestion of a subquery, I have the following note to make. 
 
SQL Server,Access, Ingres and indeed ANSI SQL (I'm not sure about Oracle) DOES NOT allow you to have multiple columns in the where clause ie the following 
where (companyID,Effectivedate) in (select ....) 
would give you an SQL syntax error. 
 
C

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Need SQL Help, Using MySQL

Android

Programmer

bitbrain

Programmer

calahans

Programmer

Android

Programmer

CRoberts

MIS

calahans

Programmer

Similar threads

Part and Inventory Search

Sponsor