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

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
Asked:
tatianak
  • 2
  • 2
1 Solution
 
Melih SARICACommented:
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
 
Scott PletcherSenior 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 SARICACommented:
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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