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 2008 Forums
 Transact-SQL (2008)
 SQL ambiguous lines query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AM Diamond
Starting Member

United Kingdom
1 Posts

Posted - 02/21/2014 :  10:09:59  Show Profile  Reply with Quote
Folks,

I am a true beginner with SQL and have a major problem. I have a extract from our SQL script that appears to be indicating where a Purchase Order has more than one product code = Ambiguous. However, what I NEED the script to do is check every purchase order and indicate how many product codes have been used per purchase order.
The script =
PO AS
(
SELECT
DISTINCT
p.order_id,
p.article
FROM
apodetail p
WHERE
p.client ='DF'
AND
article = (SELECT MAX(article) FROM apodetail WHERE client = 'DF' AND order_id = p.order_id)
),

UNSPC AS
(
SELECT
*
FROM
acrtrees
WHERE
client ='DF'
AND
att_agrid = '58'
),
Dup AS
(
SELECT
order_id, 'Yes' AS Status
FROM
(
SELECT
order_id, COUNT(DISTINCT article) AS ART -- DISTINCT ORDERS 53494, 18587 have more than one p code. 1041 have more than one different p code.
FROM
apodetail
WHERE
client = 'DF'
GROUP BY
order_id
)Z
WHERE ART>1
)
...............................

I don't want the SQL to pick the purchase orders randomly, rather show me all PO's with more than one Product code !!

Can anyone help ??
I'm pretty dispondent with my efforts thus far :-(

Please advise ......... Many thanks in advance :-)


A Diamond

Ifor
Aged Yak Warrior

615 Posts

Posted - 02/21/2014 :  10:37:58  Show Profile  Reply with Quote
I am not quite sure what you want but the following should get you started:

WITH MultipleProducts
AS
(
		SELECT order_id
		FROM apodetail
		WHERE client = 'DF'
		GROUP BY order_id
		HAVING COUNT(DISTINCT article) > 1
)
SELECT DISTINCT D.order_id, D.article
FROM apodetail D
	JOIN MultipleProducts M
		ON D.order_id = M.order_id
WHERE client = 'DF'
ORDER BY D.order_id, D.article;
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.28 seconds. Powered By: Snitz Forums 2000