Help needed to optimize query - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Help needed to optimize query

Author

Topic

ahmeterispaha
Starting Member

19 Posts

Posted - 2008-03-31 : 15:30:10

Can anyone suggest how to make this query better?

I need to get a list of parts that had an 'ADJ-QTY' transaction after a 'MFG-STK' transaction this year. Let's assume there are three fields in the transaction table -- partnum, trantype, and trandate. I'm getting the results I need using temp tables but I suspect there is a more efficient way to do this? Here's my query:

CREATE TABLE #ADJQTY (PartNum varchar(50), TranType varchar(7), TranDate datetime)
INSERT INTO #ADJQTY (PartNum, TranType, TranDate)

SELECT partnum,trantype,trandate
FROM parttran
WHERE (trandate > '2007-12-31') AND
(trantype = 'ADJ-QTY')

CREATE TABLE #MFGSTK (PartNum varchar(50), TranType varchar(7), TranDate datetime)
INSERT INTO #MFGSTK (PartNum, TranType, TranDate)

SELECT partnum,trantype,trandate
FROM parttran
WHERE (trandate > '2007-12-31') AND
(trantype = 'MFG-STK')

SELECT DISTINCT(a.PartNum) FROM #ADJQTY a
INNER JOIN #MFGSTK m on a.PartNum = m.PartNum
WHERE a.TranDate>=m.TranDate

DROP TABLE #MFGSTK
DROP TABLE #ADJQTY

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-31 : 15:43:50

This is untested, but I think you can do something like the following. but, you'd have to test it to see if it is faster or not.

SELECT DISTINCT
	A.PartNum
FROM
	PartTran AS A
INNER JOIN
	PartTran AS B
	ON A.PartNum = B.PartNum
	AND A.TranDate > '20071231'
	AND A.TranType = 'ADJ-QTY'
	AND B.TranDate > '20071231'
	AND B.TranType = 'MFG-STK'
	AND A.TranDate >= B.TranDate

EDIT: Forgot tran type

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-31 : 15:47:14

SELECT a.partnum
FROM
parttran a
INNER JOIN
(select partnum,[TranDate] = max(trandate)
from parttran
where trantype = 'MFG-STK' and trandate > '2007-12-31'
group by partnum) b
ON
a.partnum = b.partnum
WHERE
a.trantype = 'ADJ-QTY' and a.trandate >= b.trandate

Jim

ahmeterispaha
Starting Member

19 Posts

Posted - 2008-04-02 : 10:56:33

Thank you Lamprey and Jim for your responses.

Lamprey - your query returned my expected results and ran on average 44% faster than my query!

Jim - although your query ran 32% faster than mine, the results were also different.

Here are my expected results:
006416386P004
006517964P005
0165B7993P004
0181C1188P031
0181C1188P032
0181C1188P033
0181C1188P039
0181C1188P040
0254C0873P008
0265C0341G004
0265C0341P018
0285B0692P001
0366A0701704
0422A0775P001
0687C0960G009
0687C0960P014
0687C0960P015
0687C0960P016
10S10001
10S15001
12C23202
12S15001
12S4001
12S7501
22C100H1214
22C100H679
22C100H749
30S2001
30S2502
392779
40C197T03
40S267T01
40S31S01
40S8081L
40S8081R
44C14607
44C14608
60S38S01-S
62C100S01-S
62S48S01-S
C105008
C105701
C107601
C122101
C123007
C123401
C1242201
C125008
C22100H1215
C22100H680
C22100H750
C22100H750 Rework
C2210204
C2222902
C2260H750
C3230002
C80135X01
C80135X02
C80146X03
S1010001
S102501
S104001
S105002
S121501
S124001
S124002
S125001
S127501
S205001
S301301
S301901
S302502
S303801
S8002W01
S8010F02
S8013B05
S8013S01
S8013X04

Here are Jim's results:
006416386P004
006517964P005
0165B7993P004
0165B7993P004
0165B7993P004
0165B7993P004
0181C1188P031
0181C1188P032
0181C1188P033
0181C1188P039
0181C1188P040
0254C0873P008
0254C0873P008
0254C0873P008
0265C0341G004
0265C0341G004
0265C0341P018
0265C0341P018
0285B0692P001
0422A0775P001
0422A0775P001
0687C0960G009
0687C0960G009
0687C0960P014
0687C0960P014
0687C0960P014
0687C0960P015
0687C0960P016
10S10001
10S15001
12S15001
12S15001
12S4001
12S4001
12S7501
30S2001
30S2001
30S2502
40S267T01
40S31S01
40S8081L
40S8081L
40S8081R
44C14607
60S38S01-S
62S48S01-S
62S48S01-S
C105008
C105008
C105008
C107601
C107601
C107601
C107601
C125008
C125008
C125008
C22100H750 Rework
C2210204
C2222902
C2222902
C2222902
C2260H750
C2260H750
C80146X03
S102501
S105002
S121501
S121501
S124001
S124002
S124002
S127501
S205001
S301301
S301301
S301901
S303801
S8002W01
S8002W01
S8013B05
S8013B05
S8013S01
S8013X04
S8013X04

Thanks again to you both.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources