Building Dynamic Case Statement

Hi All

can any body help me for the following logic.


Input
-------
a,b

output
---------
case when isnull(a,0)=0 then b else a end

Input
-------
a,b,c

output
---------
case when isnull(a,0)=0 then (case when isnull(b,0)=0 then c else b
end) else a end

Input
-------
a,b,c,d

output
---------
case when isnull(a,0)=0 then (case when isnull(b,0)=0 then (case when
isnull(c,0)=0 then d else c end) else b end) else a end

and so on..i can give input a comma separated string

earlier help is highly appreciated.

Regards
Kalyan

0
kalyan
8/9/2010 9:56:54 AM
sqlserver.programming 1873 articles. 0 followers. Follow

3 Replies
800 Views

Similar Articles

[PageSpeed] 35

kalyan (kalikoi@gmail.com) writes:
> output
> ---------
> case when isnull(a,0)=0 then (case when isnull(b,0)=0 then (case when
> isnull(c,0)=0 then d else c end) else b end) else a end
> 
> and so on..i can give input a comma separated string
 
I have an article on my web site that describes several ways to unpack
a comma-separates list into a table, 
http://www.sommarskog.se/arrays-in-sql.html. Given the nature of the 
problem, you need a method where you get the list position.

Once you have the list in tabular format, you can use this SELECT to 
get the value:

WITH numbered AS (
    SELECT n, rowno = row_number() OVER(ORDER BY listpos)
    FROM   iter_intlist_to_tbl(@str)
    WHERE  n > 0
)
SELECT str FROM numbered WHERE rowno = 1




-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
0
Erland
8/9/2010 11:53:54 AM
On Aug 9, 4:53 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> kalyan (kali...@gmail.com) writes:
> > output
> > ---------
> > case when isnull(a,0)=0 then (case when isnull(b,0)=0 then (case when
> > isnull(c,0)=0 then d else c end) else b end) else a end
>
> > and so on..i can give input a comma separated string
>
> I have an article on my web site that describes several ways to unpack
> a comma-separates list into a table,http://www.sommarskog.se/arrays-in-sql.html. Given the nature of the
> problem, you need a method where you get the list position.
>
> Once you have the list in tabular format, you can use this SELECT to
> get the value:
>
> WITH numbered AS (
>     SELECT n, rowno = row_number() OVER(ORDER BY listpos)
>     FROM   iter_intlist_to_tbl(@str)
>     WHERE  n > 0
> )
> SELECT str FROM numbered WHERE rowno = 1
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Well i got the solution i used coalesce
0
kalyan
8/9/2010 1:00:45 PM
This looks like the recursive definition of COALESCE() given in the
standards:

COALESCE (a) => a

COALESCE (a, b) =>  (CASE WHEN a IS NULL THEN b ELSE a END)

COALESCE (a, b, c, ..) =>  (CASE WHEN a IS NULL
                                THEN COALESCE(b,c)
                                ELSE a END)

Just remember that COALESCE() promotes its results to highest data
type in the list and ISNULL() uses the data type of the first
argument.


0
CELKO
8/9/2010 4:41:29 PM
Reply:

Similar Artilces:

Dynamics GP Client Update Security Issue
Hi, I tried deploying the July Tax Update (.msp) via the Automatic client Update tool and sadly failed... When I enable the update at nex login my users (and myself) get the message but after acknowledging it they immediately get the "infamous" "Not privileged to open this form" message. I have no idea what this for can be. I wanted to do a Dexterity Trace but I have no way to start it before being loggued in. So I started recording a macro, hoping it would tell me which form is opened but I don't seem to have it in the macro. Here are the lines: .... # DEXVER...

Sumif statement
Is it possible to create a sumif statement that sums based on multiple conditions? ie. using a formula such as SUMIF ($A$2:$A$80,$A119,M$2:M$80) works fine, however, if I now want to add a further condition to this statement that would execute this formula only if say the values in $k$2:$k$80 are equal to "ABC". I assume some conditional and statement would be used or possibly an array formula, however, no success thus far. Ian Ian, SUMPRODUCT does it =SUMPRODUCT(($A$2:$A$80=$A119)*($K$2:$K$80="ABC")*(M$2:M$80)) Not even an array formula. -- HTH Bob Phillip...

