0 Replies Latest reply on Apr 3, 2010 10:10 AM by BillRichardson

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



      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!