Calculating the difference between two dates

Question

I have a report that calculates multiple date differences (in business days, not DATEDIFF) for a variety of business reasons that are far too dull to get into.

Basically the query (right now) looks something like

SELECT -- some kind of information
       DATEDIFF(dd, DateOne, DateTwo) AS d1_d2_diff,
       DATEDIFF(dd, DateOne, DateThree) AS d1_d3_diff,
       DATEDIFF(dd, DateTwo, DateThree) AS d2_d3_diff,
       DATEDIFF(dd, DateTwo, DateFour) AS d2_d4_diff
  FROM some_table;

I could change this calculation to use a scalar function, but I don't want the scalar function to be executed 4 times for every row in the result set.

I have a Calendar table in the database:

CREATE TABLE Calendar (
   Date DATETIME NOT NULL,
   IsWeekday BIT,
   IsHoliday BIT
);

Would a table-valued function and CROSS APPLY be a good choice here? If so, how would I go about writing such a thing? Or is a scalar function my best bet?

Important Note All date values in our database have been stripped of time so it is safe to ignore any code that would reset days to midnight.

Answer 1

Realistically I think you want to go with a scalar function for this. At first glance you are going to need to do a few calculations. Then I thought about it more, and you can actually do this quite simply with a two step process.

1.) Roll your date values back to midnight of the respective days, that way you can easily figure it out. Due to extra information provided, this is not needed!

2.) Execute a query to find out how many week days, that are not holidays exist between the day values

SELECT ISNULL(COUNT(*), 0)
FROM Calendar
WHERE [DATE] > DateOne 
    AND [DATE] < DateTwo
    AND IsWeekDay = 1
    AND IsHoliday = 0

Overall I think that the most efficient way is to just do this as a Scalar Function, I'm sure there might be other ways, but this way is straightforward, and as long as you have an index on the Calendar table it shouldn't be too bad performance wise.

note on cross apply

Doing a bit of looking, this could also be done via cross apply, but really in the end it does the same thing, so I think that the Scalar function is a better solution as it is easier to understand, and is easily repeatable.