Gregory
Wed Dec 03 07:34:32 CST 2003
Andrew,
You have A1, 1, 1, 1 in BookOut. So it is used I guess
Here are two possible solutions
2nd one will be faster if yiu have an index on Contain+Store1+store2+store3
in BookIn, BookOut and Location.
It all depends on the data volume
(1) We first get the max dates into a cursor : 2 selects
(2) we select location.* outer join with max dates : 3rd select
*____________________________
&& max dates
select Contain, ;
Store1, ;
store2, ;
store3, ;
Max(date) as date ;
from BookIn ;
into cursor Max_tmp_ ;
group by 1,2,3,4 ;
union ;
select Contain, ;
Store1, ;
store2, ;
store3, ;
Max(date) as date ;
from Bookout ;
group by 1,2,3,4 ;
select Contain, ;
Store1, ;
store2, ;
store3, ;
Max(date) as date ;
from max_tmp_ ;
into cursor max_tmp ;
group by 1,2,3,4
select Location.Contain, ;
location.Store1, ;
location.Store2, ;
location.store3, ;
nvl(max_tmp.date, {}) as Date ;
from location ;
left join max_tmp ;
on ( Location.Contain == max_tmp.Contain ) ;
and ( Location.store1 == max_tmp.store1 ) ;
and ( Location.store2 == max_tmp.store2 ) ;
and ( Location.store3 == max_tmp.store3 ) ;
into cursor Solution1 ;
&& solution 2
select Contain+Store1+store2+store3 as Id, ;
Max(date) as date ;
from BookIn ;
into cursor Max_tmp_ ;
group by 1 ;
union ;
select Contain+Store1+store2+store3 as Id, ;
Max(date) as date ;
from Bookout ;
group by 1
select Id, ;
Max(date) as date ;
from max_tmp_ ;
into cursor max_tmp ;
group by 1
select Location.Contain, ;
location.Store1, ;
location.Store2, ;
location.store3, ;
nvl(max_tmp.date, {}) as Date ;
from location ;
left join max_tmp ;
on ( Contain+Store1+store2+store3 == max_tmp.Id ) ;
into cursor Solution2 ;
order by 1,2,3,4
select Solution1
brow nowait
select Solution2
brow nowait
*------------------------------
"Andrew Howell" <ajh@work> wrote in message
news:e7WlmeZuDHA.2712@tk2msftngp13.phx.gbl...
> Gregory Adam wrote:
> > Hi Andrew,
> >
> > Do BookIn and BookOut contain a locationID ?\
> >
> > What is the layout of the location table re join fields ?
> >
> > I'm pretty sure it can be done in a couple of sql statements and
> > considerably faster than the scan/endscan
>
> Thanks for the continued support..
> Since you ask I shall put all the gory details here..
>
> * I've tried to put all test cases in;
> * A1, 1, 1, 0 - contents changed; used since change
> * A1, 1, 1, 1 - contents changed; not used since change
> * A1, 1, 1, 2 - something just booked in
> * A1, 1, 1, 3 - something just booked out
> * A1, 1, 1, 4 - something booked in and out twice
> * A1, 1, 1, 5 - something just booked in twice
> * A1, 1, 1, 6 - something just booked out twice
> * A1, 1, 1, 7 - something booked in and out once
> * A1, 1, 1, 8 - not used at all
>
> * expected results:
> * british date formats ;)
>
> * A1, 1, 1, 0 - 23/01/2003
> * A1, 1, 1, 1 - / / [ie not used]
> * A1, 1, 1, 2 - 01/01/2003
> * A1, 1, 1, 3 - 15/03/2003
> * A1, 1, 1, 4 - 21/01/2003
> * A1, 1, 1, 5 - 28/02/2003
> * A1, 1, 1, 6 - 14/11/2003
> * A1, 1, 1, 7 - 28/11/2003
> * A1, 1, 1, 8 - / / [ie not used]
>
>
> CREATE CURSOR location ;
> (spareref N(6,0), ;
> contain C(5), ;
> store1 C(5), ;
> store2 C(5), ;
> store3 C(5))
> CREATE CURSOR bookout ;
> (spareref N(6,0), ;
> contain C(5), ;
> store1 C(5), ;
> store2 C(5), ;
> store3 C(5), ;
> date D)
> CREATE CURSOR bookin ;
> (spareref N(6,0), ;
> contain C(5), ;
> store1 C(5), ;
> store2 C(5), ;
> store3 C(5), ;
> date D)
>
> * populate some bin locations..
> INSERT INTO location ;
> VALUES (0, 'A1', '1', '1', '0')
> INSERT INTO location ;
> VALUES (1, 'A1', '1', '1', '1')
> INSERT INTO location ;
> VALUES (1, 'A1', '1', '1', '2')
> INSERT INTO location ;
> VALUES (2, 'A1', '1', '1', '3')
> INSERT INTO location ;
> VALUES (3, 'A1', '1', '1', '4')
> INSERT INTO location ;
> VALUES (5, 'A1', '1', '1', '5')
> INSERT INTO location ;
> VALUES (6, 'A1', '1', '1', '6')
> INSERT INTO location ;
> VALUES (7, 'A1', '1', '1', '7')
> INSERT INTO location ;
> VALUES (8, 'A1', '1', '1', '8')
>
> * and some things coming out of them
> INSERT INTO bookout ;
> VALUES (4, 'A1', '1', '1', '0', {13/01/2003})
> INSERT INTO bookout ;
> VALUES (0, 'A1', '1', '1', '0', {23/01/2003})
> INSERT INTO bookout ;
> VALUES (3, 'A1', '1', '1', '4', {17/01/2003})
> INSERT INTO bookout ;
> VALUES (3, 'A1', '1', '1', '4', {21/01/2003})
> INSERT INTO bookout ;
> VALUES (4, 'A1', '1', '1', '1', {01/01/2003})
> INSERT INTO bookout ;
> VALUES (2, 'A1', '1', '1', '3', {15/03/2003})
> INSERT INTO bookout ;
> VALUES (6, 'A1', '1', '1', '6', {13/10/2003})
> INSERT INTO bookout ;
> VALUES (6, 'A1', '1', '1', '6', {14/11/2003})
> INSERT INTO bookout ;
> VALUES (7, 'A1', '1', '1', '7', {21/11/2003})
>
> * and some other things going into them..
> INSERT INTO bookin ;
> VALUES (0, 'A1', '1', '1', '0', {14/01/2003})
> INSERT INTO bookin ;
> VALUES (1, 'A1', '1', '1', '2', {01/01/2003})
> INSERT INTO bookin ;
> VALUES (3, 'A1', '1', '1', '4', {19/01/2003})
> INSERT INTO bookin ;
> VALUES (3, 'A1', '1', '1', '4', {20/01/2003})
> INSERT INTO bookin ;
> VALUES (5, 'A1', '1', '1', '5', {11/02/2003})
> INSERT INTO bookin ;
> VALUES (5, 'A1', '1', '1', '5', {28/02/2003})
> INSERT INTO bookin ;
> VALUES (7, 'A1', '1', '1', '7', {28/11/2003})
>
>
> --
> Regards
> Andrew Howell
>
>