Can this be done without temp tables

Question

Can this be done without temp tables

cwatson 81945

SSC Veteran

Points: 267
More actions
August 19, 2015 at 12:43 pm

#303639

From data below I need to return unique jobnbr and tasknbr where
Item_type = task
or
Item_type = hold and item_id <> MATL
If a jobnbr/tasknbr has 2 records and one is item_type = task and the other is item_type = hold and item_id = MATL, it should not be returned
I didn't bother with index here but the real table has index of week_id, jobnbr, tasknbr, item_type, item_id. Plus 45 columns and over 11 million records. Approximately 20,000 records are added to table weekly. I can do this with temporary tables but I feel like there's a simpler way; I just can't think it through.
declare @data table (week_id int, jobnbr varchar(8), tasknbr varchar(3), item_type varchar(4), item_id varchar(4))
insert into @data
select 1589,'10008509','060','TASK','TASK' UNION ALL
select 1589,'10502718','500','TASK','TASK' UNION ALL
select 1589,'11002168','255','TASK','TASK' UNION ALL
select 1589,'11002168','255','HOLD','NES' UNION ALL
select 1589,'11007701','500','TASK','TASK' UNION ALL
select 1589,'12507342','900','TASK','TASK' UNION ALL
select 1589,'13000188','500','TASK','TASK' UNION ALL
select 1589,'13000188','500','HOLD','MATL' UNION ALL
select 1589,'13000188','500','HOLD','NEF' UNION ALL
select 1589,'13005113','250','HOLD','NES' UNION ALL
select 1589,'13005113','250','TASK','TASK' UNION ALL
select 1589,'13005114','550','TASK','TASK' UNION ALL
select 1589,'13005114','550','HOLD','NES' UNION ALL
select 1589,'13510859','525','HOLD','MATL' UNION ALL
select 1589,'13510859','525','TASK','TASK' UNION ALL
select 1589,'13512997','525','TASK','TASK' UNION ALL
select 1589,'14506430','500','TASK','TASK' UNION ALL
select 1589,'14506783','100','TASK','TASK' UNION ALL
select 1589,'14507065','500','TASK','TASK'
Result:
Week_idJobnbrtasknbr
158910008509060
158910502718500
158911002168255
158911007701500
158912507342900
158913000188500
158913005113250
158913005114550
158913512997525
158914506430500
158914506783100
158914507065500
TIA for any help/suggestions!!
cmw

