Thanks for the reply xsevensinzx!
Instead of creating a separate date-like dimension, we thought about creating a new dimension that will hold the surrogate key and exposing only the shift...

Thanks for this! Was just looking into CTEs today, so perfect timing.

Can you please tell me why "Listing 4: Using two CTEs instead of subqueries" code example has two "WITH"s...