NOT IN

[aomara]

I am making a report that brings the Non-Moved Items in a store database.
I have the "ITEMS" table include ITEM_CODE column
and Transaction table "TXN_TABLE" includes ITEM_CODE column and TXN_DATE coulmn.

I would like to bring all items from the "ITEMS" table that are not in the "TXN_TABLE" within certain date.

I Try to do this in diffrenent ways, But It fails.

 

[MisterMo]

depending on your database, I work on a SQL2000

Try something like this

select * from ITEMS
where ITEMS_ID not in (select distinct ITEM_CODE  from
ITEMS where 
ITEM_CODE   in (select distinct ITEM_CODE from TXN_TABLE
where (TXN_DATE  between convert(datetime, '01/01/2004',103) and convert(datetime, '01/01/2005'))))

Mo

 

[GJParker]

What version of CR are you using ?

One way to accomplish this would be to usea subquery i.e.

SELECT ITEMS.ITEM_CODE
FROM ITEMS CROSS JOIN TXN_TABLE
WHERE (NOT (ITEMS.ITEM_CODE IN 
(SELECT ITEM_CODE FROM TXN_TABLE 
WHERE (TXN_DATE > ?DATE) AND (TXN_DATE < ?DATE))))

post more information about your CR version and database type if you require further help.




Gary Parker
MIS Data Analyst
Manchester, England

 

[aomara]

Many Thanks for your help.

1) My database is SQL.

2)I know how to do it using the SQL.

I want to do this with the Crystal Reports 9.
How to adjust tables and relations in the Database Export to bring this results.

Thank You.

 

[lbass]

Try the earlier suggestions by selecting "Add Command" in the database expert. The query would act as the datasource for your report, or you could link the command to other tables.

-LB

 

[Turkbear]

Hi,
You can try joining the tables in CR, using the
ITEM_CODE field and make it a Left Outer <> join from
ITEMS to TXN_TABLE..
In the record selection criteria specify the Date needed.

This should return all records where the ITEM_CODE is NOT in the TXN_TABLE...





To Paraphrase:"The Help you get is proportional to the Help you give.."