SQL Server Forums
Profile | Register | 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		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

neellotus
Starting Member

India
13 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
48076 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
13 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
48076 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
48076 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  
 New 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.05 seconds. Powered By: Snitz Forums 2000