Time-Sensitive Testing and Debugging

Add default parameter @OVERRIDE_NOW SMALLDATETIME = NULL. Specify a value when testing to set the current time; e.g.

DECLARE

  @TODAY DATE = GETDATE()

, @NOW SMALLDATETIME;

-- By default set @NOW to the current date and time. Specify a value for

-- @OVERRIDE_NOW for testing purposes to show/hide the buckets.

SET @NOW = IIF(@OVERRIDE_NOW IS NULL, GETDATE(), @OVERRIDE_NOW);


-- setup the datetime values for our report buckets

DECLARE 

@STATS_10AM SMALLDATETIME = CONVERT(DATETIME, CONCAT(@STATS_DATE, ' 10:00'))

, @STATS_1PM SMALLDATETIME = CONVERT(DATETIME, CONCAT(@STATS_DATE, ' 13:00'))

, @STATS_4PM SMALLDATETIME = CONVERT(DATETIME, CONCAT(@STATS_DATE, ' 16:00'));


-- Set the current time for each branch based on their time zone and the EST
SELECT
[BranchNumber]
, [Time_Zone]
, CASE
WHEN [Time_Zone] = N'Central' THEN DATEADD(hour, -1, @NOW)
WHEN [Time_Zone] = N'Mountain' THEN DATEADD(hour, -2, @NOW)
WHEN [Time_Zone] = N'Pacific' THEN DATEADD(hour, -3, @NOW)
ELSE @NOW
END AS [BranchCurrentTime]
FROM ...


-- Set indicators in the result set so we know which buckets are getting displayed.
, IIF([BranchCurrentTime] >= @STATS_10AM, 1, 0) AS [SHOW_10AM_BUCKET]
, IIF([BranchCurrentTime] >= @STATS_1PM,  1, 0) AS [SHOW_1PM_BUCKET]
, IIF([BranchCurrentTime] >= @STATS_4PM,  1, 0) AS [SHOW_4PM_BUCKET]


-- Determine which buckets to show on the report; set value to NULL if the bucket should not be rendered
IIF(COALESCE([SHOW_10AM_BUCKET], 1) = 0, NULL, g.LoanSet10) LoanSet10, 
IIF(COALESCE([SHOW_1PM_BUCKET], 1)  = 0, NULL, g.LoanSet13) LoanSet13, 
IIF(COALESCE([SHOW_4PM_BUCKET], 1)  = 0, NULL, g.LoanSet16) LoanSet16,



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