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’)
?
OK, but won’t that give us every part that has had a transaction?
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’;
yeah, don’t do that
it won’t work
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
Board maybe he should attempt a drop table :lol:
DROP TABLE INVENTORY_TRANS;
edit: if you even have the rights to do that, your DBA should be shot
edit2: DO NOT TRY IT
"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:
seems overly complicated for his needs…especially on a database that does a small amount of transactions
My bro is going to try(not the drop table lol) after he makes a fresh backup.
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.
nvm
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’)