## Change Excel formula to Case Statement

 Author Message J39L4753 SSC-Enthusiastic Group: General Forum Members Points: 117 Visits: 382 I have this formula I use within Excel, I'm moving most of the work into SQL & can't think how I can replicate this formula...IF(AND(A9=A8,L8="MoreThan1"),"MoreThan1",IF(A9=A8,IF(E9=E8,"OneStop","MoreThan1"),"OneStop"))Any ideas? o_O Koen Verbeeck SSCoach Group: General Forum Members Points: 16441 Visits: 13202 `SELECT   CASE WHEN (A9 = A8) AND (L8 = 'MoreThan1')         THEN 'MoreThan1'         ELSE CASE WHEN (A9 = A8)               THEN CASE WHEN (E9 = E8)                        THEN 'OneStop'                        ELSE 'MoreThan1'                  END               ELSE 'OneStop'            END   ENDFROM myTable` How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?Member of LinkedIn. My blog at SQLKover. MCSA SQL Server 2012 - MCSE Business Intelligence J39L4753 SSC-Enthusiastic Group: General Forum Members Points: 117 Visits: 382 Sorry forgot to add that A8, A9 E8, E9, L8 are cell rows within ExcelColumn A = PersonIDColumn E = DateOfApptColumn L = is the column that I will use to return the answer within the formula. Koen Verbeeck SSCoach Group: General Forum Members Points: 16441 Visits: 13202 J39L4753 (10/3/2013)Sorry forgot to add that A8, A9 E8, E9, L8 are cell rows within ExcelColumn A = PersonIDColumn E = DateOfApptColumn L = is the column that I will use to return the answer within the formula.I realized that, but I was not going to guess column names.I'm confident you can put the column names yourself in the query. How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?Member of LinkedIn. My blog at SQLKover. MCSA SQL Server 2012 - MCSE Business Intelligence J39L4753 SSC-Enthusiastic Group: General Forum Members Points: 117 Visits: 382 Koen Verbeeck (10/3/2013)J39L4753 (10/3/2013)Sorry forgot to add that A8, A9 E8, E9, L8 are cell rows within ExcelColumn A = PersonIDColumn E = DateOfApptColumn L = is the column that I will use to return the answer within the formula.I realized that, but I was not going to guess column names.I'm confident you can put the column names yourself in the query.But the bit I'm struggling with is that like with A9=A8, this is comparing PersonID with the row above etc Koen Verbeeck SSCoach Group: General Forum Members Points: 16441 Visits: 13202 To get some decent answers, it might help if you post table DDLs, sample data and desired output. How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?Member of LinkedIn. My blog at SQLKover. MCSA SQL Server 2012 - MCSE Business Intelligence J39L4753 SSC-Enthusiastic Group: General Forum Members Points: 117 Visits: 382 Koen Verbeeck (10/3/2013)To get some decent answers, it might help if you post table DDLs, sample data and desired output.Attached is a sample data set from excel, that I'm creating in SQL Attachments Example.xls (5 views, 20.00 KB) Koen Verbeeck SSCoach Group: General Forum Members Points: 16441 Visits: 13202 Which version of SQL Server? How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?Member of LinkedIn. My blog at SQLKover. MCSA SQL Server 2012 - MCSE Business Intelligence J39L4753 SSC-Enthusiastic Group: General Forum Members Points: 117 Visits: 382 Koen Verbeeck (10/3/2013)Which version of SQL Server?2008R2 Koen Verbeeck SSCoach Group: General Forum Members Points: 16441 Visits: 13202 You'll probably need a cursor to update your values, because to calculate the OneStopOrNot column for one row, you need the calculated result of OneStopOrNot column of the previous row.With a set-based solution all the rows would be updated at once, which means you cannot use the result of the previous row.To get you started on cursors:SQL Server Cursor ExampleJust to be perfectly clear: cursors are bad and slow for performance. However, in your specific business case you might need them. (unless someone else proves me wrong of course and uses set-based TSQL to solve this issue) How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?Member of LinkedIn. My blog at SQLKover. MCSA SQL Server 2012 - MCSE Business Intelligence