RE: [sqr-users] Max effective date evaluation not in sql

["Knapp, Richard" <KnappR@umsystem.edu>]

A crude bubble sort?  If A > B
                         if A > C
                             it's A
                         end-if
                      else
                         if B > C 
                            it's B
                         else
                            it's C
                         end-if
                      end-if

Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu


-----Original Message-----
From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org
[mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of
Debbie Larney
Sent: Monday, October 11, 2004 1:24 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] Max effective date evaluation not in sql

Everyone,

I have a simple issue that I can't seem to work out. I need to determine
the max effective dated row from the minimum row returned in three
different select statements. 
Here are my sample select statements

select com.effdt
from ps_company_tbl com
where com.effdt = (select 
       min(inner.effdt) 
     from ps_company_tbl inner 
     where inner.company = com.company and 
           inner.effdt  <= sysdate) 

select pay.effdt
from ps_paygroup_tbl pay
where pay.effdt = (select 
       min(inner.effdt) 
     from ps_paygroup_tbl inner 
     where inner.company = pay.company and 
     and inner.paygroup = pay.paygroup
           inner.effdt  <= sysdate) 

select loc.effdt
from ps_location_tbl loc
where loc.effdt = (select 
       min(inner.effdt) 
     from ps_location_tbl inner 
     where inner.company = loc.company and 
     and inner.location = loc.location
           inner.effdt  <= sysdate) 

Now that I have loc.effdt, pay.effdt and com.effdt how do I determine
which is the max effective dated row.

Thanks in advance

Deb



********************************************************************
CONFIDENTIALITY NOTICE:
The information contained in this e-mail is legally privileged and
confidential information intended only for the use of the individual or
entity to whom it is addressed.  If the reader of this message is not
the intended recipient, you are hereby notified that any viewing,
dissemination, distribution, or copying of this e-mail message is
strictly prohibited.  If you have received and/or are viewing this
e-mail in error, please immediately notify the sender by reply e-mail,
and delete this e-mail from your system.

Thank you.
********************************************************************


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users