Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Change Excel formula to Case Statement Expand / Collapse
Author
Message
Posted Thursday, October 3, 2013 12:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 14, 2014 3:20 AM
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

Post #1501008
Posted Thursday, October 3, 2013 12:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 13,328, Visits: 10,195
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
END
FROM 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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1501009
Posted Thursday, October 3, 2013 1:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 14, 2014 3:20 AM
Points: 117, Visits: 382
Sorry forgot to add that A8, A9 E8, E9, L8 are cell rows within Excel

Column A = PersonID
Column E = DateOfAppt
Column L = is the column that I will use to return the answer within the formula.



Post #1501019
Posted Thursday, October 3, 2013 1:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 13,328, Visits: 10,195
J39L4753 (10/3/2013)
Sorry forgot to add that A8, A9 E8, E9, L8 are cell rows within Excel

Column A = PersonID
Column E = DateOfAppt
Column 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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1501021
Posted Thursday, October 3, 2013 1:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 14, 2014 3:20 AM
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 Excel

Column A = PersonID
Column E = DateOfAppt
Column 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
Post #1501028
Posted Thursday, October 3, 2013 1:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 13,328, Visits: 10,195
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1501029
Posted Thursday, October 3, 2013 2:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 14, 2014 3:20 AM
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


  Post Attachments 
Example.xls (5 views, 20.00 KB)
Post #1501052
Posted Thursday, October 3, 2013 2:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 13,328, Visits: 10,195
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1501053
Posted Thursday, October 3, 2013 2:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 14, 2014 3:20 AM
Points: 117, Visits: 382
Koen Verbeeck (10/3/2013)
Which version of SQL Server?


2008R2
Post #1501059
Posted Thursday, October 3, 2013 3:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 13,328, Visits: 10,195
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 Example

Just 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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1501072
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse