September 14, 2012 at 11:00 am
I have 2 queries and I need to add them together to get 1 row of data for each invoice_no, order_no combination.
First query is
select invoice_line.order_no, invoice_no,
sum(invoice_line.extended_price) as 'Extended Price',
sum(invoice_line.commission_cost) as comm_cost_invline,
sum(oe_line.commission_cost/ oe_line.unit_size) as comm_cost_oeLine
from invoice_line
join oe_line on oe_line.order_no = invoice_line.order_no
and oe_line.line_no = invoice_line.oe_line_number
where invoice_no = '3101279' --used to check 1 invoice no
group by invoice_no, invoice_line.order_no
order by invoice_line.order_no
It returns
order_no invoice_no Extended Price comm_cost_invline comm_cost_oeLine
1100056 3101279 63.4900 48.295821389 38.8900000
Second query is:
select invoice_hdr.invoice_no, invoice_hdr.order_no, freight_out,invoice_hdr_salesrep.salesrep_id from invoice_hdr --and others
join invoice_hdr_salesrep on invoice_hdr_salesrep.invoice_number = invoice_hdr.invoice_no
join freight_code on freight_code.freight_code_uid = invoice_hdr.freight_code_uid
join oe_pick_ticket on oe_pick_ticket.order_no = invoice_hdr.order_no
and oe_pick_ticket.invoice_no = invoice_hdr.invoice_no
and freight_out > 0.00
where freight_cd = 'PREPAY-CEP'
and invoice_hdr.date_paid between '2012-01-01' and '2012-09-30'
and invoice_hdr_salesrep.salesrep_id = 1023
and invoice_hdr.invoice_no = '3101279'
order by invoice_hdr.invoice_no
It returns
invoice_no order_no freight_out salesrep_id
3101279 1100056 10.8400 1023
What I need is:
invoice_no order_no freight_out salesrep_id Extended Price comm_cost_invline comm_cost_oeLine
3101279 1100056 10.8400 1023 63.4900 48.295821389 38.8900000
For the life of me I can't figure this out. Can anybody help?
Thanks a lot, I really appreciate it.
September 14, 2012 at 11:13 am
Have you tried joining the INVOICE_LINE into your second query and add the sum(..) tot he select?
September 14, 2012 at 2:51 pm
mmm, yes that seemed to work. letting user test it now. thank you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply