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:

  1. Latest business date; generally this would be yesterday
  2. Week-to-date
  3. 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 DATE_PART(WEEK, FULL_DATE) = DATE_PART(WEEK, CURRENT_DATE - 1)

    AND YEAR(FULL_DATE) = DATE_PART(YEAR, CURRENT_DATE - 1) 

    AND FULL_DATE <= CURRENT_DATE - 1


    UNION ALL


    -- CURRENT MONTH thru BUSINESS DATE

    SELECT

        'MONTH-TO-DATE'

    ,   CALENDAR_KEY

    FROM CALENDAR

    WHERE FULL_DATE BETWEEN

        DATE_FROM_PARTS(

            DATE_PART(YEAR, CURRENT_DATE - 1)

        ,   DATE_PART(MONTH, CURRENT_DATE - 1)

        ,   1

        ) 

    AND CURRENT_DATE - 1

)



/*

SELECT *

FROM CTE_DATES

ORDER BY CALENDAR_KEY


SELECT

    DATE_FILTER

,   COUNT(*) AS NUMBER_OF_DAYS    

,   MIN(CALENDAR_KEY) AS MIN_CALENDAR_KEY

,   MAX(CALENDAR_KEY) AS MAX_CALENDAR_KEY

FROM CTE_DATES

GROUP BY DATE_FILTER

*/


Comments

Post a Comment

Popular posts from this blog

Using sp_executesql with OPENQUERY

 gemini prompt: give me the in depth details on using openquery in sql server with the sp_executesql stored procedure The OPENQUERY function in SQL Server allows you to execute a pass-through query on a specified linked server . By design, OPENQUERY 's arguments—the linked server name and the query string— do not accept local T-SQL variables . However, you can overcome this limitation, particularly for parameterizing the query string, by using dynamic SQL executed via the system stored procedure sp_executesql . Here is an in-depth breakdown of how to use OPENQUERY with sp_executesql to include variable values in your remote query. 1. The Challenge: Parameterizing OPENQUERY The fundamental restriction of OPENQUERY is its syntax: SQL OPENQUERY ( linked_server , 'query' ) Neither linked_server nor the 'query' string can be a local SQL Server variable (e.g., @MyLinkedServerName or @MyQuery ). To pass a variable (like a specific CustomerID or a date range) into...
Read more

Executing Remote Queries Safely and Efficiently with sp_executesql

 gemini prompt: provide a blog post by a database professional on the best practice for using sp_executesql to query a remote sql server database Executing Remote Queries Safely and Efficiently with sp_executesql As database professionals, we often face the need to query data from a remote SQL Server instance . While options like simple four-part names (e.g., [Server].[Database].[Schema].[Table] ) or OPENQUERY exist, using sp_executesql in conjunction with linked servers provides a powerful, secure, and flexible approach, especially when dealing with dynamic SQL. Here's a breakdown of the best practices for leveraging sp_executesql for remote queries. 1. Why sp_executesql for Remote Queries? When executed locally, sp_executesql is primarily used to execute dynamic SQL safely by allowing you to parameterize the query string, which is the fundamental defense against SQL injection . When querying a linked server, you can use it to execute code directly on the remote server...
Read more