Re: simple sql question/mlml
From: Gerard H. Pille <ghp_at_santens.be>
Date: 1997/10/08
Message-ID: <01bcd3cb$2a803b80$7b1340c0_at_pcghp.santens.be>#1/1
Date: 1997/10/08
Message-ID: <01bcd3cb$2a803b80$7b1340c0_at_pcghp.santens.be>#1/1
The first solution with an index on end_date seems more appropriate
--
------------
Kind reGards
\ / |
X |
/ \ x
Gerard
Michel Lee <ae299_at_FreeNet.Carleton.CA> schreef in artikel
<61eumn$537_at_freenet-news.carleton.ca>...
>
>
>
> actually i was thinking of doing this:
> create a 'period' table with 2 columns:
> date and period and index on date.
> but i will still have to perform this query(which is still
> a join), do you think its the best way?:
>
> select t.amt, d1.per, d2.per, d3.per, d4.per
> from
> transactions t,
> fiscalyear d1,
> fiscalyear d2,
> fiscalyear d3,
> fiscalyear d4
> where
> t.date1 = d1.datee and
> t.date2 = d2.datee and
> t.date3 = d3.datee and
> t.date4 = d4.datee;
>
>
>
> mike
>
> Michel Lee (ae299_at_FreeNet.Carleton.CA) writes:
> > Hi All,
> >
> > I have this 'newbie' sql question , i hope some has encountered.
> > (im pretty sure someone knows the answer)
> >
> > I have 2 tables.
> > 'fiscalyear' defines fiscal periods with date ranges.
> > e.g.
> > -------------------------------------
> > | PER | BEGIN_DATE | END_DATE |
> > | 9701 | 10-JAN-97 | 13-FEB-97 |
> > | 9702 | 14-FEB-97 | 11-MAR-97 |
> > ...
> > 'transactions' are transactions with many dates.
> > e.g.
> > --------------------------------------------
> > | AMT | DATE1 | DATE2 | DATE3 | DATE4 |
> > ...
> >
> > How do i display each transaction row with these dates 'converted' to
> > their
> > fiscal periods?
> > my attempt is
> >
> > select t.amt, d1.per, d2.per, d3.per, d4.per
> > from
> > transactions t,
> > fiscalyear d1,
> > fiscalyear d2,
> > fiscalyear d3,
> > fiscalyear d4
> > where
> > t.date1 between begin_date and end_date and
> > t.date2 between begin_date and end_date and
> > t.date3 between begin_date and end_date and
> > t.date4 between begin_date and end_date ;
> >
> >
> > but this sounds very inefficient since its count a cartesion product
> > with all the joins. (my report runs a very long time)
> >
> >
> > yr help is appreciated.
> >
> > Mike
> >
> >
> >
> > --
> > /-----------------------------------------------/ \--\--\ \-- \--
\--
> > / www.ncf.carleton.ca/~ae299 /---\ \ \ \\ \
\
> > / ae299_at_freenet.carleton.ca /-----\ \ \\--
\-- \--
> > /-----------------------------------------------/
>
>
> --
> /-----------------------------------------------/ \--\--\ \-- \--
\--
> / www.ncf.carleton.ca/~ae299 /---\ \ \ \\ \ \
> / ae299_at_freenet.carleton.ca /-----\ \ \\-- \--
\--
> /-----------------------------------------------/
>
Received on Wed Oct 08 1997 - 00:00:00 CEST