help writing query

Question

Post reply

help writing query

winston Smith

SSCoach

Points: 19500
More actions
September 18, 2009 at 5:43 am

#141892

I am trying to write a query and having some issues. The query must return any orderlineID's that have a status of InProcess but not delivered.
The problem is each OrderlineID can have multiple entries in the table, each with a different status.
Here is sample data:
StatusIDOrderLineIDDateStatus
11001-01-2009InProcess
21002-01-2009Delivered
41201-01-2009InProcess
51201-01-2009InProcess
61401-01-2009InProcess
71501-01-2009InProcess
81501-01-2009Delivered
EDIT: to help, here is some code to create the sample table with sample data. only OrderID 12 and 14 should be returned:
create table #test
(
statusid int,
orderlineid int,
date datetime,
status char(3)
)
insert into #test
values(1,10,'2009-09-18','PRO')
insert into #test
values(2,10,'2009-09-19','SHI')
insert into #test
values(3,12,'2009-09-18','PRO')
insert into #test
values(4,12,'2009-09-18','PRO')
insert into #test
values(5,14,'2009-09-18','PRO')
insert into #test
values(6,15,'2009-09-18','PRO')
insert into #test
values(7,15,'2009-09-18','SHI')
So i would need to return only info on Orderline 12 and 14 from this list. can anyone help me out with this? Note i want to do this using joins, and avoid recursive sub queries like:
select * from #test t1
where orderlineid not in
(
select orderlineid from #test t2
where status = 'SHI'
)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi Winston

You can use the HAVING clause and a SUM depending on your status:

SELECT

orderlineid

FROM #test

GROUP BY orderlineid

HAVING

SUM(CASE WHEN Status = 'PRO' THEN 1 ELSE 0 END) != 0

AND SUM(CASE WHEN Status = 'SHI' THEN 1 ELSE 0 END) = 0

Greets

Flo

Lowell SSC Guru Points: 323518 More actions · Answer 2

way to go for posting the test data!

the trick is to join the table against a copy of itself.

the join condition is the orderlineid's must equal, but the statusID for items's processed must be less than the same order id's SHIPPEd.

if you needed the third status, DELEIVEREd, you'd join a third time, joining SHIPPED to a copy aliased as DELIVERED, the same way

you can see from the results below, items with statusid=null have not shipped yet:

statusid    orderlineid date                    status statusid    orderlineid date                    status
----------- ----------- ----------------------- ------ ----------- ----------- ----------------------- ------
1           10          2009-09-18 00:00:00.000 PRO    2           10          2009-09-19 00:00:00.000 SHI
2           10          2009-09-19 00:00:00.000 SHI    NULL        NULL        NULL                    NULL
3           12          2009-09-18 00:00:00.000 PRO    4           12          2009-09-18 00:00:00.000 PRO
4           12          2009-09-18 00:00:00.000 PRO    NULL        NULL        NULL                    NULL
5           14          2009-09-18 00:00:00.000 PRO    NULL        NULL        NULL                    NULL
6           15          2009-09-18 00:00:00.000 PRO    7           15          2009-09-18 00:00:00.000 SHI
7           15          2009-09-18 00:00:00.000 SHI    NULL        NULL        NULL                    NULL

select * from #test PROCESSING

left outer join #test SHIPPED

ON PROCESSING.orderlineid=SHIPPED.orderlineid

AND PROCESSING.statusid < SHIPPED.statusid

/*

left outer join #test DELIVERED

ON SHIPPED.orderlineid=DELIVERED.orderlineid

AND SHIPPED.statusid < DELIVERED.statusid

*/

--WHERE status ='PRO'

--AND SHIPPED.statusid is null --has not shipped

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

This should work for you, working with joins as you requested.

Consider using Florian's solution too, depending on the execution plan.

SELECT t1.*

FROM #test t1

LEFT JOIN #test t2

ON t1.orderlineid = t2.orderlineid

AND t2.status = 'SHI'

WHERE t2.orderlineid IS NULL

Hope this helps

Gianluca

-- Gianluca Sartori

winston Smith SSCoach Points: 19500 More actions · Answer 4

thanks folks! its educational to see all of the options i had in this case!

I love Florians solution, i would have never considered an idea like that.

I ended up using something similar to Gianluca Sartori's response.

Thanks once again!!!

drew.allen SSC Guru Points: 76985 More actions · Answer 5

You could also use a CTE.

WITH CTE AS (

SELECT

orderlineid

, date

, status

, Row_Number() OVER(Partition By orderlineid ORDER BY statusid desc ) AS RowNum

FROM #test

)

SELECT *

FROM CTE

WHERE status = 'PRO'

AND RowNum = 1

I'm not sure how it compares for performance.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA