{"id":105151,"date":"2025-02-12T03:15:58","date_gmt":"2025-02-12T03:15:58","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=105151"},"modified":"2025-02-11T02:32:18","modified_gmt":"2025-02-11T02:32:18","slug":"exploring-scalar-solutions-to-complex-data-math","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/exploring-scalar-solutions-to-complex-data-math\/","title":{"rendered":"Exploring Scalar Solutions to Complex Data Math"},"content":{"rendered":"\n

There are many functions and tools available to database professionals that can solve data math challenges, regardless of complexity. A recent problem came across my desk that called for not only a valid solution, but one that performs as optimally as possible when included in a scalar User-Defined-Function (UDF).<\/p>\n\n\n\n

This left me asking: How often these problems impact us and what general solutions can we find that may solve them more easily for us?<\/p>\n\n\n\n

The impetus for this was a simple question, \u201cHow many instances of days occur between two dates?\u201d. For example: \u201cHow many Mondays were there between 1\/1\/2024 and 6\/17\/2024?\u201d Or: \u201cHow many Tuesdays and Thursdays are there in total from 9\/2\/2022 and 9\/1\/2025?\u201d<\/p>\n\n\n\n

This sounds super-simple at first, but as I dove in for a universal solution, I found myself coming back to either iteration, calendar tables, or exceptionally long SQL that replaced iteration with copies of the same code over-and-over. There are no built-in functions in SQL Server that do what I wanted.<\/p>\n\n\n\n

Notes on DATEFIRST and SQL Server Defaults<\/h2>\n\n\n\n

SQL Server by default assigns a number to each day of the week that can be used in date math and identification. The defaults are as follows:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

<\/p>\n\n\n\n

This means that the default in SQL Server is for Sunday to be the first day of the week. Any date math that identifies the day of the week will return \u201c1\u201d for the day corresponding to Sunday, like this:<\/p>\n\n\n\n

SELECT DATEPART(DW, '12\/31\/2023') AS DayOfWeekResult;<\/pre>\n\n\n\n
12\/31\/2023 was a Sunday, so the results are as follows:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Most developers will go along with this convention as it is the default and easy enough to accommodate with consistent code. The default can be changed at the connection level, using SET DATEFIRST<\/code>:<\/p>\n\n\n\n
SET DATEFIRST 1;<\/pre>\n\n\n\n
For my session, the first day of the week is now Monday, and the day of the week for 12\/31\/2023 is now going to be a different value:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Sunday is defined as the last day of the week and therefore is indicated with a day-of-week of 7. Keep in mind that this setting only applies to a given session and all other processes in SQL Server will continue to return results as if SET DATEFIRST<\/code> were never invoked.<\/p>\n\n\n\n
Maintaining the defaults is far easier than changing the first day of the week in SQL Server. Regardless of defaults, this article will maintain Sunday as the first day of the week and not change this as part of the problem-solving done here. Before continuing, let\u2019s return this setting to the default, to prevent any potential confusion later on:<\/p>\n\n\n\n
SET DATEFIRST 7;<\/pre>\n\n\n\n
Note that the code in this article assumes that this default is enabled. If your SQL Server has another default, or if defaults are adjusted on a session-by-session basis, then the days of the week used here will also adjust accordingly.<\/em><\/p>\n\n\n\n
Solutions That Do Not Perform Well Enough<\/h2>\n\n\n\n
In this section I will cover some of the solutions that work, often seemly fast enough, but for dealing with lot of rows, they start slowing down the output to a level that is not desirable.<\/p>\n\n\n\n
Simple Iteration<\/h3>\n\n\n\n
Iteration is the easiest solution to the problem. Consider the first example question: \u201cHow many Mondays were there between 1\/1\/2024 and 6\/17\/2024?\u201d We can set a variable equal to the start date and iterate through every date between it and the end date, counting instances of Mondays along the way, like this:<\/p>\n\n\n\n
DECLARE @StartDate DATE = '1\/1\/2024';\nDECLARE @EndDate DATE = '6\/17\/2024';\nDECLARE @CurrentDate DATE = @StartDate;\nDECLARE @Result INT = 0;\nWHILE @CurrentDate <= @EndDate\nBEGIN\n\tIF DATEPART(DW, @CurrentDate) = 2\n\tBEGIN\n\t\tSELECT @Result = @Result + 1;\n\tEND\n\tSELECT @CurrentDate = DATEADD(DAY, 1, @CurrentDate);\nEND\nSELECT @Result;<\/pre>\n\n\n\n
Nothing fancy here \u2013 we get the result by iterating 168 times and done. The wider the date range, the more effort is needed to solve for the result. If joined into a table and directly applied to a column, then the effort would increase based both on row counts and date ranges.<\/p>\n\n\n\n
Of course, when you execute this directly, any slowness is imperceptible. But when you try to use this function with large datasets, the iterations become more and more noticeable. In this article I won\u2019t delve into the testing of the functions, but it is important to consider the amount of data your code may be used with and test with as much more than that that you can.<\/p>\n\n\n\n
Tactical Iteration<\/h3>\n\n\n\n
An alternative that is less painful, but also quite methodical is to iterate by weeks prior to iterating by days. We know that for each 7-day span, each day of the week will occur once.<\/p>\n\n\n\n
Therefore, iteration can be accomplished via incrementing by weeks, until within seven days of the @EndDate. This solution can be implemented using the following T-SQL:<\/p>\n\n\n\n
DECLARE @StartDate DATE = '1\/1\/2024';\nDECLARE @EndDate DATE = '6\/17\/2024';\nDECLARE @CurrentDate DATE = @StartDate;\nDECLARE @Result INT = 0;\n--get the number of full weeks past\nWHILE DATEADD(DAY, 7, @CurrentDate) <= @EndDate\nBEGIN\n    SELECT @Result = @Result + 1;\n    SELECT @CurrentDate = DATEADD(DAY, 7, @CurrentDate);\nEND\n--then see if there is a Monday in the remainder of the week.\nWHILE @CurrentDate <= @EndDate\nBEGIN\n    IF DATEPART(DW, @CurrentDate) = 2\n    BEGIN\n        SELECT @Result = @Result + 1;\n    END\n    SELECT @CurrentDate = DATEADD(DAY, 1, @CurrentDate);\nEND\nSELECT @Result;<\/pre>\n\n\n\n
This solution has two WHILE<\/code> blocks. The first one loops by increments of seven days, adding one to the result each tie. The second WHILE<\/code> loop performs the same logic as the previous solution, iterating the remainder of days until reaching @EndDate<\/code>. While this iterates less, it\u2019s more complex and still relies on WHILE<\/code> loops that have to iterate some number of times each to return a result.<\/p>\n\n\n\n
It is useful to be reminded that any solution built here is likely to be encapsulated into a scalar function. Once converted to a function, it can be embedded into any part of a query, including aggregation, filtering, and ordering. Therefore, any performance challenges identified, even if minor, will be magnified when applied to any part of a query against data of potentially any size!<\/p>\n\n\n\n
The following code is the natural extension of what was done here:<\/p>\n\n\n\n
CREATE FUNCTION dbo.ReturnDaysBetweenDates_Iteration\n    (@StartDate DATE, @EndDate DATE)\nRETURNS INT\nAS\nBEGIN\n    DECLARE @CurrentDate DATE = @StartDate;\n    DECLARE @Result INT = 0;\n    WHILE @CurrentDate <= @EndDate\n    BEGIN\n        IF DATEPART(DW, @CurrentDate) = 2\n        BEGIN\n            SELECT @Result = @Result + 1;\n        END\n        SELECT @CurrentDate = DATEADD(DAY, 1, @CurrentDate);\n    END\n    RETURN @Result;\nEND<\/pre>\n\n\n\n
Once wrapped into a function, it may be reused anywhere, including inside additional functions, stored procedures, or views.<\/p>\n\n\n\n
While reusing optimal object can result in great savings, reusing suboptimal code can causes inefficiencies being multiplied exponentially, which for the sake of performance can be absolutely crushing!<\/p>\n\n\n\n
For this reason, maximizing efficiency is quite important here, so it is important to test, and in some cases, looking for even more efficient, if not as straightforward solutions that will work fast enough when used in any situation! In this case, needing to iterate was deemed to be a suboptimal solution, so I continued at it looking for a better solution.<\/p>\n\n\n\n
Solving it All-at-Once<\/h2>\n\n\n\n
The key to solving this date problem lies in the fact that the effort needed to compute the answer does not need to be linear to the time frame analyzed.<\/p>\n\n\n\n
When solving this sort of issues, it can be a good idea to look at the problem from a bit higher level. While you should always be careful to not extend the scope too far, sometimes a general solution can also be the better solution.<\/p>\n\n\n\n
In this case, it was determined that it would also be ideal to expand the solution to allow for calculating day-of-the-week counts for any individual day or combination of days. Weekdays are a common data request, but different fields will require other combinations of days based on differing business models. For the demonstration here, the example of solving for Monday will be completed first, and then a more complex scenario involving multiple days.<\/p>\n\n\n\n
Calculating days of the week over a one-hundred-year span should not require more effort than over a one-month span. A visualization can help in seeing a better solution:<\/p>\n\n\n\n
\"A<\/figure>\n\n\n\n
<\/p>\n\n\n\n
In this example, the start date for the date range is on a Tuesday and the @EndDate<\/code> is on a Friday, 2 weeks later. To calculate the number of Mondays in this time frame requires 2 bits of math:<\/p>\n\n\n
\n
    \n
  1. Count the number of 7-day spans in the range (weeks)<\/li>\n\n\n\n
  2. Count the remaining days.<\/li>\n<\/ol>\n<\/div>\n\n\n
    This was accomplished earlier via iteration, but by visualizing the problem, it becomes clear that there are 2 calculations that need to be completed. The first is trivial: take the number of days in the date range and divide by seven. The result provides the first part of the solution:<\/p>\n\n\n\n
    \"A<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The remaining challenge is to count the extra days at the end of the date range, which will be a number from 0-6. Visually, it is easy to see how many days remain and which days of the week they are, but how can this programmatically be calculated? The number of days and which days of the week are represented are both required to solve this problem, without any human\/visual inspection.<\/p>\n\n\n\n
    The first and simplest scenario is one where the number of days in the date range is an even multiple of seven:<\/p>\n\n\n\n
    \"A<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    In this scenario, the number of Mondays will always be the count of days in the date range divided by seven:<\/p>\n\n\n\n
    SELECT (DATEDIFF(DAY, @StartDate, @EndDate) + 1) \/ 7;<\/pre>\n\n\n\n
    This can also be simplified to:<\/p>\n\n\n\n
    SELECT DATEDIFF(WEEK, @StartDate, @EndDate) \/ 7;<\/pre>\n\n\n\n
    The other two scenarios are:<\/p>\n\n\n
    \n
      \n
    1. The starting day of the week is less than or equal to the ending day of the week.<\/li>\n\n\n\n
    2. The starting day of the week is greater than the ending day of the week.<\/li>\n<\/ol>\n<\/div>\n\n\n
      For each of these, the count of full weeks is computed as it was above and 1 is added to it if the remaining days happen to include a Monday.<\/p>\n\n\n\n
      Scenario #1 looks like this:<\/p>\n\n\n\n
      \"A<\/figure>\n\n\n\n
      <\/p>\n\n\n\n
      If the start day (2) is less than or equal to the end day (5), which it is, then whether a Monday exists in that range depends solely on if the start day is less than or equal to 1 or not. Since it is, we can add one and the total number of Mondays is 3, and given by the following T-SQL:<\/p>\n\n\n\n
      SELECT @MondayCount = DATEDIFF(WEEK, @StartDate, @EndDate) \/ 7 +\nCASE WHEN @DayOfWeekStart > 1 THEN 0 ELSE 1 END;<\/pre>\n\n\n\n
      Alternatively, if the start day (2) is greater than the end day (5), then the extra Monday would not have been added on, and the count would have been two, rather than three.<\/p>\n\n\n\n
      There are two more problems to solve here:<\/p>\n\n\n
      \n