Multiple Where Not Clauses

[Asuod_]

Hello,

Im trying to implement a where clause that removes a specific name and timestamp interval from a table. The current script i have seems to work but is there a better or more efficient way to write this script. Thank you in advance!

table:

Load

          Name,

           Timestamp

Resident table_temp 

Where

not (Name = 'name1' and Timestamp >= date('3/1/2022') AND Timestamp < date('4/1/2022'))

and not (Name = 'name2' and Timestamp >= date('3/1/2022') AND  Timestamp < date('4/1/2022'))

;

Drop table table_temp

[stevedark]

Hi @Asuod_ 

As you are being quite specific about what needs to come out I think your where statement is going to be that kind of long, but there are a couple of issues in there.

First up, I would recommend bracketing the not statements, as they may not give the result you are after at the moment:

Where
(not (Name = 'name1' and Timestamp >= date('3/1/2022') AND Timestamp < date('4/1/2022')))
and (not (Name = 'name2' and Timestamp >= date('3/1/2022') AND  Timestamp < date('4/1/2022')))

Also, the date function is potentially not working how you want. You either need to do a date# function to convert the string date to a number, or use makedate - which creates a date from parts:

Where
(not (Name = 'name1' and Timestamp >= makedate(2022,3,1) AND Timestamp < makedate(2022,4,1)))
and (not (Name = 'name2' and Timestamp >= makedate(2022,3,1) AND Timestamp < makedate(2022,4,1)))

To go between two dates, there is a function called BETWEEN that you might want to look at, which would replace the two separate compares.

In this case though, you are wanting everything in March, so if you calculate a month in your initial load, like this:

Date(MonthStart(Timestamp), 'MMM-YY') as Month,

You can then use this in the WHERE statement, and it will be much cleaner:

Where
(not (Name = 'name1' and Month = makedate(2022,3)))
and (not (Name = 'name2' and Month = makedate(2022,3)))

Hope that helps.

Steve

[vinieme12 · Accepted solution]

Option 1

Where

not (wildmatch(Name , 'name1','name2') and (Timestamp >= date('3/1/2022') AND Timestamp < date('4/1/2022'))

Option 2

ExcludeNames:

Load * Inline [

ExName 

name1

name2];

ExcludeDates:

Load 

Date(date#('3/1/2022','MM/DD/YYYY')+RECNO()) as exTimestamp

AUTOGENERATE date#('4/1/2022','MM/DD/YYYY')-date#('3/1/2022','MM/DD/YYYY');

table:

Load

Name,  Timestamp

Resident table_temp 

Where (Exists(ExName,Name)=0 and Exist (exTimestamp, Timestamp)=0);