## Change Excel formula to Case Statement

 Author Message J39L4753 UDP Broadcaster Group: General Forum Members Points: 1443 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 SSC Guru Group: General Forum Members Points: 145094 Visits: 13349 `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?My blog at SQLKover.MCSE Business Intelligence - Microsoft Data Platform MVP J39L4753 UDP Broadcaster Group: General Forum Members Points: 1443 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 SSC Guru Group: General Forum Members Points: 145094 Visits: 13349 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?My blog at SQLKover.MCSE Business Intelligence - Microsoft Data Platform MVP J39L4753 UDP Broadcaster Group: General Forum Members Points: 1443 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 SSC Guru Group: General Forum Members Points: 145094 Visits: 13349 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?My blog at SQLKover.MCSE Business Intelligence - Microsoft Data Platform MVP J39L4753 UDP Broadcaster Group: General Forum Members Points: 1443 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 (25 views, 20.00 KB) Koen Verbeeck SSC Guru Group: General Forum Members Points: 145094 Visits: 13349 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?My blog at SQLKover.MCSE Business Intelligence - Microsoft Data Platform MVP J39L4753 UDP Broadcaster Group: General Forum Members Points: 1443 Visits: 382 Koen Verbeeck (10/3/2013)Which version of SQL Server?2008R2 Koen Verbeeck SSC Guru Group: General Forum Members Points: 145094 Visits: 13349 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?My blog at SQLKover.MCSE Business Intelligence - Microsoft Data Platform MVP