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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros