March 7, 2013 at 4:02 am
Hi All,
I want to find item that purchased from more than one supplier, so i am running following query :-
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
but it is displaying only items if i run it with vendor code then it is displaying nothing in result.
Pls help me tell me how i can get both fields i will very thankful to you.
March 7, 2013 at 4:06 am
Can you please provide DDL and sample data as per the second link in my signature so that we can help you out better.
March 7, 2013 at 5:57 am
SELECT d.Item
FROM (
SELECT
a.Item,
Vendor_Code
FROM Purch_Inv_Line a
INNER JOIN Purch_Inv_Header a
ON a.[Document No_] = b.Item
AND b.[Posting Date] BETWEEN '2010-01-01' AND '2010-12-31'
GROUP BY a.Item, Vendor_Code
) d
GROUP BY d.Item
HAVING COUNT(*) > 1
SELECT
a.Item,
Vendor_Code,
vc = DENSE_RANK() OVER(PARTITION BY Item ORDER BY Vendor_Code)
FROM Purch_Inv_Line a
INNER JOIN Purch_Inv_Header a
ON a.[Document No_] = b.Item
AND b.[Posting Date] BETWEEN '2010-01-01' AND '2010-12-31'
Edit: replaced double-quote identifier delimiters with square brackets for readability.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 7, 2013 at 7:18 am
Which table contains Vendor_Code ?
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply