Duplicate Values

Clif
Clif used Ask the Experts™
on
I have two tables similar to this:
-Invoice_Table-
Customer  Job_No  Sale_Amt
12345678  123456  123.45
87654321  246800  645.23

-Vendor_Table-
Customer  Job_No  Vendor  Vendor_Cost
12345678  123456  ABCDEF  10.50
12345678  123456  UVWXYZ  20.92
87654321  246800  LMNOPQ  12.45

Open in new window

And I have a query similar to the following (but a LOT more complex):
SELECT
	Inv.Customer,
	Inv.Job_No,
	Ven.Vendor,
	Ven.Cendor_Cost,
	Inv.Sale_Amt
FROM
	Invoice_Table Inv
	LEFT OUTER JOIN Vendor_Table Ven
		ON Inv.Customer = Ven.Customer
		AND Inv.Job_No = Ven.Job_No

Open in new window

This query produces output similar to the following:
Customer  Job_No  Vendor  Vendor_Cost  Sale_Amt
12345678  123456  ABCDEF  10.50        123.45
12345678  123456  UVWXYZ  20.92        123.45
87654321  246800  LMNOPQ  12.45        645.23

Open in new window


This is causing a problem in Crystal (where I use this) as it's adding 123.45 twice.

Is there any way that I can, using the tables provided (that I can't modify) produce results something like this:
Customer  Job_No  Vendor  Vendor_Cost  Sale_Amt
12345678  123456  ABCDEF  10.50        123.45
12345678  123456  UVWXYZ  20.92        0 (or NULL)
87654321  246800  LMNOPQ  12.45        645.23

Open in new window


TIA
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chief Technology Officer
Most Valuable Expert 2011
Commented:
Hi.

Depending on your version of MS SQL, you can try an approach ROW_NUMBER() analytical function; however, the bigger question is why is it duplicated in the first place and what are you doing on the Crystal side with the data.  If you are taking an aggregate and trying compare total vendor cost to Sale_Amt for margin, then it may make more sense to remove Vendor from your original query and group by Customer, Job_No, Sale_Amt and then SUM(Vendor_Cost).  If Sale_Amt is top level invoice amount and there could be many line items (hence duplicates), then it may even be better to find the invoice details table (and column that uniquely grabs right vendor cost record) as margin type reports would probably want to see this by line.  

Again, just depends on what you are doing/needing from a business perspective.

Please clarify and provide version of SQL and will expand on my suggestion if you still want to pursue making secondary lines $0 versus removing duplicates.

Kevin

Author

Commented:
First off, I'm using SQL Server 2008 (and Crystal XI, if that matters)

The report is supposed to show the lines like the sample data above.  Basically the Customer, Job_No, Vendor and Vendor_Cost.  Although it's prefered to have the Sales_Amt as part of the line, it's confusing in this particular context.  The Sales_Amt is, ultimately, being summed as a grand total.  In Crystal I can "hide" the duplicate Sales_Amt.  However they do still exist and are added into the grand totals.

When the report was originally created, we only used one Vendor per job.  In that case, everything was hunky-dory.  However, this month it was decided to parcel out work to multiple vendors, which created the problem I have today.
here is the Row_number() approach that Kevin suggested
select 
	Customer,
	Job_No,
	Vendor,
	Cendor_Cost,
	case when rn = 1 then Sale_Amt else 0 end as Sale_Amt

from (
	SELECT
		Inv.Customer,
		Inv.Job_No,
		Ven.Vendor,
		Ven.Cendor_Cost,
		Inv.Sale_Amt
		row_number() over (partition by Inv.Customer, Inv.Job_No order by Inv.Customer) rn
	FROM
		Invoice_Table Inv
		LEFT OUTER JOIN Vendor_Table Ven
			ON Inv.Customer = Ven.Customer
			AND Inv.Job_No = Ven.Job_No
) t1

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)

Commented:
In crystal report set Supress IF duplicate to TRue.

Author

Commented:
PatelAlpesh,
Setting a field (value) to "Supress If Duplicated" doesn't remove the value from cummary calculations.  All it does is hide the field from the display/print out.

Author

Commented:
Sorry, that should have read "Setting a field (value) to "Supress If Duplicated" doesn't remove the value from summary calculations."
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Did you ever try http:#a35757303 < That is exactly what I meant as it uses row numbering to establish a row 1 and thus can use that in case statement to display sales amount on that row and 0 on all others for the same customer and job combination.

Author

Commented:
Yes, and it works perfectly.

I did have to test it this morning, but had a couple of unrelated issues that kept interrupting me.

Grade/points coming forthwith.

Author

Commented:
Works perfectly.

Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial