I'm puzzled as to the best way to go about retreiving a dataset - not sure
if it can be done in a single sql query or whether it would have to be done
programmatically with several queries.

a single table called grid_history with attributes:

last_change (varchar2(10))
cmdy (varchar2(10))
gv number

last_change is the date a cmdy record was inserted, cmdy represents the name
of a commodity and gv is a number. insertion of a record represents the
altering of the gv for the cmdy - hence the name "grid_history"

for a single cmdy there could be many records all with unique last_change
dates and gv's

the objective is given a "target date" to find the set of gv's that were "in
effect" at that point in time. So for each cmdy one wants to find the
max(last_change) <= target. Seems simple, but this value may be different
for different cmdys and you want the entire set - ie; on any given day, all
or none or some of the cmdys may get changed.

last_change cmdy gv
----------------------------------
1-1-01 ET 1
6-1-01 ET .9
1-1-02 ET .6
6-1-02 ET .9
1-1-03 ET 1
1-1-01 AT .2
5-1-01 AT 1
6-1-02 AT .7
9-1-02 AT .4
1-1-03 AT 1

there are n cmdys and n changes - I want to return the set of values in
effect at say: 10-27-02

6-1-02 ET .9
9-1-02 AT .4

Can I do this in a single query? I tried writing one using a subselect but
quickly confused myself.

Any ideas?

TIA

Mike

OK, I solved it by some further trial and error- (I think - unless a larger
sample set will prove my query to be incorrect or massively inefficient..)

Select * from grid_history a
where
last_change =
(select max(last_change) from grid_history
where
cmdy = a.cmdy and
last_change <= to_date('10-27-02','MM-DD-YY')
);

LAST_CHAN CMDY GV
--------- ------ ----------
01-JUN-02 ET .9
01-SEP-02 AT .4

To anyone who looked and even thought about maybe replying to my little
problem - thanks!

-Mike

"Mike Stenzler" <mstenzler (AT) ssaris (DOT) com> wrote


try this

select a.* ,tt.act,tt.grp,tt.effdate,tt.enddate from pay a ,
(select empno empno,act,grp,effdate,lead(effdate,1,to_date('5000 1231','yyyymmdd')
)
over (partition by empno order by empno,effdate) enddate from
chg) tt
where a.empno=tt.empno
and a.postdate>=effdate
* and a.postdate<=tt.enddate
this can run under 9i pl/sql

EMPNO POSTDATE AMT ACT GRP EFFDATE ENDDATE
------- ---------- ---------- ---------- ---------- ----------
----------
1010 10-1月 -04 2163 New AAAA 12-2月
-01 31-12月-00
1010 17-1月 -04 2645 New AAAA 12-2月
-01 31-12月-00
1010 24-1月 -04 2163 New AAAA 12-2月
-01 31-12月-00
1010 17-1月 -04 2163 New AAAA 12-2月
-01 31-12月-00
1010 17-1月 -04 2163 New AAAA 12-2月
-01 31-12月-00
1010 17-1月 -04 2163 New AAAA 12-2月
-01 31-12月-00
1010 17-1月 -04 2645 New AAAA 12-2月
-01 31-12月-00
1010 31-1月 -04 2354 New AAAA 12-2月
-01 31-12月-00
1011 10-1月 -04 2321 New CCCC
11-11月-02 18-1月 -04
1011 17-1月 -04 2211 New CCCC
11-11月-02 18-1月 -04
1011 24-1月 -04 2242 Upd BBBB 18-1月
-04 31-12月-00

EMPNO POSTDATE AMT ACT GRP EFFDATE ENDDATE
------- ---------- ---------- ---------- ---------- ----------
----------
1011 31-1月 -04 2211 Upd BBBB 18-1月
-04 31-12月-00
1012 17-1月 -04 2433 New EEEE 11-1月
-04 31-12月-00
1012 24-1月 -04 2246 New EEEE 11-1月
-04 31-12月-00
1012 31-1月 -04 2235 New EEEE 11-1月
-04 31-12月-00
1013 17-1月 -04 2766 New DDDD 11-1月
-04 18-1月 -04
1013 24-1月 -04 2661 Upd BBBB 18-1月
-04 25-1月 -04

"Mike Stenzler" <mstenzler (AT) ssaris (DOT) com> wrote

(select empno empno,act,grp,effdate,lead(effdate,1,to_date('5000 1231','yyyymmdd') )
over (partition by empno order by empno,effdate) enddate from chg) tt
where a.empno=tt.empno
and a.postdate>=effdate
and a.postdate<=tt.enddate