Help on simple SQL statement
in [Programming SQL]
|
Prev: SQL query help
Next: connection status "suspended"
From: CAM on 11 May 2010 20:10 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 11 May 2010 21:52 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 12 May 2010 00:35 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 12 May 2010 10:52 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 12 May 2010 20:07
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 |