I have a problem where I need to search on data set with 5 fields.

Field 1 - Account number
Field 2 - Date
Field 3 - Person1
Field 4 - Person2
Field 5 - Person3

The data sat contains two months of data (November and October).

I need to compare if any of the Persons have an 'X' in the specific field both months.

So I have this statestatement

(Case when
((Date = 'Nov' AND Person1 = 'X') AND (Date = 'Oct' AND Person1 = 'X'))
((Date = 'Nov' AND Person2 = 'X') AND (Date = 'Oct' AND Person2 = 'X'))
((Date = 'Nov' AND Person3 = 'X') AND (Date = 'Oct' AND Person3 = 'X'))
Then 1 ELSE 0)

This statement is the statement I want however it needs to check the specific account number in November matches the account number in October. If the Account numbers do not match then there is no reason to run through the check.

Any thoughts?


Edited by Reverend Jim: Moved to programming forum

4 Months
Discussion Span
Last Post by benanamen

Since Date cannot be both Nov and Oct your Case will always return 0. Also, Date is possibly a reserved word and should be avoided as a field name. It's been a few years since I've had a SQL DB installed so I am a bit rusty (and I can't test it) but perhaps you are looking for

(SomeDate = 'Nov' OR SomeDate = 'Oct') AND
(Person1 = 'X' OR Person2 = 'X' OR Person3 = 'X')

Edited by Reverend Jim

Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.