Re: simple sql question/mlml
From: Michel Lee <ae299_at_FreeNet.Carleton.CA>
Date: 1997/10/11
Message-ID: <61oi8s$o6i_at_freenet-news.carleton.ca>#1/1
>>
>> 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 /-----\ \ \\-- \-- \--
>> /-----------------------------------------------/
Date: 1997/10/11
Message-ID: <61oi8s$o6i_at_freenet-news.carleton.ca>#1/1
Kluged (kluged_at_ix.netcom.com) writes: > how do you want your output formatted? did you want each transaction
I want it like this ->
> listed with the periods like: > "amount period1 period2 period3" > "amount period1 period2 period3" > "amount period1 period2 period3" > > > are the number dates in the transaction row fixed or variable?
the number of dates in a row is fixed,...
thx
mike
> > > Michel Lee wrote:
>>
>> 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 Sat Oct 11 1997 - 00:00:00 CEST