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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Find items purchased from multiple suppliers
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

neellotus
Starting Member

India
14 Posts

Posted - 03/07/2013 :  05:29:56  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 03/07/2013 :  05:36:36  Show Profile  Reply with Quote

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


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

Go to Top of Page

neellotus
Starting Member

India
14 Posts

Posted - 03/07/2013 :  05:44:02  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 03/07/2013 :  06:13:31  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 03/07/2013 :  06:14:49  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000