April 30, 2012 at 9:23 am
Dear Experts,
Can you please assist with this issue?
I have 3 fieldnames. They are receivedBy, Inperson, PostCard.
Users can only select from one of the fieldnames at a time.
For instance, receivedBy has 3 values. They are mail, phone, or fax.
If a user selects either mail or phone or fax from receivedBy, s/he cannot select from Inperson or Postcard.
If a uer selects from Inperson, s/he cannot select from postcard or receivedBy.
If a user selects from postcard, s/he cannot select from receivedBy or Inperson.
If user selects from receivedby, then the following code is a concatenation of eventdate + first letter of the values of receivedby (which can M for mail, P for phone and F for fax)+7 digit contactId)
This is the code that does the concatenation for receivedBy
select Contacts = CASE WHEN requestReivedBy IS NOT NULL Then (CONVERT(VARCHAR(8), EventDate, 112)+ substring(ReivedBy,1,1)+right( '0000000' + convert( varchar( 7 ), contactid ), 7 )) from mytable
This produces this result: 20120731F0000007
This is exactly what we want.
If the user selects from Inperson, then the value should be Contacts = (CONVERT(VARCHAR(8), EventDate, 112)+ 'I'+right( '0000000' + convert( varchar( 7 ), ContactId), 7 ))
The letter I represents Inperson
If user selects from PostCard then the value should be Contacts = (CONVERT(VARCHAR(8), EventDate, 112)+ 'C'+right( '0000000' + convert( varchar( 7 ), ContactId), 7 ))
The letter C represents PostCard
The problem is that We have not figured out how to put all of these together.
If user selects receivedBy, show the concatenated value for receivedBy.
If the user selects Inperson, show concatenated value for Inperson
If user selects Postcard, show concatenated value for postcard.
Can you please help?
Thanks a lot
April 30, 2012 at 10:51 am
With 163 points and 618 visits you should know we need some information to help. ddl, sample data and desired output please. See the first link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 1, 2012 at 4:44 am
Base on limited details posted...
Contacts = CONVERT(VARCHAR(8), EventDate, 112)
+
CASE WHEN RequestReivedBy IS NOT NULL THEN 'R'
WHEN Inperson IS NOT NULL THEN 'I'
WHEN PostCard IS NOT NULL THEN 'C'
ELSE 'X' -- for unknown, if you wish...
END
+ RIGHT( '0000000' + CONVERT( VARCHAR(7), ContactId),7))
Is ContactID is integer? If so the safest option would be converting to varchar(11), otherwise it may overflow...
If the answer is irrelevant, please post your DDL, sample data and expected results in consumable form. Link at the bottom of my signature.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply