New training course: Customizing SQL Prompt. Watch now.

OUTER APPLY doesn't honor 'Parentheses style' settings

This is how SQL Prompt 8.2.3.2750 formats the code:
SELECT emp.EmployeeId
      ,history.WorkType1 - (history.WorkType2 + history.WorkType3)
FROM Emp.Employee emp
     OUTER APPLY
   (
      SELECT SUM(  CASE WHEN hst.WorkType = 1 THEN DATEDIFF(  DAY, hst.StartDate, CASE WHEN hst.EndDate < @ActualDate THEN hst.EndDate
                                                                                       ELSE @ActualDate
                                                                                  END
                                                           )
                        ELSE NULL
                   END
                ) AS WorkType1
            ,SUM(  CASE WHEN hst.WorkType = 2 THEN DATEDIFF(  DAY, hst.StartDate, CASE WHEN hst.EndDate < @ActualDate THEN hst.EndDate
                                                                                       ELSE @ActualDate
                                                                                  END
                                                           )
                        ELSE NULL
                   END
                ) AS WorkType2
            ,SUM(  CASE WHEN hst.WorkType = 3 THEN DATEDIFF(  DAY, hst.StartDate, CASE WHEN hst.EndDate < @ActualDate THEN hst.EndDate
                                                                                       ELSE @ActualDate
                                                                                  END
                                                           )
                        ELSE NULL
                   END
                ) AS WorkType3
      FROM Emp.WorkHistory hst
   ) AS history

Can't get something close to this with style options:
SELECT emp.EmployeeId
      ,history.WorkType1 - (history.WorkType2 + history.WorkType3)
FROM   Emp.Employee emp
       OUTER APPLY(
                   SELECT SUM(CASE WHEN hst.WorkType = 1 THEN DATEDIFF(DAY, hst.StartDate, CASE WHEN hst.EndDate < @ActualDate THEN hst.EndDate
                                                                                                ELSE @ActualDate
                                                                                           END)
                                   ELSE NULL
                              END
                             ) AS WorkType1
                         ,SUM(CASE WHEN hst.WorkType = 2 THEN DATEDIFF(DAY, hst.StartDate, CASE WHEN hst.EndDate < @ActualDate THEN hst.EndDate
                                                                                                ELSE @ActualDate
                                                                                           END)
                                   ELSE NULL
                              END
                             ) AS WorkType2
                         ,SUM(CASE WHEN hst.WorkType = 3 THEN DATEDIFF(DAY, hst.StartDate, CASE WHEN hst.EndDate < @ActualDate THEN hst.EndDate
                                                                                                ELSE @ActualDate
                                                                                           END)
                                   ELSE NULL
                              END
                             ) AS WorkType3
                   FROM Emp.WorkHistory hst
       ) AS history

Why extra spaces in 'SUM( CASE..' and 'DATEDIFF( DAY...' clauses?
Tagged:

Answers

Sign In or Register to comment.