CASE statement

[wfd1bdb]

I have a relatively simple query except 1 field is a text field which users can either choose from a pull down or type the entry. The problem occurs when the users abbreviate what are supposed to be standardized answers because if they don't they will not fit into the field. For example: 'Yearly RR Required' could be entered 'YRR Required' or 'YYR Req'. Also..... this field could contain more than one entry separated by a comma. For Example: 'Yearly RR Required', 'Continue Current MH Services'. My question is how can I write a CASE statement that will only pull 'Yearly RR Required' regardless of the other text that is in the field?

I wrote this CASE Statement....
CASE WHEN clmastbcassr.ASSESSTYPE = 'PAS' and clmastbcassr.RECOMMEND = 'Yearly RR Required' then DATEADD(DD,365,clmastbcassr.STAFFDATE) end AS [YEARLY DUE DATE]

This will return only data where the clmastbcassr.RECOMMEND field = 'Yearly RR Required' but if anything else is in that field it will come back NULL. I guess I could use LIKE but that still seems too vague. Help is appreciated!

BDB

 

[bborissov]

And when the condition:

clmastbcassr.ASSESSTYPE = 'PAS' and
clmastbcassr.RECOMMEND = 'Yearly RR Required'

is not matched what do you want as [YEARLY DUE DATE]?
Also better use

DateAdd(yy,1,clmastbcassr.STAFFDATE)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[wfd1bdb]

It can be NULL,,,it does not matter to the user.

 

[bborissov]

CASE WHEN clmastbcassr.ASSESSTYPE = 'PAS' and
          CHARINDEX('Yearly RR Required',
                    clmastbcassr.RECOMMEND) > 0 THEN
          DateAdd(yy,1,clmastbcassr.STAFFDATE)
     ELSE NULL END AS [YEARLY DUE DATE]

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[wfd1bdb]

Does CHARINDEX('Yearly RR Required', clmastbcassr.RECOMMEND)
mean find the character string 'Yearly RR Required' within the field clmastbcassr.RECOMMEND? If so how can I get it to find 'Yearly RR Required' and 'YYR Req' and 'Yearly RR Req'? Because users have typed in different string to mean the same thing.

Thanks

 

[bborissov]

If you can build a simple regular expression (I am not the master of this) that will match whole pattern of the field you could use PATINDEX(). You could try:

CASE WHEN clmastbcassr.ASSESSTYPE = 'PAS' and
          CHARINDEX('RR Req',
                    clmastbcassr.RECOMMEND) > 0 THEN
          DateAdd(yy,1,clmastbcassr.STAFFDATE)
     ELSE NULL END AS [YEARLY DUE DATE]

Because 'RR Req' is the only common part of that expression.
Of course if the user didin't enter 'YYRR'. But if the string you searched is user choice no way to do this, how do you know what the user is enter and what He/She means with that. If the user enter YYRR it maybe 'Yearly RR Required' but it can be 'Yeah Yeah Rock and Roll'.

Of course you could write something like

CASE WHEN clmastbcassr.ASSESSTYPE = 'PAS' and
          PATINDEX('%'+user inputed text+'%',
                    clmastbcassr.RECOMMEND) > 0 THEN
          DateAdd(yy,1,clmastbcassr.STAFFDATE)
     ELSE NULL END AS [YEARLY DUE DATE]

but this is no guarantee that you will have all records needed, becuase if the user enter 'YYR Req' that condition will ignore 'Yearly RR Required' records.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP