AnsweredAssumed Answered

Find Count of Records matching specific filter in un-related table

Question asked by BillRichardson on Apr 3, 2010


Find Count of Records matching specific filter in un-related table


Hi all,


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:


Let (


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!