Viewing 13 posts - 1 through 12 (of 12 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

since you only want tasks that are not on hold, i'd look to get the data via the EXCEPT operator,a dn join it with the other items on hold.

nice job providing the setup.

does this produce the results you expect?

--first critieria

SELECT OT.Week_id,OT.Jobnbr,OT.tasknbr FROM @data OT WHERE item_type = 'TASK' GROUP BY OT.Week_id,OT.Jobnbr,OT.tasknbr

EXCEPT

SELECT Week_id,Jobnbr,tasknbr FROM @data OH WHERE item_type = 'HOLD' AND item_id <> 'MATL' GROUP BY Week_id,Jobnbr,tasknbr

UNION ALL

--second critieria

SELECT Week_id,Jobnbr,tasknbr FROM @data OH WHERE item_type = 'HOLD' AND item_id <> 'MATL' GROUP BY Week_id,Jobnbr,tasknbr

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!

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

Thanks for the quick reply but No it's not the desired result. It has jobnbr/task 13510859/525 in the result which should not be there .

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

I think this is more efficient since it only requires reading the table once instead of three times.

SELECT d.week_id, d.jobnbr, d.tasknbr

FROM @data d

GROUP BY d.week_id, d.jobnbr, d.tasknbr

HAVING MIN(

CASE

WHEN d.item_type = 'TASK' THEN 1

WHEN d.item_type = 'HOLD' AND d.item_id <> 'MATL' THEN 1

WHEN d.item_type = 'HOLD' AND d.item_id = 'MATL' THEN 0

ELSE NULL -- there may be other types and items unaccounted for in your data, I'm excluding these.

END

) = 1

You include task 13000188 in your results even though it has an item type = HOLD and item_id = MATL.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

pietlinden SSC Guru Points: 63428 More actions · Answer 4

something like this?

SELECT jobnbr, tasknbr

FROM dataTable

EXCEPT

(SELECT d1.jobnbr, d1.tasknbr

FROM dataTable d1

WHERE d1.item_Type = 'TASK'

INTERSECT

SELECT d2.jobnbr, d2.tasknbr

FROM dataTable d2

WHERE d2.item_Type = 'HOLD');

(I may be missing the weekNbr, though...)

I have to admit, getting schooled here every day is really interesting!

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

Yes I included 13000188/500 because it should be there. It has a NEF hold along with the MATL hold. And because of the NEF hold, it needs to be in the list. Hence I thought if there are only two records and one type is task and the other is type hold and id of MATL, then that record could somehow be excluded.

drew.allen SSC Guru Points: 76944 More actions · Answer 6

Lowell (8/19/2015)
--first critieria SELECT OT.Week_id,OT.Jobnbr,OT.tasknbr FROM @data OT WHERE item_type = 'TASK' GROUP BY OT.Week_id,OT.Jobnbr,OT.tasknbr EXCEPT SELECT Week_id,Jobnbr,tasknbr FROM @data OH WHERE item_type = 'HOLD' AND item_id <> 'MATL' GROUP BY Week_id,Jobnbr,tasknbr UNION ALL --second critieria SELECT Week_id,Jobnbr,tasknbr FROM @data OH WHERE item_type = 'HOLD' AND item_id <> 'MATL' GROUP BY Week_id,Jobnbr,tasknbr

I think that this code is closer to what he wants. I removed the GROUP BY since EXCEPT automatically does a DISTINCT and there is no reason to also do a GROUP BY when using a DISTINCT or UNION.

SELECT OT.Week_id,OT.Jobnbr,OT.tasknbr FROM @data OT WHERE item_type IN ( 'TASK', 'HOLD')

EXCEPT

SELECT Week_id,Jobnbr,tasknbr FROM @data OH WHERE item_type = 'HOLD' AND item_id = 'MATL'

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 7

What about something like?

SELECT week_id, jobnbr, tasknbr FROM @data WHERE item_type = 'TASK' OR (item_type = 'HOLD' AND item_id != 'MATL')

GROUP BY week_id, jobnbr, tasknbr

HAVING SUM(CASE WHEN item_id = 'MATL' AND item_type = 'HOLD' THEN 1 ELSE 0 END) = 0

ORDER BY jobnbr

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

Closer but it's missing job/task 13000188/500. This job has a two holds and because it has another hold beside MATL, it needs to be in the list.

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 9

cwatson 81945 (8/19/2015)
Closer but it's missing job/task 13000188/500. This job has a two holds and because it has another hold beside MATL, it needs to be in the list.

ah okay, what about this?

SELECT week_id, jobnbr, tasknbr FROM #data WHERE item_type = 'TASK' OR (item_type = 'HOLD')

GROUP BY week_id, jobnbr, tasknbr

HAVING (SUM(CASE WHEN item_id = 'MATL' AND item_type = 'HOLD' THEN 1 ELSE 0 END) != COUNT(*) - 1 AND COUNT(*) > 1) OR (SUM(CASE WHEN item_id = 'MATL' AND item_type = 'HOLD' THEN 1 ELSE 0 END) = 0 AND COUNT(*) = 1)

ORDER BY jobnbr

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

Thanks ZZartin, that returned the correct results. Now I'll review it and make sure I understand what it's doing. Thanks again, I knew there had to be a way!!!!

sgmunson SSC Guru Points: 110639 More actions · Answer 11

cwatson 81945 (8/19/2015)
Thanks ZZartin, that returned the correct results. Now I'll review it and make sure I understand what it's doing. Thanks again, I knew there had to be a way!!!!

Here's an alternative, but it relies on MATL being the minimum possible value for the type of HOLD, and also relies on HOLD being the minimum possible value for the item_type. If the actual data has other values that would interfere with those assumptions, then this won't work:

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

DECLARE @data AS TABLE (

week_id int,

jobnbr varchar(8),

tasknbr varchar(3),

item_type varchar(4),

item_id varchar(4)

);

INSERT INTO @data

SELECT 1589,'10008509','060','TASK','TASK' UNION ALL

SELECT 1589,'10502718','500','TASK','TASK' UNION ALL

SELECT 1589,'11002168','255','TASK','TASK' UNION ALL

SELECT 1589,'11002168','255','HOLD','NES' UNION ALL

SELECT 1589,'11007701','500','TASK','TASK' UNION ALL

SELECT 1589,'12507342','900','TASK','TASK' UNION ALL

SELECT 1589,'13000188','500','TASK','TASK' UNION ALL

SELECT 1589,'13000188','500','HOLD','MATL' UNION ALL

SELECT 1589,'13000188','500','HOLD','NEF' UNION ALL

SELECT 1589,'13005113','250','HOLD','NES' UNION ALL

SELECT 1589,'13005113','250','TASK','TASK' UNION ALL

SELECT 1589,'13005114','550','TASK','TASK' UNION ALL

SELECT 1589,'13005114','550','HOLD','NES' UNION ALL

SELECT 1589,'13510859','525','HOLD','MATL' UNION ALL

SELECT 1589,'13510859','525','TASK','TASK' UNION ALL

SELECT 1589,'13512997','525','TASK','TASK' UNION ALL

SELECT 1589,'14506430','500','TASK','TASK' UNION ALL

SELECT 1589,'14506783','100','TASK','TASK' UNION ALL

SELECT 1589,'14507065','500','TASK','TASK';

SELECT D.week_id, D.jobnbr, D.tasknbr

FROM @data AS D

GROUP BY D.week_id, D.jobnbr, D.tasknbr

HAVING

(MIN(D.item_type) = 'TASK' AND COUNT(*) = 1)

OR

(MIN(D.item_type) = 'HOLD' AND MIN(D.item_id) <> 'MATL' AND MAX(D.item_type) = 'TASK' AND COUNT(*) = 2)

OR

(MIN(D.item_type) = 'HOLD' AND MIN(D.item_id) = 'MATL' AND MAX(D.item_id) <> 'MATL' AND MAX(D.item_type) = 'TASK' AND COUNT(*) > 2)

SET STATISTICS IO OFF;

SET STATISTICS TIME OFF;

This does produce the exact desired result from the original post.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

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

Thanks for taking the time but MATL is not the min hold so this wouldn't work.