Re: Multitable select with two seperate search criteria.
In article <5k4ucb$hg5@paperboy.ids.net>,
macphee@conan.ids.net (Donald Mac Phee Ii) wrote:
>table1 =
>Itemlink (serial)
>DollarAmt (float)
>
>table2 =
>Date (Date)
>Detaillink (serial) [Corresponds to Itemlink]
>Accode (char)
>
>table3 =
>Accode (char)
>Address (char)
>
>This is an oversimplification, but will do for the purposes of my
>question.
>
>Itemlink is a nonunique number and can have multiple entries in table1.
>Detaillink is unique in table two.
>
>What I want to do is get the sum of all the DollarAmt by account for two
>distinct ranges of date, compare them, and output the results of the
>comparison.
>
>Or more definitively: Produce a report, sorted by account, summing all
>dollar activity for week1, and week2; comparing all of the totals and
>producing a third value for percentage change, and then output the results
>in a column format listing Accode, address, week1, week2, %change
>
>Seeing as how I only have the runtime of SQL, and no compiled language, it
>must be done using isqlrf, biff, awk, csh, sh, etc...
>
>My first few attempts have yielded promising results, but I can't seem to
>make awk process all of those rows, and I'm pretty sure there must be an
>easier way of accomplishing this using ISQL...
>
Hi,
You can try using an intermediate table, followed by a self-join.
Step 1 : Create a intermediate table
create table temp_table (
accode char(?),
weekno smallint, { should not be decimal}
totamt money
);
Step 2 : Insert information into this table from table1 & 2
insert into temp_table
select accode,
(trx_date - '1/1/97')/7, # Converts to integer weekno
sum (dollaramt)
from table1, table2
group by acccode, 2
Note : Replace '1/1/97' by a date which return weekno of trx_date
appropriately.
Step 3 : Examine temp_table to assess the weekno to be used in Step 4.
Step 4 : Retrieve final result
select a.accode Acccode, a.totamt Week1, b.totamt Week2,
100 * (b.totamt-a.totamt)/ a.totamt %Change
from temp_table a, temp_table b
where a.accode = b.accode
and a.weekno = 17 # Value determined in Step 3.
and b.weekno = 16
Ideally, Step 1,2 and 3 would be a single sql script returning you
values to be used for the script in Step 4.
HTH.
-----------------------
Rudy Fernandes
GIC, Kuwait
OL 7.20UC4, 4GL 6.04UC1
-----------------------