I'm using SQL Server Management Studio. Where have I gone wrong?

Here is what I want to do - I want to get a value:

• If duration in months between exstart_start and dateord is <= 0, then

  • (minus 24 months from duration of exstart_start to datecoms ie only if the solution is >=0

  • (if the above is not satisfied then minus 12 months from duration of exstart_start to datecoms ie only if the solution is >= 0

  • If none are satisfied than make it 0

• If duration in months between exstart_start and dateord is > 0, then make it equal to duration in months between exstart_start and dateord

My attempt at the CASE:

CASE 
    WHEN DATEDIFF (m, exstart_start, dateord) <= 0
        THEN
            (CASE 
                 WHEN ((DATEDIFF (m, exstart_start, datecoms)) - 24 <= 0) 
                     THEN 0 
                     ELSE (DATEDIFF (m, exstart_start, datecoms)) - 24 
             END)
        ELSE
            ((CASE 
                  WHEN DATEDIFF (m, exstart_start, datecoms)) - 12 <= 0) 
                      THEN 0 
                      ELSE (DATEDIFF (m, exstart_start, datecoms)) - 12) 
              END) 
       END)

ELSE 
    DATEDIFF (m, exstart_start, dateord) END AS diffexstarttoordered

CodePudding user response：

For starter i think there is 1 END too much or 1 CASE less because we got 4 END and 2 ELSE for 1 CASE:

CASE --1.
WHEN DATEDIFF (m, exstart_start, dateord) <= 0
    THEN
-- ? here might be missing a CASE
        (CASE --2.
             WHEN ((DATEDIFF (m, exstart_start, datecoms)) - 24 <= 0) 
                 THEN 0 
                 ELSE (DATEDIFF (m, exstart_start, datecoms)) - 24 
         END --2.)
    ELSE --(1 else from first case)
        ((CASE --3.
              WHEN DATEDIFF (m, exstart_start, datecoms)) - 12 <= 0) 
                  THEN 0 
                  ELSE (DATEDIFF (m, exstart_start, datecoms)) - 12) 
          END--3.) 
   END --?)
ELSE(--? else from 1.CASE if 1 CASE is missing or too much)
  DATEDIFF (m, exstart_start, dateord) 
END --1.) 
AS diffexstarttoordered

update: After some thinking this might be your desired solution:

CASE WHEN DATEDIFF (m, exstart_start, dateord)<=0
 THEN
 (CASE WHEN ((DATEDIFF (m, exstart_start, datecoms)-24) >=0) 
                  THEN (DATEDIFF (m, exstart_start, datecoms)-24)
       WHEN ((DATEDIFF (m, exstart_start, datecoms)-12) >=0)
                  THEN (DATEDIFF (m, exstart_start, datecoms)-12)
       ELSE 0           
  END) 
ELSE DATEDIFF (m, exstart_start, dateord) 
END AS diffexstarttoordered