How to migrate Exchange mailbox in this case?
The headquarter of my company is located in NewYork, and the branch office is located in Denver. Originally, both of the 2 offices have domain controllers, and the Denver office is a child domain of the NewYork domain. But only NewYork has the Exchanger server. So everyone in the Denver office has 2 accouts: one is for the local domain logon, another is in the NewYork, used as their email account. We have built up a Exchange server in Denver office recently, and we are going to migrate all mailboxes of the Denver employees to Denver local Exchange server. But the problem is: these use...

Creating a Case automatically
Hi all, Is it possible to create a case when a date has been reached. For example a Technical Team does Maintenance visits to clients with a Service Level Agreement. These maintenance visits occur every 1st and on the 15th of every month. Is this possible to create cases automatically on these dates mentionaed above? Any ideas would be highly appreciated? Lebo Hi Lebo, Try CRM Workflow. [CRM Folder] >> [Workflow Manager] Cheers, Jim -- Technical Consultant @ United Kingdom - Microsoft CRM | SharePoint | Office | Scribe Data Migration "Lebo" wrote: > Hi all, ...

Update path to Dynamics GP 10 ..
The documentation says we can move from .. 8.00g73 >> 10.0 Is this refering to the version of Dexterity or Great Plains. We are on .. Great Plains .. 8.00g34 Dexterity .. 8.00m076 Thx a bunch. M. That refers to your GP version. You need to download and apply a service pack prior to upgrading to v10. "Maria" wrote: > The documentation says we can move from .. > 8.00g73 >> 10.0 > > Is this refering to the version of Dexterity or Great Plains. > > We are on .. > Great Plains .. 8.00g34 > Dexterity .. 8.00m076 > > Thx a bunch. >...

Dynamic Table Problem
I am using excel 2007. I had originally asked this question in the function section, but VBA was suggested. I want to create an invoice where the total number of line items is dynamic. I have created the 'header' section of the invoice with company name, customer name, etc and I have created the 'body' of the invoice using a table. This table has Qty, Description, Unit Cost, and Item Cost. Item cost is a formula showing Qty * Unit Cost. This table works great and is dynamic. As I simply type more items, the table automatically expands and formats correctly. ...

Dynamic fields in form
Good morning, We are developing a MS CRM solution and we have a problem while doing the entities personalization... We have to implement a form with a field called "type". When selecting a value for "type" (20 or 30 possible values). Dependeng on the selected field, new fields will be enabled or disabled for every of those types. Those fields will be visible or invisible in a form or tab (knowing the 8 tabs limit...). Seeing the big number of fields we will have, we don't know the best solution: - Create a huge tab with all the fields and view/hide the ones for ...

combining statements
Hello Is there a way to use an IF statement to Combine statements from multiple sheets based on a date? Thanks Do you mean something like =IF(AND(Sheet1!A1<TODAY(),Sheet2!A1<TODAY()),"Both less","Not both less") -- HTH RP (remove nothere from the email address if mailing direct) "Jack" <dmmclean@rogers.com> wrote in message news:CMedneg7aY8zeSDcRVn-sg@rogers.com... > Hello > Is there a way to use an IF statement to Combine statements from multiple > sheets based on a date? > > Thanks > > ...

