On Oct 26, 2:49 pm, ddf <orat...@msn.com> wrote:
> On Oct 25, 5:59 pm, Aine Ni She <ain...@gmail.com> wrote:
>
>
>
>
>
> > Hi,
>
> > I have 3 columns of data
>
> > Column 1:subscription
> > Column 2: invoice number
> > Column 3: Service
>
> > I need to seperate the subscription types into new, renewals and additional
> > which is fine but the next bit i am having trouble
>
> > Each invoice number can have 1 or more service
>
> > e.g.
> > Invoice Number Service
> > 123 Photocopying
> > 123 Printing
> > 123 Scan & Store
> > 234 Photocopying
> > 234 Scan & Store
> > 345 Photocopying
> > 345 Printing
>
> > I apply a rate for each service e.g.
> > photocopying = 1.5
> > printing = 1.7
>
> > but if Scan and store is in an invoice with photocopying we charge an extra
> > 1.5
>
> > but if printing is a service with the scan and store a different rate
> > applies 1.7
>
> > so i can't just count scan and store and apply a rate i have to figure out
> > if it is with photocopying or with printing and then apply the rate
>
> > What I want to be able to do is creat a table with columns that calculates
> > this
> > so i get a 4 columns:
>
> > Service usage rate total
> > photocopying 3 1.5 4.5
> > Printing 2 1.7 3.4
> > Scan & Store 1 1.5 1.5
> > Scan & Store w/Print 1 1.7 1.7
>
> > The problem comes in when i'm trying to count scan and store wit/without
> > printing. I can't figure it out.
>
> > I import the report from an excel spreadsheet into acces and want to run a
> > query that does all this...
>
> > thanks in advance,
> > ainese
>
> If you can add a column to your source table you can get this:
>
> SQL> Create table subscription(
> 2 subscr_type varchar2(15),
> 3 invoice number,
> 4 service varchar2(40),
> 5 service_cd number
> 6 );
>
> Table created.
>
> SQL>
> SQL> insert all
> 2 into subscription
> 3 values('RENEWAL',123,'Photocopying',0)
> 4 into subscription
> 5 values('RENEWAL',123,'Printing',2)
> 6 into subscription
> 7 values('RENEWAL',123,'Scan '||chr(38)||' Store',5)
> 8 into subscription
> 9 values('ADDITIONAL',234,'Photocopying',0)
> 10 into subscription
> 11 values('ADDITIONAL',234,'Scan '||chr(38)||' Store',5)
> 12 into subscription
> 13 values('NEW',345,'Photocopying',0)
> 14 into subscription
> 15 values('NEW',345,'Printing',2)
> 16 select * From dual;
>
> 7 rows created.
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> column services format a40
> SQL> break on report skip 1
> SQL> compute sum of photocopy printing scan_and_store
> scan_and_store_w_prt on report
> SQL>
> SQL> select subscr_type, invoice, services,
> 2 decode(bitand(service_cds, 0), 0, 1.5, 0) photocopy,
> 3 decode(bitand(service_cds, 2), 2, 1.7, 0) printing,
> 4 decode(bitand(service_cds, 7), 5, 1.5, 0) scan_and_store,
> 5 decode(bitand(service_cds, 7), 7, 1.7, 0)
> scan_and_store_w_prt
> 6 from
> 7 (select subscr_type, invoice, wm_concat(service) services,
> sum(service_cd) service_cds
> 8 from subscription
> 9 group by subscr_type,invoice);
>
> SUBSCR_TYPE INVOICE SERVICES
> PHOTOCOPY PRINTING SCAN_AND_STORE SCAN_AND_STORE_W_PRT
> --------------- ---------- ----------------------------------------
> ---------- ---------- -------------- --------------------
> NEW 345
> Photocopying,Printing 1.5
> 1.7 0 0
> RENEWAL 123 Photocopying,Printing,Scan &
> Store 1.5 1.7 0 1.7
> ADDITIONAL 234 Photocopying,Scan &
> Store 1.5 0
> 1.5 0
>
> ---------- ---------- -------------- --------------------
> sum
> 4.5 3.4 1.5 1.7
>
> SQL>
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -
The 'formatting' killed my nice, pretty output. My apologies, I hope
you can understand what I did.
David Fitzjarrell
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
