Re: Aggregate SQL Query with multiple master-detail tables

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 6 Mar 2003 23:07:55 GMT
Message-ID: <b48kcb$1sevc4$1@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

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html

Received on Thu Mar 06 2003 - 17:07:55 CST