query problem - urgent

[tosh]

Hi..

would appreciate if anybody can help me with this
the query is to find out termed groups based on expiratoin dates.
i.e if the group has expiration date in 12/31/2004.and if they did not reneiwed their membership tehn the group is said to be termed for jan'2005.
i need a query to find out the termed groups for last year.

here is the sample query :


SELECT RATING_GRP_NUM,min(RATING_GRP_NAM)
FROM ENT.RATING_GROUP
WHERE rec_exp_dt is null and
RATING_GRP_SK IN
(SELECT RATING_GRP_SK FROM
ENT.GRP_CONTRACT_COV
WHERE REC_EXP_DT IS NULL
GROUP BY RATING_GRP_SK
HAVING MAX(EXP_dT_SK) = to_date('12/31/2004','mm/dd/yyyy'))
group by rating_grp_num


my requirement is if the group has max expiration date in dec' they would be termed in next month i.e jan in the above query.
with the above query I actually get the groups which are termed in nov.
what would be the query for findout the temed groups for one year.
as per the logic it should be the max(exp_dt) + 1


Thanks

[culonbu]

i will assist helper if you post create table and insert data statement to create test data.

[waitecj]

Sounds to me like you are in need of ADD_MONTHS()...

Take a look at this...