From: CAM on
Hello,

I am having a problem with my SQL statements and I need a pro on this one.
I have two tables 1. SalesHeader 2. SalesDetail.
I join these two table together (code below) Here is my problem when I have
the same invoice number with multiple LineNo I want to display only the
AmountShipped field not the AmountOrder field in this example InvNo would be
55555 and the amount would be 69.00. Now if the InvNo has only 1 LineNo
like InvNo 77777 I want to display 85.00. How do I do this. It seems I
have to do some sort of count of LineNo group by InvNo. I just don't know
how to do this. Any tips will be appreciated. Thank you in advance.



Select InvNo, LineNo, AmountOrder, AmountShipped
From SalesHeader,SalesDetail
Where SalesHeader.InvNo = SalesDetail.InvNum



Fields
InvNo LineNo AmountOrder AmountShipped

55555 1 35.00 69.00
55555 2 35.00 75.00

77777 1 85.00 56.00
88888 1 35.99 77,00

From: Plamen Ratchev on
I am not sure I understand, but you can use a CASE expression with
COUNT OVER:

SELECT CASE WHEN COUNT(*) OVER(PARTITION BY InvNo) > 1 THEN 69.0 ELSE
85.0 END...

--
Plamen Ratchev
http://www.SQLStudio.com
From: CAM on
Plamen,

Thanks for your help, When an same invoice number (InvNo) has more then 1
lineNo (1,2,3, etc.) then I want to see the amount from the AmountShipped
field for example:

InvNo LineNo AmountShipped

55555 1 69.00
55555 2 75.00

When there is an invoice number (InvNo) like 77777 that only has one LineNo
(1) then I want to see the amount from the AmountOrder field, which will be
85.00. Another example for InvNo 88888 I want to see the amount from the
AmountOrder.




InvNo LineNo AmountOrder AmountShipped

55555 1 35.00 69.00
55555 2 35.00 75.00

77777 1 85.00 56.00
88888 1 35.99 77.00



What you suggested will this work. Thanks again.

Cheers,


"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:mb2ku5trh8hsd1gjbqv1blbqr7e9umm0ns(a)4ax.com...
>I am not sure I understand, but you can use a CASE expression with
> COUNT OVER:
>
> SELECT CASE WHEN COUNT(*) OVER(PARTITION BY InvNo) > 1 THEN 69.0 ELSE
> 85.0 END...
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com

From: Plamen Ratchev on
You simply change the CASE expression to reflect the columns you want:

SELECT CASE WHEN COUNT(*) OVER(PARTITION BY InvNo) > 1
THEN AmountShipped
ELSE AmountOrder END...

--
Plamen Ratchev
http://www.SQLStudio.com
From: CAM on
Thanks Plamen. I really appreciate your help.

Cheers,

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:f3glu55tgabhvma2h728bc5eqg17ui8kqp(a)4ax.com...
> You simply change the CASE expression to reflect the columns you want:
>
> SELECT CASE WHEN COUNT(*) OVER(PARTITION BY InvNo) > 1
> THEN AmountShipped
> ELSE AmountOrder END...
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com

 |  Next  |  Last
Pages: 1 2
Prev: SQL query help
Next: connection status "suspended"