Change Excel formula to Case Statement

  • 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

  • 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

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • 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.

  • 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.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • 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

  • To get some decent answers, it might help if you post table DDLs, sample data and desired output.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • 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

  • Which version of SQL Server?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (10/3/2013)


    Which version of SQL Server?

    2008R2

  • 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[/url]

    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)

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (10/3/2013)


    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[/url]

    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)

    Cool, thanks for this, I'll have a look and give it a go

  • Hi,

    This can be achieved using CTE

    declare @person table (PersonID int, ApptDate datetime, ApptStatus varchar(10), NoOfAppts int, OneStopOrNot VARCHAR(10))

    insert into @person (PersonID, ApptDate, ApptStatus, NoOfAppts) values

    (49820,'07-09-2013','Seen',1),

    (49827,'12-sep-2013','Seen',1),

    (49831,'07-sep-2013','Seen',1),

    (49834,'07-sep-2013','Seen',1),

    (50084,'07-sep-2013','Seen',1),

    (50097,'05-sep-2013','Seen',1),

    (50172,'05-sep-2013','Seen',1),

    (50172,'27-sep-2013','Seen',2),

    (50175,'05-sep-2013','Seen',1)

    ;WITH persons AS (

    SELECT

    rownum = ROW_NUMBER() OVER (ORDER BY PersonID, ApptStatus),

    *

    FROM @person

    )

    UPDATE cur SET OneStopOrNot =

    ( case WHEN cur.PersonID != prev.PersonID THEN 'OneStop'

    WHEN prev.PersonID IS NULL THEN 'OneStop'

    WHEN cur.PersonID = prev.PersonID AND prev.OneStopOrNot = 'MoreThan1' THEN 'MoreThan1'

    WHEN cur.PersonID = prev.PersonID AND cur.ApptDate = prev.ApptDate THEN 'OneStop'

    ELSE 'MoreThan1' END)

    FROM persons cur

    LEFT JOIN persons prev on prev.rownum = cur.rownum - 1

    select * from @person

  • This can be achieved using CTE

    declare @person table (PersonID int, ApptDate datetime, ApptStatus varchar(10), NoOfAppts int, OneStopOrNot VARCHAR(10))

    insert into @person (PersonID, ApptDate, ApptStatus, NoOfAppts) values

    (49820,'07-09-2013','Seen',1),

    (49827,'12-sep-2013','Seen',1),

    (49831,'07-sep-2013','Seen',1),

    (49834,'07-sep-2013','Seen',1),

    (50084,'07-sep-2013','Seen',1),

    (50097,'05-sep-2013','Seen',1),

    (50172,'05-sep-2013','Seen',1),

    (50172,'27-sep-2013','Seen',2),

    (50175,'05-sep-2013','Seen',1)

    ;WITH persons AS (

    SELECT

    rownum = ROW_NUMBER() OVER (ORDER BY PersonID, ApptStatus),

    *

    FROM @person

    )

    UPDATE cur SET OneStopOrNot =

    ( case WHEN cur.PersonID != prev.PersonID THEN 'OneStop'

    WHEN prev.PersonID IS NULL THEN 'OneStop'

    WHEN cur.PersonID = prev.PersonID AND prev.OneStopOrNot = 'MoreThan1' THEN 'MoreThan1'

    WHEN cur.PersonID = prev.PersonID AND cur.ApptDate = prev.ApptDate THEN 'OneStop'

    ELSE 'MoreThan1' END)

    FROM persons cur

    LEFT JOIN persons prev on prev.rownum = cur.rownum - 1

    select * from @person

  • By mistaken two times posted the same.

  • parulprabu (10/3/2013)


    By mistaken two times posted the same.

    Thanks for the CTE, I'll give that a go 🙂

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply