Date in a time period

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).  
Example:
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.
tatianakAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
I've got some code that will do what you need ... except that you cannot access tables from within a scalar function (nor, btw, can you use EXEC() in a scalar function).  The general logic is below; still trying to work out how to get it into a scalar function:



DECLARE @code INT
DECLARE @date DATETIME
DECLARE @period VARCHAR(10) --'M'|'Monthly'|'Q'|'Quarterly'
DECLARE @dateNum TINYINT  --relative date # or 99 for 'Last'

-- change values below as needed for testing
SET @code = 1
SET @date = '2005-02-05'
SET @period = 'Q'
SET @dateNum = 99


-- code below should not need changed for testing

IF @period = 'Monthly'
      SET @period = 'M'
ELSE IF @period = 'Quarterly'
      SET @period = 'Q'
IF @period NOT IN ('M', 'Q')
BEGIN
      RAISERROR('Period must be M|Monthly|Q|Quarterly; "%s" not valid.', 16, 1, @period)
      --RETURN -1
END --IF

DECLARE @minDate DATETIME
DECLARE @maxDate DATETIME

-- force date to first day of month
SET @minDate = CONVERT(CHAR(6), @date, 112) + '01'
-- if period is quarterly, force date to first month of qtr
IF @period = 'Q'
      WHILE MONTH(@minDate) NOT IN (1, 4, 7, 10)
      BEGIN
            SET @minDate = DATEADD(MONTH, -1, @minDate)
      END --WHILE
SET @maxDate = DATEADD(MINUTE, -1, DATEADD(MONTH, CASE WHEN @period = 'M' THEN 1 ELSE 3 END, @minDate))

IF @dateNum = 99 --'Last'
      SELECT @date = MAX(date)
      FROM #dates
      WHERE date BETWEEN @minDate AND @maxDate
ELSE
      SELECT @date = date
      FROM #dates d1
      WHERE code = @code
      AND date BETWEEN @minDate AND @maxDate
      AND (SELECT COUNT(*)
            FROM #dates d2
            WHERE d2.code = @code
            AND d2.date BETWEEN @minDate AND @maxDate
            AND d2.date <= d1.date) = @dateNum

PRINT @date


0
 
Melih SARICAOwnerCommented:
try this


-- By Melih SARICA
-- period = 1 : monthly
--          3 : quarter
-- order  = 1 : 1st
--        = 2 : 2nd
--        = 99: last
create function fn_getValue(
      @date datetime,
      @period int,
      @order int)
return datetime
as


declare      @date datetime
declare      @period int
declare      @order int

declare @ret datetime
declare @per varchar(2)
declare @msql varchar(500)

set @date = '01.01.2005'
set @period = 1
set @Order = 1

if @period = 1
      set  @per= 'mm'
if @period = 3
      set  @per= 'qq'

if @order<99
      set @msql = 'Select top 1 datefield into #aa from (
                  Select top '+str(@order,1) +' datefield from table_A
                        where datepart('+@per+',datefield) =  datepart('+@per+','+convert(varchar(10),@date,104)+') ) drv order by datefield desc'

else
      set @msql = 'Select top 1 datefield into #aa from (
                  Select datefield from table_A
                        where datepart('+@per+',datefield) =  datepart('+@per+','+convert(varchar(10),@date,104)+') ) drv order by datefield desc'

select @ret =datefield from #aa

return @ret

did not tried it


 
0
 
tatianakAuthor Commented:
Is there a way to do it without using dynamic SQL?
0
 
Melih SARICAOwnerCommented:
u can do it with out dynamic SQL

extract dyn SQL  out and execute each statement

thats it
0
 
Scott PletcherSenior DBACommented:
D'OH:  here's the code to load the temp table, which should run before the other code:


DROP TABLE #dates
GO
CREATE TABLE #dates (
      code INT,
      date DATETIME
)
SET NOCOUNT ON
INSERT INTO #dates VALUES(1, '2005-01-03')
INSERT INTO #dates VALUES(1, '2005-01-10')
INSERT INTO #dates VALUES(1, '2005-01-18')
INSERT INTO #dates VALUES(1, '2005-01-24')
INSERT INTO #dates VALUES(1, '2005-01-31')
INSERT INTO #dates VALUES(1, '2005-02-07')
INSERT INTO #dates VALUES(1, '2005-02-28')
INSERT INTO #dates VALUES(1, '2005-03-07')
INSERT INTO #dates VALUES(1, '2005-03-14')
INSERT INTO #dates VALUES(1, '2005-03-21')
INSERT INTO #dates VALUES(1, '2005-03-28')
SET NOCOUNT OFF
GO



0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.