Hi,

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'))
OR
((Date = 'Nov' AND Person2 = 'X') AND (Date = 'Oct' AND Person2 = 'X'))
OR
((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?

thanks!

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')

Your DB design is incorrect. You need to learn about Database Normalization.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.