Query Assistance

Question

Query Assistance

cwatson 81945

SSC Veteran

Points: 267
More actions
January 3, 2017 at 3:08 pm

#401314

Hello all, It's been awhile since I've been on and I have something I can't wrap my head around. I've received help on something similar in the past so I know this can be done! I can do it with a cursor but am trying to avoid it if possible.
Scenario:
I have several job/tasks that can have multiple routes. The routes have different statuses. I want a list of jobs that
do not have a JPRE or JPOS route, or if they do, status must be COMP or RJCT
AND
a JFB route status INIT or ASGN.
Possible routesJFB, JPRE, JPOS
Route statusesINIT, ASGN, COMP, RJCT
--Create temp table
create table #JobRoutes
( jobnbr varchar(8),
tasknbr varchar(3),
route_type varchar(4),
route_status varchar(4)
)
--Load table
insert into #JobRoutes(jobnbr, tasknbr, route_type, route_status)
values ('15512890','500','JFB','ASGN'),
('15512890','500','JFB','ASGN'),
('15512890','500','JFB','ASGN'),
('15004362','295','JFB','ASGN'),
('15003736','510','JFB','ASGN'),
('15003736','510','JPRE','COMP'),
('14512241','500','JPOS','INIT'),
('14512241','500','JPOS','ASGN'),
('14512241','500','JPRE','COMP'),
('14512241','500','JFB','INIT'),
('08400124','510','JPRE','COMP'),
('08400124','510','JPOS','COMP'),
('14508049','500','JPOS','COMP'),
('14508049','500','JPOS','ASGN'),
('14508049','500','JFB','ASGN'),
('14508049','500','JPRE','COMP'),
('14508049','500','JPOS','COMP'),
('14508049','500','JPRE','COMP'),
('12505660','500','JPRE','COMP'),
('12505660','500','JPOS','ASGN'),
('12505660','500','JFB','ASGN'),
('12505660','500','JPRE','COMP'),
('12505660','500','JPRE','COMP'),
('12505660','500','JFB','ASGN'),
('12505660','500','JPRE','COMP'),
('09007856','520','JPOS','COMP'),
('09007856','520','JPRE','COMP'),
('09007856','520','JPRE','COMP'),
('09007856','520','JFB','INIT'),
('09007856','520','JPRE','COMP')
Desired Result:
jobnbrtasknbr
15004362295
15003736510
09007856520
15507086500
TIA for any help/suggestions.
Cosandra

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

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

x SSC-Insane Points: 23660 More actions · Answer 1

According to your description, you should see '15512890','500' in your results but you don't list this one in your desired results. Is your description omitting some info?

Lowell SSC Guru Points: 323518 More actions · Answer 2

here's my first pass, does this do what you are asking?

SELECT distinct jobnbr,tasknbr

FROM (

--do not have a JPRE or JPOS route,

SELECT * FROM #JobRoutes WHERE jobnbr NOT IN(SELECT jobnbr FROM #JobRoutes WHERE route_type In('JPRE','JPOS') )

--or if they do, status must be COMP or RJCT

--AND a JFB route status INIT or ASGN.

UNION

SELECT * FROM #JobRoutes WHERE jobnbr IN(SELECT jobnbr FROM #JobRoutes WHERE route_type In('JPRE','JPOS') AND route_status IN('COMP','RJCT') )

) MyAlias

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!

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

Your expected results don't match your sample data. Specifically, 08400124 and 15512890 should be included and there is no record with 15507086, so that cannot appear in the results.

Here is my take.

SELECT jr.jobnbr, jr.tasknbr

FROM #JobRoutes jr

GROUP BY jr.jobnbr, jr.tasknbr

HAVING MAX(CASE WHEN jr.route_type = 'JFB' THEN NULL WHEN jr.route_status IN ( 'COMP', 'RJCT' ) THEN NULL ELSE route_status END) IS NULL

ORDER BY jr.jobnbr

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

cwatson 81945 SSC Veteran Points: 267 More actions · Answer 4

Yep, you're right. I missed it in the desired results.

cwatson 81945 SSC Veteran Points: 267 More actions · Answer 5

Thanks SSCrazy!!! That gives me what I needed. Sorry for not having the data correct. Can't thank you enough!!