Hi all,
I have 3 tables:
Order Header (oh) order header info (inv_no customer, address, date, etc)
Order Line (ol) order line detail (items ordered, qty, price, etc)
Credits (c) any available credits against the order

Credits link to order header by inv_no.
How can I avoid returning the credits multiple times (once per line item)
Not all orders have a credit. I need to return all orders and their line
items and show if a credits exists

If an order has 5 items, this will show the credit 5 times
select oh.inv_no, ol.item_no, ol.qty, c.CreditAmt

from OrderHeader oh

inner join OrderLine ol on oh.inv_no = ol.inv_no

left outer join Credits c on oh.inv_no = c.inv_no



Any ideas and pointers are much appreciated

Thanks

Artie wrote:

> Hi all,
> I have 3 tables:
> Order Header (oh) order header info (inv_no customer, address, date, etc)
> Order Line (ol) order line detail (items ordered, qty, price, etc)
> Credits (c) any available credits against the order
>
> Credits link to order header by inv_no.
> How can I avoid returning the credits multiple times (once per line item)
> Not all orders have a credit. I need to return all orders and their line
> items and show if a credits exists
>
> If an order has 5 items, this will show the credit 5 times
> select oh.inv_no, ol.item_no, ol.qty, c.CreditAmt
>
> from OrderHeader oh
>
> inner join OrderLine ol on oh.inv_no = ol.inv_no
>
> left outer join Credits c on oh.inv_no = c.inv_no

create view CreditTotals as
select inv_no, sum(CreditAmt) TotalCreditAmt
from Credits
group by inv_no

select oh.inv_no, ol.item_no, ol.qty, tc.TotalCreditAmt
from OrderHeader oh
inner join OrderLine ol on oh.inv_no = ol.inv_no
left outer join CreditTotals tc on oh.inv_no = tc.inv_no

Ed, thanks for the . Sorry, I was not clear enough.
The credits table only has 1 row per inv_no so your view would produce the
same results I already have.

Example:
Credits.Inv_no = 54321
Credits.CreditAmt = 100.00

Order 54321 has 5 line items
my original script would show:
inv_no item_no CreditAmt
54321 abc123 100.00
54321 abc456 100.00
54321 abc789 100.00
54321 cde123 100.00
54321 cde456 100.00

If I total this, I get a CreditAmt of 500.00




"Ed Murphy" <emurphy42@socal.rr.com> wrote in message
news:gffdna$2nd$1@news.eternal-september.org...
> Artie wrote:
>
>> Hi all,
>> I have 3 tables:
>> Order Header (oh) order header info (inv_no customer, address, date,
>> etc)
>> Order Line (ol) order line detail (items ordered, qty, price, etc)
>> Credits (c) any available credits against the order
>>
>> Credits link to order header by inv_no.
>> How can I avoid returning the credits multiple times (once per line item)
>> Not all orders have a credit. I need to return all orders and their line
>> items and show if a credits exists
>>
>> If an order has 5 items, this will show the credit 5 times
>> select oh.inv_no, ol.item_no, ol.qty, c.CreditAmt
>>
>> from OrderHeader oh
>>
>> inner join OrderLine ol on oh.inv_no = ol.inv_no
>>
>> left outer join Credits c on oh.inv_no = c.inv_no
>
> create view CreditTotals as
> select inv_no, sum(CreditAmt) TotalCreditAmt
> from Credits
> group by inv_no
>
> select oh.inv_no, ol.item_no, ol.qty, tc.TotalCreditAmt
> from OrderHeader oh
> inner join OrderLine ol on oh.inv_no = ol.inv_no
> left outer join CreditTotals tc on oh.inv_no = tc.inv_no

The main question here is how do you want to see the credit?

Here are two queries, the first one will distribute the credit to all
line items, and the second will show it only on the last item (assuming
item_no is something you can order by, also, you can easily change that
to show on the first item, just remove DESC in the ORDER BY clause of
OVER). Both methods require SQL Server 2005/2008.

SELECT oh.inv_no,
ol.item_no,
ol.qty,
c.CreditAmt /
COUNT(*) OVER(PARTITION BY oh.inv_no) AS credit_amt
FROM OrderHeader AS oh
INNER JOIN OrderLine AS ol
ON oh.inv_no = ol.inv_no
LEFT OUTER JOIN Credits AS c
ON oh.inv_no = c.inv_no;

SELECT oh.inv_no,
ol.item_no,
ol.qty,
CASE
WHEN
ROW_NUMBER() OVER(PARTITION BY oh.inv_no
ORDER BY ol.item_no DESC) = 1
THEN c.CreditAmt
ELSE 0
END credit_amt
FROM OrderHeader AS oh
INNER JOIN OrderLine AS ol
ON oh.inv_no = ol.inv_no
LEFT OUTER JOIN Credits AS c
ON oh.inv_no = c.inv_no;

--
Plamen Ratchev
http://www.SQLStudio.com

"A problem well stated is a problem half solved." -- Charles F.
Kettering

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.

Sample data is also a good idea, along with clear specifications. It
is very hard to debug code when you do not let us see it. If you want
to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html