select s.emp, s.trtry, trunc(month/100) year, sum(sales) sales,
sum(decode(month, max_month, tax, 0)) tax
from sales_tab s,
(select max(month) max_month, emp, trtry from sales_tab group by emp,
trtry) g
where s.emp=g.emp and s.trtry=g.trtry
group by emp, trtry, trunc(month/100);
--
Alexander I.Doroshko, aid_at_grant.kharkov.ua
- Bank "Grant", Kharkov, Ukraine
mapascoe_at_my-deja.com wrote in article <7qlr5r$cuj$1_at_nnrp1.deja.com>...
:
:
: Rudy,
:
: Thanks for your clever reply. Unfortunately, your second assumption
is
: invalid in our case -- we cannot guarantee that tax amounts will
: increase or even remain the same. Any added suggestions?
:
: Also, to answer your first assumption, this table contains
information
: from a legacy system, so we have no choice on the design.
:
: In article <936192710.762677624_at_news.pathcom.com>,
: Rudy Fernandes <rferdy_at_pathcom.com> wrote:
: > It appears that your table stores Sales for the Month, but tax for
the
: year to
: > date. That's quite odd.
: >
: > However, on the assumption that there is some purpose being served
: with that
: > design and on a further important assumption that the tax value
only
: increases
: > during a given year (i.e. Sales tax for a month always exceed
: refunds), you
: > could get your results with the following view
: >
: > create view vx as
: > select emp, trtry, trunc(month/100) year, sum(sales) sales,
max(tax)
: tax
: > from sales_tab
: > group by emp, trtry, trunc(month/100);
: >
Received on Fri Sep 03 1999 - 03:32:10 CDT
