Multiple Exclusions

[agsearle]

I have two sets of data, a series of numbers, and a series of dates, in two separate tables. The numbers are transaction numbers and the dates are the dates they were applied.

Numbers Example:
6000-0010
6000-0030
6200-0030
6200-0060

I need to exclude, in the script preferably, a selection of the numbers, eg 6000-0010 and 6200-0030, but only if their corresponding date is greater than 30/09/2008.

I've been trying for ages to get this done, but have failed miserably, can anyone help?

[agsearle]

The date format is stored as 2008-09-30, but I've tried both formats, usually the 30/09/2008 format works fine.

In the result I get all the 6000-0010 entries, regardless of the date.

[#3 · Accepted solution]

Thats odd,

it should work if you put WHERE NOT (Main_Sub_Trans_12 = '6000-0010' AND Due_Date_12 > '2008-09-30');

Maybe your dateformat is SET DateFormat='DD/MM/YYYY';

Try to load only the

WHERE NOT Due_Date_12 > '2008-09-30');

See if that works. Then youll know its not your dateformat.

rgds

Rey-man

[agsearle]

It didn't work with any date format, but I think I know where the problem might be.

As I wanted to join two fields together, I load them as a temporary table, then using RESIDENT reload the table trying to exclude as above. But I think it's still linking with the data in the temporary table, and hence all these solutions aren't working?

[agsearle]

Sorted!!!!!

Your suggestion works fine if I use it before the RESIDENT table, so I've removed this part and it works fine. Script now looks like:

DFKKOPBW_TEMP_12:
LOAD Due_Date as Due_Date_12,
 Main_Transaction&'-'&Subtransaction_For_Document_Item as Main_Sub_Trans_12,
 CA as CA_12
FROM  (qvd)
WHERE EXISTS (CA_12,CA)
AND NOT (Main_Transaction = '6000' AND Subtransaction_For_Document_Item = '0030' AND Due_Date > '30/09/2008');

Thanks for all your help, couldn't have done it without you.

Regards