Avatar of Clif
ClifFlag for United States of America

asked on 

Duplicate Values

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
Microsoft SQL Server

Avatar of undefined
Last Comment
Clif
ASKER CERTIFIED SOLUTION
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Clif
Clif
Flag of United States of America image

ASKER

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.
SOLUTION
Avatar of ralmada
ralmada
Flag of Canada image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of Alpesh Patel
Alpesh Patel
Flag of India image

In crystal report set Supress IF duplicate to TRue.
Avatar of Clif
Clif
Flag of United States of America image

ASKER

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.
Avatar of Clif
Clif
Flag of United States of America image

ASKER

Sorry, that should have read "Setting a field (value) to "Supress If Duplicated" doesn't remove the value from summary calculations."
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

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.
Avatar of Clif
Clif
Flag of United States of America image

ASKER

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.
Avatar of Clif
Clif
Flag of United States of America image

ASKER

Works perfectly.

Thanks.
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo