Help to improve a query, w/ sample data!!!!

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

Transact-SQL (2000)

Help to improve a query, w/ sample data!!!!

Author

Topic

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-11-06 : 12:56:02

This seems too BIG AND UGLY



CREATE TABLE atable (so varchar(6), jo varchar(10) PRIMARY KEY)

INSERT atable VALUES ('C2255', '00834-0000')
INSERT atable VALUES ('C2255', '00834-0001')
INSERT atable VALUES ('C2255', '00834-0002')
INSERT atable VALUES ('C2255', '00834-0003')
INSERT atable VALUES ('C2255', '00834-0004')
INSERT atable VALUES ('C2255', '00834-0005')
INSERT atable VALUES ('C2255', '00834-0006')
INSERT atable VALUES ('C2255', '00834-0007')
INSERT atable VALUES ('C2255', '00834-0008')
INSERT atable VALUES ('C2255', '00834-0009')
INSERT atable VALUES ('C2255', '00834-0010')
INSERT atable VALUES ('C2255', '00834-0011')
INSERT atable VALUES ('C2255', '00834-0012')
INSERT atable VALUES ('C2255', '01785-0000')
INSERT atable VALUES ('C2255', '01813-0000')
INSERT atable VALUES ('C2255', '01813-0002')
INSERT atable VALUES ('C2255', '01662-0000')
INSERT atable VALUES ('C2256', '00918-0000')
INSERT atable VALUES ('C2257', '00833-0000')
INSERT atable VALUES ('C2257', '00833-0001')
INSERT atable VALUES ('C2257', '00833-0002')
INSERT atable VALUES ('C2258', '00919-0000')
INSERT atable VALUES ('C2259', '00920-0000')
INSERT atable VALUES ('C2259', '00920-0001')
INSERT atable VALUES ('C2259', '00920-0002')
INSERT atable VALUES ('C2259', '00920-0003')
INSERT atable VALUES ('C2259', '00920-0004')
INSERT atable VALUES ('C2259', '00920-0005')
INSERT atable VALUES ('C2259', '00920-0006')
INSERT atable VALUES ('C2259', '00920-0007')
INSERT atable VALUES ('C2259', '01280-0000')
INSERT atable VALUES ('C2259', '01845-0000')
INSERT atable VALUES ('C2259', '01938-0000')
INSERT atable VALUES ('C2259', '02182-0000')
INSERT atable VALUES ('C2259', '02328-0000')

--SELECT * FROM atable

SELECT DISTINCT RTRIM(Q.so) AS salesorder, Q.jo as joborder FROM atable AS Q
INNER JOIN (SELECT so, MIN(SUBSTRING(jo,1,5)) AS S FROM atable GROUP BY so) AS T
ON Q.so = T.so AND SUBSTRING(Q.jo,1,5) = T.S
WHERE (RIGHT(Q.jo,4)= '0000')

-- below are some additional requirements but just because so
-- dosen't stay very pure in atable on the production table
-- which isn't an issue in this demo
 
-- AND Q.so <> '' AND LEN(Q.jo) < 6


--DROP TABLE atable

The jist of it is for each group of so find the MIN first part of jo (the first jo created under that sales order) and the second part of jo that is '0000'
(ideally it would be MIN second part of jo because upon creation the jo with '-0000' may become unuseable then '-0001' would inherit it's role)

so is a salesoreder
jo is a joborder
-0000,-0001,-0002..... represent the sub jobs

RESULT

salesorder joborder   
---------- ---------- 
C2255      00834-0000
C2256      00918-0000
C2257      00833-0000
C2258      00919-0000
C2259      00920-0000

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-11-06 : 14:10:26

You're inserts have duplicate jo and thus violate the PK constraint. I removed the duplicates to test.

I got slightly better performance from...


select
    a.so,
    a.jo
from
    atable a
where
    not exists (
        select 1
        from
            atable b
        where
            a.so = b.so and
            (convert(int,right(a.jo,4)) > convert(int,right(b.jo,4)) or
            convert(int,substring(a.jo,1,5)) > convert(int,substring(b.jo,1,5))))

Jay White
{0}

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-06 : 14:45:37

Wow Jay... how did you manage to understand the subject?

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-06 : 16:16:14

Sitka!

That's the way aha aha!

Jay, I didn't have to remove any data and it came out 100%...except for the sorting...

What did you have to remove?

Brett

8-)

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-11-06 : 16:44:51

Thanks folks had to split for a little while, back now. checkin' it out.

Fixed jo duplicate (tough find 01662-0000 AND 01813-0002) in sample

Thanks Page47..It's a cool way to attack minimuns in gappy series.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources