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

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

    BillRichardson

      Title

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

      Post

      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);

      ]

      d

      )

       

      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!

       

      Thanks,

       

      Bill