We help IT Professionals succeed at work.

Changing VBA code - into - SQL statement

Mich Mich
Mich Mich asked
on
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
Comment
Watch Question

Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
What flavor of SQL are you targeting, SQL Server, Oracle, Access, ...


»bp
Test your restores, not your backups...
Expert of the Year 2019
Top Expert 2016
Commented:
Okay, double check this, it should be what you had above in VBA.

SELECT CASE
           WHEN tms_activatetime < Getdate() - 7 THEN 
               CASE 
                   WHEN task_status = "closed" THEN 
                       CASE 
                           WHEN actual_enddate <= Getdate() - 7 THEN "boh-co" 
                           ELSE "boh-c" 
                       END 
                   WHEN task_statusreason = "canceled" THEN 
                       CASE 
                           WHEN actual_enddate <= Getdate() THEN "boh-xo" 
                           ELSE "boh-x" 
                       END 
                   ELSE "boh" 
               END 
           ELSE 
               CASE 
                   WHEN task_status = "closed" THEN "new-c" 
                   WHEN task_status = "canceled" THEN "new-x" 
                   ELSE "new" 
               END 
       END AS TimeClass

Open in new window


»bp
Mich MichBI Lead

Author

Commented:
Works PERFECT! Thanks Bill :-)
Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
Welcome, glad that helped.


»bp