Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Changing VBA code - into - SQL statement

Avatar of Mich Mich
Mich MichFlag for United States of America asked on
VBASQL
4 Comments1 Solution91 ViewsLast Modified:
I have this code in Excel VBA:

If Cells(lngRow, ConstTASTMS_ActivateTime).Value < getdate() - 7 Then

      Cells(lngRow, ConstTASTimeClass).Value = "BOH"

      If Cells(lngRow, ConstTASTask_Status) = "Closed" Then

        If Cells(lngRow, ConstTASActual_EndDate).Value <= getdate() - 7 Then

          Cells(lngRow, ConstTASTimeClass).Value = "BOH-CO"

        Else

          Cells(lngRow, ConstTASTimeClass).Value = "BOH-C"

        End If
      End If

      If Cells(lngRow, ConstTASTask_StatusReason) = "Canceled" Then

        If Cells(lngRow, ConstTASActual_EndDate).Value <= getdate() Then

          Cells(lngRow, ConstTASTimeClass).Value = "BOH-XO"

        Else

          Cells(lngRow, ConstTASTimeClass).Value = "BOH-X"

        End If

      End If

    Else

      Cells(lngRow, ConstTASTimeClass).Value = "New"

      If Cells(lngRow, ConstTASTask_Status) = "Closed" Then Cells(lngRow, ConstTASTimeClass).Value = "New-C"

      If Cells(lngRow, ConstTASTask_Status) = "Canceled" Then Cells(lngRow, ConstTASTimeClass).Value = "New-X"

    End If


And now, I need to translate it into the SQL statement.
So far, I got this, and don't think it's correct.
Can someone please take a look and help me to correct it?


CASE WHEN TMS_ActivateTime < getdate() - 7 THEN

      TimeClass = "BOH"

      CASE WHEN Task_Status = "Closed" THEN

      CASE WHEN Actual_EndDate  <= getdate() - 7 THEN

      TimeClass  = "BOH-CO"

    ELSE

      TimeClass  = "BOH-C"
 
      CASE WHEN  Task_StatusReason = "Canceled" THEN

      CASE WHEN  Actual_EndDate  <= getdate() THEN

      TimeClass  = "BOH-XO"

        ELSE

      TimeClass  = "BOH-X"
       
    ELSE

      TimeClass  = "New"

      CASE WHEN  Task_Status = "Closed" THEN  TimeClass  = "New-C"

      CASE WHEN  Task_Status = "Canceled" THEN  TimeClass  = "New-X"

END