• Status: Solved
• Priority: Medium
• Security: Public
• Views: 308

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.
0
tatianak
• 2
• 2
1 Solution

Commented:
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

Author Commented:
Is there a way to do it without using dynamic SQL?
0

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

Commented:
u can do it with out dynamic SQL

extract dyn SQL  out and execute each statement

thats it
0

Senior 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

Featured Post

• 2
• 2
Tackle projects and never again get stuck behind a technical roadblock.