Date in a time period
Posted on 2005-05-06
Hi. I have a table with two columns: code and date. I'm trying to create a sql udf that I can pass a date and a code to and it will return a record that is the 1st/2nd/etc... or last in a given time period (month, quarter). The inputs into udf would be code, date, time period, what date I want to extract (1st/2nd/last).
Dates in the table - 2005-01-03, 2005-01-10, 2005-01-18, 2005-01-24, 2005-01-31, 2005-02-07, 2005-02-28 ,2005-03-07, 2005-03-14, 2005-03-21, 2005-03-28
If I input 02/05/05, Quarterly, last - the output would be 03/28/05
If I input 02/05/05, Monthly, last - the output would be 02/28/05
If I input 02/05/05, Quarterly, 1st - the output would be 01/03/05
If I input 02/05/05, Quarterly, 2nd - the output would be 01/10/05
If I input 02/05/05, Monthly, 1st - the output would be 02/07/05.
I'm open to the possibility of two different functions: one for Monthly and one for Quarterly. Thanks for all your help.