Find Count of Records matching specific filter in un-related table
I'm trying to write a custom function called "AddMarketDays(startDate; numOfDays)" which will add a specific number of Stock market trading days to a date.
I've already created a custom function called "AddBusinessDays(startDate, numOfDays)", which simply adds business days (M-F) to an intial date.
To implement AddMarketDays, I start by calling AddBusinessDays.
From there, I'd like to take the startDate and the output from AddBusinessDays, and make a query against a table called "MarketHolidays", returning the count of all days that fall between the startDate and the AddBusinessDays result. This function would be called GetMarketHolidays(startDate, endDate).
Finally, I will take that count, and call AddBusinessDays again, using the result from the previous call, to get the final end date.
So, something like this for 60 days out:
a = AddBusinessDays(startDate, numOfDays);
c = GetMarketHolidays(startDate, a);
d = AddBusinessDays(startDate, numOfDays + c);
The question I'm strugging with - how do I implement the function "GetMarketHolidays(startDate; endDate)", which should do something like the following (in TSQL format):
Select count(*) from MarketHolidays where HolidayDate >= startDate AND HolidayDate <= endDate)
And yes, I realize that the logic above has a flaw - after adding the holidays back in as business days, I would need to check AGAIN in case that pushed me over the next threshold of market holidays - but one step at a time! :)
Note also that this is the same implementation as a function that adds Business Days and takes into account normal holidays in a table - so if somebody can point me to an implementation of that, I could simply use that with a modified holiday table holding market holidays!