vendor repeating more than one time

[Former Member]

Dear Experts

am looking to create a report in which i want to know from which and all Vendor we have purchased an item

i had create a SPT with a quey, am getting same vendor multi time, i think some issue with Query, pls can any one help me by editing the Query.

USE [SAP DEMO]
GO
/****** Object:  StoredProcedure [dbo].[PreferedVendor]    Script Date: 03/01/2017 10:37:57 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[PreferedVendor]		

AS
Begin






select distinct OPCH.CardCode [Supplier Code],OPCH.CardName [Supplier Name] ,PCH1.ItemCode,PCH1.Dscription,OCRD.CntctPrsn [Contact Person],OCRD.Phone1 [Ph1],OCRD.Phone2 [Ph2],OCRD.Cellular[Mob],OCRD.E_Mail [E-mail] from OPCH
inner join OCRD on OPCH.CardCode = OCRD.CardCode
iNNER JOIN PCH1 ON OPCH.DocEntry = PCH1.DocEntry
where PCH1.TargetType <> 19 and OPCH.CANCELED NOT IN ('N''C') AND OCRD. GroupCode <> 108
group by PCH1.ItemCode,OPCH.CardCode,OPCH.CardName,PCH1.ItemCode,PCH1.Dscription,OCRD.CntctPrsn,OCRD.Phone1,OCRD.Phone2 ,OCRD.Cellular,OCRD.E_Mail


End

[Former Member]

Hi johan

this is for the updating our Item master data with Preferred Vendor. we are having more that 75000 items, and each item are purchased from different vendors, so the current emp who deals with has resign, so when the substitute come , he should get aware that when an item is for purchasing which and an all Vendor he had to Approach, so we plan to update the item master data >> Preferred Vendor data, but as we had no of items more it not easy to find out , so we think if we get a report from the previous invoices in which the item is been bought from different vendors it would be easy to update the MD .

Hope u get the scenario

Regards

[Johan_H]

Hi,

Yes, I understand. Good that you managed to get it fixed.

Regards,

Johan

[Johan_H]

Hi,

As far as I can tell, your query simply returns a list of items and vendors that these items have been purchased from.

There is no logic in your query to limit the result set to single vendor-per-item combinations.

Also, what is this supposed to do?:

OPCH.CANCELED NOTIN('N''C')

Regards,

Johan

[Former Member]

yes, it says the items purchased from which vendor in previous time,

i need a report in such a way itself . can u pls tell how can i stop Vendor duplication happening in the report

Regards

[Johan_H]

Could you please explain in a little more detail what this query is supposed to do?

Should it return the most recent vendor of an item, or the most popular vendor of an item, or the cheapest vendor of an item, or simply a list items with their preferred vendors?

[Former Member]

Hi Johan

Its done, i made some small changes, it giving me perfect Data

Thanks for the support