# SQL query ?????????????????????????
**URL:** https://forums.speedlife.net/t/sql-query/73727
**Category:** NYSpeed Off Topic
**Created:** [September 18, 2009, 12:20pm UTC](https://forums.speedlife.net/t/sql-query/73727 "2009-09-18T12:20:56Z")
**Posts on this page:** 13
**Page:** 2
### Author:  [@LZ1](https://forums.speedlife.net/u/LZ1)
#### Post date: [September 18, 2009, 1:29pm UTC](https://forums.speedlife.net/t/sql-query/73727/21 "2009-09-18T13:29:50Z")
SELECT ID, DESCRIPTION FROM PART WHERE PARTID NOT IN (SELECT INVENTORY\_TRANS.PART\_ID FROM INVENTORY\_TRANS WHERE TRANSACTION\_DATE BETWEEN ‘10-OCT-2008’ AND ‘30-SEP-2009’)
?
---
### Author:  [@Blue\_Eyed\_Devil](https://forums.speedlife.net/u/Blue_Eyed_Devil)
#### Post date: [September 18, 2009, 1:33pm UTC](https://forums.speedlife.net/t/sql-query/73727/22 "2009-09-18T13:33:27Z")
> [@boardjnky4](#):
>
> Remove ‘NOT’
OK, but won’t that give us every part that has had a transaction?
---
### Author:  [@boardjnky4](https://forums.speedlife.net/u/boardjnky4)
#### Post date: [September 18, 2009, 1:34pm UTC](https://forums.speedlife.net/t/sql-query/73727/23 "2009-09-18T13:34:47Z")
I’ve never seen something like this “NOT BETWEEN ‘10-OCT-2008’ AND ‘30-SEP-2009’;” used
usually more like:
where
last\_transaction\_date \< ‘10-OCT-2008’
OR
last\_transaction\_date \> ‘30-SEP-2009’;
---
### Author:  [@boardjnky4](https://forums.speedlife.net/u/boardjnky4)
#### Post date: [September 18, 2009, 1:35pm UTC](https://forums.speedlife.net/t/sql-query/73727/24 "2009-09-18T13:35:35Z")
> [@Blue Eyed Devil](#):
>
> OK, but won’t that give us every part that has had a transaction?
yeah, don’t do that
it won’t work
---
### Author:  [@boardjnky4](https://forums.speedlife.net/u/boardjnky4)
#### Post date: [September 18, 2009, 1:37pm UTC](https://forums.speedlife.net/t/sql-query/73727/25 "2009-09-18T13:37:03Z")
SELECT DISTINCT P.ID,P.DESCRIPTION FROM PART P, INVENTORY\_TRANS I
WHERE P.ID(+) = I.PART\_ID AND (I.TRANSACTION\_DATE \< ‘10-OCT-2008’ OR I.TRANSACTION\_DATE \> ‘30-SEP-2009’);
I wonder if that query would work
---
### Author:  [@LZ1](https://forums.speedlife.net/u/LZ1)
#### Post date: [September 18, 2009, 1:38pm UTC](https://forums.speedlife.net/t/sql-query/73727/26 "2009-09-18T13:38:52Z")
Board maybe he should attempt a drop table :lol:
---
### Author:  [@boardjnky4](https://forums.speedlife.net/u/boardjnky4)
#### Post date: [September 18, 2009, 1:40pm UTC](https://forums.speedlife.net/t/sql-query/73727/27 "2009-09-18T13:40:59Z")
DROP TABLE INVENTORY\_TRANS;
edit: if you even have the rights to do that, your DBA should be shot
edit2: DO NOT TRY IT
---
### Author:  [@LZ1](https://forums.speedlife.net/u/LZ1)
#### Post date: [September 18, 2009, 1:42pm UTC](https://forums.speedlife.net/t/sql-query/73727/28 "2009-09-18T13:42:07Z")
> [@boardjnky4](#):
>
> DROP INVENTORY\_TRANS;
>
> edit: if you even have the rights to do that, your DBA should be shot
"Change the int and datetime to whatever they should be for the database in question.
Change the temporary table name to something unique in your db.
Create Table Temp1(PART\_ID int, TRANSACTION\_DATE datetime)
INSERT INTO Temp1 SELECT PART\_ID, TRANSACTION\_DATE FROM INVENTORY\_TRANS WHERE TRANSACTION\_DATE NOT BETWEEN ‘10-OCT-2008’ AND ‘30-SEP-2009’
SELECT PART.ID, PART.DESCRIPTION FROM PART LEFT OUTER JOIN Temp1 ON PART.ID = Temp1.PART\_ID WHERE TRANSACTION\_DATE IS NULL
DROP TABLE Temp1
"
Is what someone at work just sent me…
Why don’t you email them with what I just posted and tell them you want something like that :lol:
---
### Author:  [@boardjnky4](https://forums.speedlife.net/u/boardjnky4)
#### Post date: [September 18, 2009, 1:46pm UTC](https://forums.speedlife.net/t/sql-query/73727/29 "2009-09-18T13:46:57Z")
seems overly complicated for his needs…especially on a database that does a small amount of transactions
---
### Author:  [@Blue\_Eyed\_Devil](https://forums.speedlife.net/u/Blue_Eyed_Devil)
#### Post date: [September 18, 2009, 1:59pm UTC](https://forums.speedlife.net/t/sql-query/73727/30 "2009-09-18T13:59:34Z")
My bro is going to try(not the drop table lol) after he makes a fresh backup.
---
### Author:  [@Blue\_Eyed\_Devil](https://forums.speedlife.net/u/Blue_Eyed_Devil)
#### Post date: [September 21, 2009, 11:27am UTC](https://forums.speedlife.net/t/sql-query/73727/31 "2009-09-21T11:27:59Z")
The following statement gave us what we wanted even though it has shit in it we didn’t want…
SELECT DISTINCT ID,description FROM PART WHERE
DESCRIPTION = ‘DO NOT USE’ OR STATUS = ‘O’
OR ID NOT IN(SELECT PART\_ID FROM
INVENTORY\_TRANS WHERE PART\_ID IS NOT NULL AND TRANSACTION\_DATE BETWEEN
‘01-OCT-2008’ AND ‘30-SEP-2009’) ORDER BY ID;
My bro had originally told the guy about the “DO NOT USE” we put in some description because they were obsolete but, he kept telling him to forget about that and of course he did not. lol I asked if this guy was foreign because he did not seem to understand English… at all. My bro said he sounded like he was born and raised in the US. He may be a super computer geek but he should not be talking to customers. lol
Thanks for the help guys. I really need to learn sql talk.
---
### Author:  [@DrDoS](https://forums.speedlife.net/u/DrDoS)
#### Post date: [September 21, 2009, 1:14pm UTC](https://forums.speedlife.net/t/sql-query/73727/32 "2009-09-21T13:14:25Z")
nvm
---
### Author:  [@Jack](https://forums.speedlife.net/u/Jack)
#### Post date: [September 22, 2009, 7:17pm UTC](https://forums.speedlife.net/t/sql-query/73727/33 "2009-09-22T19:17:39Z")
I can try to help, w/o actually having a database to play in, I may be of little use.
Wouldn’t this work? NOT BETWEEN is valid syntax (at times)… and it would only return PART\_IDs, which is what you were looking for.
If that is truely a transaction table, it should have all transactions, even those outside the date range. If there are no transactions, at any time, use the 2nd piece of code. The only piece I changed on the 2nd part was adding the “!=” in front of the 2 values. I’m assuming the ID’s you got from your brother’s code are the ones that are marked DO NOT USE or have a status O (obsolete), and those are the values you didn’t want? If you did want to see those, how about some detail on the ones you don’t want to see.
sry, don’t have time to proof read this.
SELECT PART\_ID
FROM INVENTORY\_TRANS
WHERE PART\_ID IS NOT NULL
AND TRANSACTION\_DATE NOT BETWEEN ‘01-OCT-2008’ AND ‘30-SEP-2009’
SELECT DISTINCT
ID
FROM PART
WHERE DESCRIPTION != ‘DO NOT USE’
AND STATUS != ‘O’
AND ID NOT IN (SELECT PART\_ID
FROM INVENTORY\_TRANS
WHERE PART\_ID NULL
OR TRANSACTION\_DATE BETWEEN ‘01-OCT-2008’ AND ‘30-SEP-2009’)
[Previous page](https://forums.speedlife.net/t/sql-query/73727.md?page=1)