Help on generating a report table having nulls

Question

Help on generating a report table having nulls

jscontreras

SSC-Addicted

Points: 430
More actions
January 23, 2003 at 8:46 am

#79444

Hi Everyone,
I trying to figure how I can do the following:
I have two tables one invoice table and one po table. I'm doing a sum on invoice amount and po amount from there respective table. However the joins are weird based on the design of the database. I don't have the time to normalized. I have to work with what I have.
The results I'm getting are I have unique PO Amounts, but the Invoice Amounts are SUM, but get duplicated.
For example:
PO AMOUNT INVOICE AMOUNT The join should have been on invoiceID, but its on POID intstead.
20 300
40 300
20 300
50 300
This is basically telling me there are four pos against one invoice
What I'd like to see is
PO Amount INVOICE AMOUNT
20 300
40 NULL
20 NULL
50 NULL
When I go to write the report it will only see one instance of 300 and can do a proper summation of all invoices in the report.
I would greatly appreciate any ideas or assistance.
Thanks
JMC

JMC
January 27, 2003 at 8:00 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

lparks2001 SSC Veteran Points: 278 More actions · Answer 1

You may want to read this article. I don't know if this will help but its worth reading. I'm not an expert i'm just learning myself so here you go.

http://www.sqlservercentral.com/columnists/jtravis/understandingthedifferencebetweenisnull.asp

dj_meier SSCrazy Points: 2732 More actions · Answer 2

Without seeing the query youhave, I'm not sure if I can help, but maybe a CASE statement will do the trick, something like this:

 

SELECT 

  [PO AMOUNT],

  [INVOICE AMOUNT] = 

    CASE

      WHEN [POID]=(SELECT MIN([POID]) FROM Invoices ii WHERE ii.POID = i.POID) 

        THEN [INVOICE AMOUNT]

      ELSE NULL 

    END

  FROM Invoices i INNER JOIN PO p ON i.POID = p.POID

I would really need to see some code to understand what you are doing.

-Dan

jscontreras SSC-Addicted Points: 430 More actions · Answer 3

Hi Everyone,

I went back to the client and adjust business rules. Now the data contains the right relationships and the way the data was being stored is no longer applicable.

Thanks for all your help

JMC