Re: Aggregate SQL Query with multiple master-detail tables

Forgot to mention we are using 9i. Is there another way of doing this with 9i vs 8i that would be more efficient? Thanks,

John

Rene Nyffenegger <rene.nyffenegger_at_gmx.ch> wrote in message news:<b48kcb$1sevc4$1_at_ID-82536.news.dfncis.de>...
> > Greetings,
> >
> > Can someone help me from dropping down to procedural code to
> > accomplish the following via SQL? I have 3 tables, here's what my data
> > looks like:
> >
> > INVOICE TABLE:
> > PATIENT INVOICE# LOCATION
> > 123 2222 Office
> >
> > TRANSACTION TABLE:
> > PATIENT INVOICE# TRANS# SERV_CODE PAY_CODE CHARGE_AMT PAID_AMT
> > 123 2222 1 A 99 200.00
> > 123 2222 2 B 99 200.00
> > 123 2222 3 C 99 100.00
> > 123 2222 4 77 100.00
> >
> > LINE_ITEM TABLE:
> > PATIENT INVOICE# TRANS# LINE# PAID_AMT
> > 123 2222 1 1 100.00
> > 123 2222 1 2 50.00
> >
> > The tables are joined on PATIENT, INVOICE#, and TRANS#. What I really
> > want to know is how much we got paid for a particular service code
> > (SERV_CODE). In the example above, TRANS# 1 has payments directly
> > associated with it via the LINE_ITEM table (100.00 and 50.00), but
> > payments for TRANS# 2 and 3 are not posted on the LINE_ITEM; they
> > should share the 100.00 PAID_AMT by a proportion of their CHARGE_AMT
> > in TRANS# 4.
> >
> > So the end result should look like
> >
> > PATIENT INVOICE# SERVICE_CODE CHARGE_AMT PAID_AMT
> > 123 2222 A 200.00 150.00
> > 123 2222 B 200.00 66.67
> > 123 2222 C 100.00 33.33
> >
> > Thanks to anybody who can help me out!
> >
> > John
>
>
>
> Which Version? On 9i, this would be much easier with 9i. Here's a solution
> that works on 9i, but should work on 8i as well. I couldn't test it as I don't
> have one at hand.
>
>
> --drop table trn;
> create table trn (
> pat number(3),
> inv number(4),
> trn number(1),
> sc char(1),
> amt_c number(5,2),
> amt_p number(5,2)
> );
>
> --drop table ln_it;
> create table ln_it (
> pat number(3),
> inv number(4),
> trn number(1),
> amt_p number(5,2)
> );
>
> insert into trn values(123,2222,1, 'A',200, 0);
> insert into trn values(123,2222,2, 'B',200, 0);
> insert into trn values(123,2222,3, 'C',100, 0);
> insert into trn values(123,2222,3,null, 0, 100);
>
> insert into ln_it values (123,2222,1,100);
> insert into ln_it values (123,2222,1, 50);
>
> select
> t.pat,
> t.inv,
> t.sc,
> sum(l.amt_p)
> from
> trn t,
> ln_it l
> where
> t.pat = l.pat and
> t.inv = l.inv and
> t.trn = l.trn
> group by
> t.pat, t.inv, t.sc
> union
> select
> t.pat,
> t.inv,
> t.sc,
> round(t.amt_c * s.sm / x.sm,2)
> from
> trn t,
> ln_it l,
> (select
> pat,inv,
> sum(amt_p) sm
> from
> trn
> where
> sc is null
> group by
> pat,inv,sc) s,
> (select
> t.pat,t.inv,
> sum(amt_c) sm
> from
> trn t, ln_it l
> where
> t.pat = l.pat (+) and
> t.inv = l.inv (+) and
> t.trn = l.trn (+) and
> --t.pat = s.pat (+) and
> --t.inv = s.inv (+) and
> --t.sc = s.sc (+) and
> l.trn is null and
> t.sc is not null
> group by t.pat,t.inv) x
> where
> t.pat = l.pat (+) and
> t.inv = l.inv (+) and
> t.trn = l.trn (+) and
> t.pat = s.pat (+) and
> t.inv = s.inv (+) and
> t.pat = x.pat (+) and
> t.inv = x.inv (+) and
> l.trn is null and
> t.sc is not null;
>
> hth
> Rene Nyffenegger
Received on Mon Mar 10 2003 - 16:05:13 CST