What are the challenges you face when working across database platforms? Take the survey
Options

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.