Create a Simple Calendar or Date Dimension On-the-fly
I have a report where I want the user to pick from 3 options for the date range on the report: Latest business date; generally this would be yesterday Week-to-date Month-to-date The SQL below was actually used in Snowflake. Change CURRENT_DATE to GETDATE() for SQL Server. The idea is to get the list of dates from the CTE that are relevant and join to any fact table based on the value of the CALENDAR_KEY column. I use a format of yyyymmdd for CALENDAR_KEY. WITH CTE_DATES (DATE_FILTER, CALENDAR_KEY) AS ( -- BUSINESS_DATE SELECT 'BUSINESS-DATE' AS DATE_FILTER , CALENDAR_KEY FROM CALENDAR WHERE FULL_DATE = CURRENT_DATE - 1 UNION ALL -- CURRENT WEEK thru BUSINESS_DATE SELECT 'WEEK-TO-DATE' , CALENDAR_KEY FROM CALENDAR WHERE D...