Getting Top values from a table

[cpmasesa]

I have a table storing membership renewal details.
MemberID, RenewalDate, RenewalMonths, etc
ExpiryDate can be calculated as RenewalDate + (RenewalMonths * 30)
Each member can have several renewals in this file
I need to run a query that will show whether a membership is active or has expired.
Output would be something like: MemberID MembershipStatus.
MembershipStatus should be "Active" or "Expired" depending on whether latest ExpiryDate > Date()
Conceptualy thats what i need to do but have not been able to do it for the past 3 days!
I do not want to use 'SELECT TOP...' as in JET SQL because it would not work on a different RDBMS.
Could somebody pls help me out

TIA

 

[NVSbe]

If this table has unique id's, and if these id's augment as time go up, you can get the last renewal item for each member with

select memberid, decode(renewaldate + (renewalmonths*30) > date(), "Active", "Inactive&quot from tbl
where id in (select max(id)
from tbl
group by memberid)


But if your table has no id field... I'd suggest using the select TOP --------------------------------------
It's not the monsters under your bed, it is the men next door.
That make you fear, make you cry. Make you cry for the Child.
All the wars are fought amongst those lonely men. Unharmed, unscarred.

 

[r937]

the ansi sql equivalent to oracle's proprietary DECODE is CASE

solution doesn't need TOP, it needs a correlated subquery with MAX()

for CURRENT DATE, substitute DATE() or whatever the function happens to be in your database

select MemberID
     , case
        when RenewalDate+RenewalMonths*30 
             > CURRENT DATE
        then "Active" 
        else "Expired"
       end as MembershipStatus 
  from memberships XXX
 where RenewalDate =
       ( select max(RenewalDate)
           from memberships 
          where MemberID = XXX.MemberID )

rudy

 

[cpmasesa]

Thanks for the response.

I tried the sql against an access 2000 database, it does not seem to work. Checking out the help in access, there doesnot seem to be anything about 'case' in jet sql?

Is there a solution that i can use against an access database?

TIA

cpmasesa

 

[r937]

cpmasesa, i would just like to point out that you posted this in the Ansi SQL forum, where CASE is perfectly valid

i see that you also posted it as thread701-388947 in the Microsoft: Access Queries and JET SQL Forum, so i shall answer it there

rudy

 

[barbarul2001]

I know how hard can be a deadline problem so I help as soon as I can.
I think this is what you want...

SELECT Iif(DateAdd("d",t1.RenewalMonths*30,t1.RenewalDate)>date(),"Active", "Expired&quot
FROM tbl t1
where t1.RenewalDate =
( select max(tbl.RenewalDate)
from tbl
where tbl.MemberID = t1.MemberID )
Senior Developer / DBA

 

[cpmasesa]

Thanks everyone.

I finally got a solution.
See Thread701-388947
It seems that i should have posted my problem in the JET SQl Forum.

Thanks everyone for your suggestions

cpmasesa