Multiple IF statement replacements?
I need some help and don't know if it will be possible to get it to do what I need. I am automating all my paperwork I do every morning and I need a cell on a worksheet to decypher people's initials and translate it to a name. here is a sample of what I have. =IF('INDIVIDUAL TARGET'!A21="DH","Diane",IF('INDIVIDUAL TARGET'!A21="LT","Lisa",IF('INDIVIDUAL TARGET'!A21="LB","Linda",IF('INDIVIDUAL TARGET'!A21="RN","Rae",IF('INDIVIDUAL TARGET'!A21="LM","Liz&q...

Dynamic copying of data to new sheets
I need to create a seperate sheet in an excel workbook that contains th information from a data sheet. There are multiple columns and I need to seperate the rows out tha match values in one column. Number Status Notes 1 Fail none 2 Pass none 3 Not Run none 4 Missing none I need to make a seperate sheet for fail, pass, not run, and missing. I would like these sheets to update automatically, as the data sheet i quite large and it would take sometime to update the information b hand. Thanks, Quirthano -- Message posted from http://www.ExcelForum.com ...

How to write APOSTROPHE into an XML SelectNodes statement????
Ok, I've looked for an answer for this, because I'm sure it's been asked a thousand times... but no luck... so here goes. Imagine I want to create a NodeList based on an XPath statement. The 'query' portion of that statement wants to search for the following value: Joe's Garage ....now please note the APOSTROPHE in the above string. Now, you VB.Net & XML experts know the following will NOT work: myXmlNodeList = MyXmlDoc.SelectNodes("MyParentNode/SomeOtherNode[Title='Joe's Garage']) ....because the *apostrophe* in "Joe's" will s...

Downloading Statements and Using MSN Bill Pay
I use MSN BillPay with my checking account to pay bills. My bank offers the downloading of statements to Money. Can I connect to my bank to download statements on the same checking account that I use to connect to MSN Bill Pay to pay bills? If so, how do I set that up? -- Semper Fi Kevin Campbell "Some critics have said our duties in Iraq must be internationalized. This particular criticism is hard to explain to our partners in Britain, Australia, Japan, South Korea, the Philippines, Thailand, Italy, Spain, Poland, Denmark, Hungary, Bulgaria, Ukraine, Romania, the Netherlands, Nor...

building array
I am trying to build a dynamic array. Sadly, I can't seem to see my error. I have iStartWkCol = 2 iEndWkCol = 74 iNoWks = iEndWkCol - iStartWkCol + 1 ReDim Preserve aNewDbData(1, iNoWks) As Variant 'Resize our array to accomodate the data For iCounter = iStartWkCol To iEndWkCol 'Loop through the Week Cols of the Worksheet iarrPosn = iCounter - iStartWkCol aNewDbData(0, iarrPosn) = _ Range(CLetter(iCounter) & "2").Value 'Date aNewDbData(1, iarrPosn)...

Upper Case only?
I'm using Excel 2007, is it possible for me to format a column so that any letters typed into it are automatically shown as upper case, regardless of whether I have Caps Lock switched on? Thanks for any replies. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A:A" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If Not .HasFormula Then .Value = UCase(.Value) End If ...

NEWBIE: Adding New Fields to the Case Form
I searched the forum but I cannot find anything answering this question? I have aded a new Attribute to the "CASE ENTITY" called "SUBSCRIPTION TYPE". "SUBSCRIPTION TYPE" is a picklist value I creaed that I wish to be able to display on the standard CASE FORM. "SUBSCRIPTION TYPE" should be a 1-To-1 field to a CASE. When I attempt to customize my standard CASE FORM by using "Add fields" I dont see my new "SUBSCRIPTION TYPE" Attribute in the list of available fields. I want to add this new field to a Tab I created on the default case f...

Where can I find a Publisher template for DVD case inserts?
I need to print inserts for 7.5" x 5.25" DVD cases. http://office.microsoft.com/en-us/assistance/HA011649681033.aspx "taherte" <taherte@discussions.microsoft.com> wrote in message news:8153F347-E3C7-4261-8FF9-AA0AD0C668B9@microsoft.com... |I need to print inserts for 7.5" x 5.25" DVD cases. I think taherte wants a template for a label that fits the DVD case movies normally come in. It appears a sheet of paper 7.25"x10.75" will fit nicely behind the clear plastic front. It is time for taherte to get the artistic juices flowing and crea...

Dynamic Data Validation
I have numeric values (0, 1, 2, 3, or 4) in cells for each of columns I, J, K, L. User can also enter values (0, 1, 2, 3, 4, or 5) in the cells under columns W, X, Y. Now for each row, if any of the cells under the columns I, J, K, and L contain a value 1, user may only be allowed to enter any of the values 1, 2, 3, 4, or 5 in the cells under the columns W, X, and Y in that row, ELSE user may only be allowed to enter 0. My first question is how may I force this validation? My second question is how do I ensure integrity across both sets of columns? Really appreciate a...

Unresolved external with custom build of MFC71.DLL
Hi, I have build a custom version of the MFC71 and the MSVCRT dll and now I am trying to link this with my C++ projects. But I got a lot of unresolved externals like error LNK2001: Nichtaufgelöstes externes Symbol "public: virtual long __thiscall CWnd::put_accValue(struct tagVARIANT,unsigned short *)" (?put_accValue@CWnd@@UAEJUtagVARIANT@@PAG@Z) I think the problem is that this an some other functions are missing in src/mfc/intel/mfc71.def What can I do? Is there a download page where I can get a corret file. Sounds like you enjoy living dangerously. As far as I know,...

Regarding Dynamic chart
Dear All I have problem in formula ans it was solved Mr. Max , now i am facing problem in Chart please do the needful . I am mention the my problem and the formula given by Mr.Max . In cell b1 my input value varies from 1-30 in e1 my input will be amount it varies assume 300000. i want formula in a5 to show 0 and a6 as 1 so on till i get the input in b1 .. and in c5 i need a formula =$e$1*((2*(a5/$b$1)^2-(a5/$b$1)^4) till same as in column based on the value in b1. wish you all a very happy new year -2010 in advance A B C D E ...

displaying spreadsheet info on a web page dynamically
I am trying to display a spreadsheets' contents in a web page, and have it auto-refresh to display changes made to the spreadsheet. I've been messing around with it all morning and cant seem to figure out what I am doing wrong. I know it has something to do with external data ranges, save as a web page with pivot table interactivity I believe but I cant seem to get it to work. Can some one assist me with this problem or point me in the direction of some helpful tutorials other than the microsoft tutorials, I browsed threw them but could not find a sufficient solution. Tha...

Sorting columns in case view
I'm looking at the list of cases for an account, and the list goes on to more than one page. If I sort the list by clicking on a column header, only the current page gets sorted. For instance, to start with the cases are sorted by title, say. I want to see the most recent case, so I click on the Date Created column header (twice). The most recent case from this page goes to the top, not the most recent across the whole list. Is this a design feature? I seriously doubt it. If not, how does a simple bug like this make it through to version 3 of a major piece of commercial software? ...

IF Statement with both AND and OR Criteria
I'm having difficulty creating an if statement that has both AND/OR criteria. The following statement covers the OR part, but how do I include the AND condition? =IF(OR(A1=TRUE,A2=TRUE),C2,"N/A") In other words, return the value in C2 if A1=TRUE OR A2=TRUE. In addition, F2 must = "Include" Any suggestions would be greatly appreciated. Thanks in advance! T Try this: =If(And(F2="Include",Or(A1="True",A2="True")),C2,"NA") -- HTH, RD ===================================================== Please keep all correspondence wit...

Insufficient Memory when emailing Statements
Hi there My client has a terminal server (Windows 2003 R2 service 2) with 2GB of memory. Dynamics 10 and Adobe PDF Writer/Distiller 9.0 is installed. I have switched off the 2 font settings boxes in the Adobe PDF printer properties. However I still get the error when we try email 1 statement to the customer. The error is: Could not e-mail status report to Sender. Insufficient Memory Please help! Robin I usually recommend that my clients by individual workstations with 2 gigs. Now here we have a terminal server sharing 2 gig between how many users? You should expect OOM errors. R...

Update Case History automatically after New Service Activity is sa
Hi All, When a technician completes a service activity, a custom field on the form "Current Status" will contain the details of the current status of the case. The "Regarding" field on the Service Activity form already contains the particular case. I want to add the "Service Activity (Current Status)" field contents to the beginning of the "Case (Current Status)" field to allow users to see the current status of each case, with the last entry showing on the top of the form. Any ides would be welcome. Thanks -- David Dalton IT Systems Ltd Kill...

DLookup within Iif statement
I'm having trouble getting an Iif Statement that includes a Dlookup to work. Here is what I have but the records where the Iif is true show nothing for this field. IIf([tblOrders].[FreightOnly]=False,[tblOrders].[Price]-DLookUp("Price","tblProductCost","EffectiveDate <=#" & [qryNetRevenue].![Loadeddate] & "#"),[tblOrders].[Price]) Thanks! Walter Walter, there is a typo here: [qryNetRevenue].![Loadeddate] you have both a . and a ! Maybe you need to remove the ! from [qryNetRevenue].![Loadeddate] If the above doesn't w...