October 1, 2015 at 10:54 am
I have a DistributorInvoiceNumber that can end with in 'R', 'A', 'CRR' or 'CR'.
I am trying to write a case statement like so:
CASE WHEN RIGHT([ih].[DistributorInvoiceNumber],1) = 'A'
THEN 'ADJ'
WHEN RIGHT([ih].[DistributorInvoiceNumber],1) = 'R'
THEN 'REV'
WHEN RIGHT([ih].[DistributorInvoiceNumber],3) = 'CRR'
THEN 'REV'
WHEN RIGHT([ih].[DistributorInvoiceNumber],2) = 'CR'
THEN 'CREDIT'
ELSE NULL END AS 'Status'
For the most part the code is working, with the exception of the fields that just end in 'R'.
An example of this is 471268R, 2525125901CRR, 11100325230CR
Basically if the number ends with an A, then its an Adjustment, if it ends with JUST an R, then its a Reversal; if it ends with just a CR then it is a Credit and if it ends with CRR then it is a Reversal (Credit Reversal).
How can I differentiate between the different R's since three of them end with R? Would I use a RTRIM command somehow?
October 1, 2015 at 11:21 am
shankley (10/1/2015)
I have a DistributorInvoiceNumber that can end with in 'R', 'A', 'CRR' or 'CR'.I am trying to write a case statement like so:
CASE WHEN RIGHT([ih].[DistributorInvoiceNumber],1) = 'A'
THEN 'ADJ'
WHEN RIGHT([ih].[DistributorInvoiceNumber],1) = 'R'
THEN 'REV'
WHEN RIGHT([ih].[DistributorInvoiceNumber],3) = 'CRR'
THEN 'REV'
WHEN RIGHT([ih].[DistributorInvoiceNumber],2) = 'CR'
THEN 'CREDIT'
ELSE NULL END AS 'Status'
For the most part the code is working, with the exception of the fields that just end in 'R'.
An example of this is 471268R, 2525125901CRR, 11100325230CR
Basically if the number ends with an A, then its an Adjustment, if it ends with JUST an R, then its a Reversal; if it ends with just a CR then it is a Credit and if it ends with CRR then it is a Reversal (Credit Reversal).
How can I differentiate between the different R's since three of them end with R? Would I use a RTRIM command somehow?
If you test for CRR before CR, and CR before testing for R then you should get what you want.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply