SQL ambiguous lines query - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

SQL ambiguous lines query

Author

Topic

AM Diamond
Starting Member

1 Post

Posted - 2014-02-21 : 10:09:59

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

700 Posts

Posted - 2014-02-21 : 10:37:58

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;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources