Where statement

[Anonymous]

Can anyone help.  I need a where statement in my script which only includes any rows with the following criteria :

All data for company 'ABCD' where the status is NOT in authorised status and the create date > 365 ago

The script below does not work.

Where Company = 'ABCD' and

(Status<>'Authorised' and CreateDate>=(Today()-365))

Thoughts anyone?

Please help

Thanks

Phil

[ahaahaaha]

Hi Phil ,

Try

Where Company = 'ABCD' and Status<>'Authorised' and CreateDate>=Date((Today()-365))    Or

Where Company = 'ABCD' and Status<>'Authorised' and Num(CreateDate)>=(Today()-365)

Andrey

[sunny_talwar]

What you mentioned isn't working for you?

[juan_patrick]

hi,

Is case sensitive,check that or you can do this:

where upper(Status) <>'AUTHORISED'

BETTER:

WHERE

NOT MATCH(upper(Status), 'AUTHORISED')

the other thing is the format of the date field, but i think thats working.

[Anonymous]

The above suggestions dont work.

My actual where statement works, but it excludes all authorised and shows all records which are less than 12 months old.

What I want is for it to show all records with an exception of the records less than 12 months old which still have a status 'authorisation'. 

i.e.  If the record is less than 12 months old then it still needs to show the authorised fields.

Does that make sense?

[Anonymous]

each statement works on it's own, so it can't be a syntax error

[sunny_talwar]

May be this

Where (Company = 'ABCD' and Status<>'Authorised' and CreateDate >= Today()-365) or (Company = 'ABCD' and Status = 'Authorised' and CreateDate < Today()-365);

[juan_patrick]

Load *

from table

where

company = 'ABCD' and

(

  (createdate <= 365 and status <> 'authorised') or

  (createdate > 365)

);