Find items purchased from multiple suppliers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Find items purchased from multiple suppliers

Author

Topic

neellotus
Starting Member

15 Posts

Posted - 2013-03-07 : 05:29:56

Hi All,

I want to find item that purchased from more than one supplier, so i am running following query but it is displaying wrong data because it is also showing article that purchased only from single vendor.

SELECT B.Vendor_Code,A.Item
FROM Purch_Inv_Line A, Purch_Inv_Header B
WHERE A."Document No_"=B.Item and B."Posting Date" BETWEEN '2010-01-01' AND '2010-12-31'
GROUP BY B.Vendor_Code,A.Item
having count(*)>1

Pls tell me where i am doing wrong i will be very thankful to you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-07 : 05:36:36

[code]
SELECT A.Item
FROM Purch_Inv_Line A, Purch_Inv_Header B
WHERE A."Document No_"=B.Item and B."Posting Date" BETWEEN '2010-01-01' AND '2010-12-31'
GROUP BY A.Item
having count(distinct Vendor_Code)>1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

neellotus
Starting Member

15 Posts

Posted - 2013-03-07 : 05:44:02

Visakh,

Lot of thanx for your quick reply and this query showing only items but i need vendor code with items in result.

Thanx...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-07 : 06:13:31

you should have specified this earlier.


SELECT Vendor_Code,Item
FROM
(
SELECT B.Vendor_Code,A.Item,COUNT(B.Vendor_Code) OVER (PARTITION BY A.Item) AS Cnt
FROM Purch_Inv_Line A, Purch_Inv_Header B 
WHERE A."Document No_"=B.Item and B."Posting Date" BETWEEN '2010-01-01' AND '2010-12-31'
)t
WHERE Cnt =1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-07 : 06:14:49

Also if Posting Date has time also stored then you'll miss out all records that got created on 2010-12-31 after start of the day (12 midnight)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources