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/

Go to Top of Page

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...
Go to Top of Page

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/

Go to Top of Page

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/

Go to Top of Page
   

- Advertisement -