whenriksen (10/18/2016)

---

I rewrote it yet again, without case or firstdate:

DATEADD(WEEK, ABS(( DATEDIFF(WEEK, '20161009', DATEADD(DAY, -1, o.modify_date))

+ 1 ) % 2), DATEADD(DAY, ( DATEPART(WEEKDAY, DATEADD(DAY, -1, o.modify_date))

- 7 ) * -1, o.modify_date))

I also inserted the cross join results into a temp table to more realistically represent the source table overhead with the expression version.

Turns out the join to the calendar table is the quickest on a large data set, even including the overhead of the parser.

--join to calendar table

SQL Server parse and compile time:

CPU time = 921 ms, elapsed time = 938 ms.

(163 row(s) affected)

Table '#temp_00000000E3BD'. Scan count 3, logical reads 14115, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'tDates'. Scan count 0, logical reads 1055, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 3026 ms, elapsed time = 1729 ms.

--expression without case statements

SQL Server parse and compile time:

CPU time = 5 ms, elapsed time = 5 ms.

(163 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#temp_00000000E3BD'. Scan count 3, logical reads 14115, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 7176 ms, elapsed time = 3618 ms.

--expression with case statements

SQL Server parse and compile time:

CPU time = 5 ms, elapsed time = 5 ms.

(163 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#temp_00000000E3BD'. Scan count 3, logical reads 14115, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 6099 ms, elapsed time = 3097 ms.