how to make this into 1 sql? - 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

General SQL Server Forums

New to SQL Server Programming

how to make this into 1 sql?

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2014-03-28 : 12:59:00

I have to process in an sql as follows. each order is made up of many detail rows. I only need to look at one table, TRA99.

Order number TRAN CODE

123 QEE
123 @23
123 ABC

I want all the Order # which have 'QEE' or 'QNE'. These are QUote codes. We want a report that will tell us, which quotes orders' converted to a real order and which did not.

then if they have as well @23, this tells me that the order was converted or became an actual order. I am not sure how to do this in 1 sql query i was thinking to create a view for all QEE and QNE codes. then run a second query against that looking for @23.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-28 : 13:20:32

Not 100% sure I'm following, but you can make two derived tables and join them together as one option:

SELECT 
	*
FROM
(
	SELECT DISTINCT OrderNumber
	FROM TRA99
	WHERE TranCode IN ('QNE', 'QNE')
) AS A
INNER JOIN
(
	SELECT DISTINCT OrderNumber
	FROM TRA99
	WHERE TranCode LIKE '@%'
) AS B
ON A.OrderNumber = B.OrderNumber

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2014-03-28 : 13:35:37

derived table, i was thinking along these lines, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources