AnsweredAssumed Answered

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

Question asked by BillRichardson on Apr 3, 2010

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

 

 

Outcomes