Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
What you mentioned isn't working for you?
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.
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?
each statement works on it's own, so it can't be a syntax error
May be this
Where (Company = 'ABCD' and Status<>'Authorised' and CreateDate >= Today()-365) or (Company = 'ABCD' and Status = 'Authorised' and CreateDate < Today()-365);
Load *
from table
where
company = 'ABCD' and
(
(createdate <= 365 and status <> 'authorised') or
(createdate > 365)
);