Member Avatar for thomas_43

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!

Edited by Reverend Jim because: Moved to programming forum
  • 3 Contributors
  • 2 Replies
  • 308 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by benanamen
Member Avatar for Reverend Jim

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
Member Avatar for benanamen

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.