A DateRange Table Valued function

  • Jonathan AC Roberts

    SSCoach

    Points: 16992

    Comments posted to this topic are about the item A DateRange Table Valued function

  • scdecade

    Mr or Mrs. 500

    Points: 531

    Danke schön!  Nicely done -- it's in my toolbox now.

    We have a project coming up where I'll have the chance to alter or replace the following proc (which runs in prod now):

    if object_id('dbo.api_delivery_dates_view_get') is not null
    drop proc dbo.api_delivery_dates_view_get;
    go
    create proc dbo.api_delivery_dates_view_get
    @qtr_id int
    as
    declare
    @start_dt date,
    @frequency int,
    @end_dt date;

    select
    @start_dt=delivery_dt,
    @frequency=freq_days
    from
    calendars
    where
    qtr_id=@qtr_id;

    select @end_dt=dateadd(qq, datediff(qq, 0, @start_dt)+1, -1);

    with
    calendar_range_cte(delivery_dt) as (
    select @start_dt
    union all
    select dateadd(d, @frequency, delivery_dt)
    from calendar_range_cte
    where delivery_dt < @end_dt),
    calendar_reschedules_cte(old_delivery_dt, new_delivery_dt) as (
    select old_delivery_dt, new_delivery_dt
    from calendar_reschedules
    where qtr_id=@qtr_id)
    select
    coalesce(crec.new_delivery_dt, crc.delivery_dt) delivery_dt
    from
    calendar_range_cte crc
    left join
    calendar_reschedules_cte crec on crc.delivery_dt=crec.old_delivery_dt
    where
    crc.delivery_dt < @end_dt
    except
    select
    cancel_delivery_dt
    from
    calendar_cancellations
    where
    qtr_id=@qtr_id
    option (maxrecursion 0)
    for json path;
    go

    Your function slides right in like:

    drop proc if exists api_delivery_dates_view_get_test;
    go
    create proc api_delivery_dates_view_get_test
    @qtr_id int
    as
    declare
    @start_dt date,
    @frequency int,
    @end_dt date;

    select
    @start_dt=delivery_dt,
    @frequency=freq_days
    from
    calendars
    where
    qtr_id=@qtr_id;

    select @end_dt=dateadd(qq, datediff(qq, 0, @start_dt)+1, -1);

    with
    calendar_range_cte(delivery_dt) as (
    select [value] as delivery_dt from dbo.daterange(@start_dt, @end_dt, 'dd', @frequency)),
    calendar_reschedules_cte(old_delivery_dt, new_delivery_dt) as (
    select old_delivery_dt, new_delivery_dt
    from calendar_reschedules
    where qtr_id=@qtr_id)
    select
    coalesce(crec.new_delivery_dt, crc.delivery_dt) delivery_dt
    from
    calendar_range_cte crc
    left join
    calendar_reschedules_cte crec on crc.delivery_dt=crec.old_delivery_dt
    where
    crc.delivery_dt < @end_dt
    except
    select
    cancel_delivery_dt
    from
    calendar_cancellations
    where
    qtr_id=@qtr_id
    for json path;

    No need to set max recursions anymore.  No need for the where clause with inequality comparison on date.

    The only change to the function I made was to make it d.i.e. (drop if exists) and make it all lower case.  Since Sql 2016 most objects can d.i.e.

    print 'create function [dbo].[daterange]' 
    drop function if exists dbo.daterange;
    go
    /*-- **********************************************************************
    -- function: daterange
    -- returns a table of datetime values based on the parameters
    -- parameters:
    -- @startdate :start date of the series
    -- @enddate :end date of the series
    -- @datepart :the time unit for @interval
    -- ns : nanoseconds
    -- mcs : microseconds
    -- ms : milliseconds
    -- ss : seconds
    -- mi : minutes
    -- hh : hours
    -- dd : days
    -- ww : weeks
    -- mm : months
    -- qq : quarters
    -- yy : years
    -- @interval :the number of dateparts between each value returned
    --
    -- sample calls:
    -- select * from [dbo].[daterange]('2011-01-01 12:24:35', '2011-02-01 12:24:35', 'ss', 2)
    -- select count(*) from [dbo].[daterange]('2018-01-01 00:00:00', '2018-01-25 20:31:23.646', 'ms', default)
    -- select * from [dbo].[daterange]('2011-01-01', '2012-02-03', default, default)
    -- select * from [dbo].[daterange]('2012-02-03', '2011-01-01', 'dd', 7)
    -- select datediff(ns,'2018-01-01 00:00:00.000', value),value,* from [dbo].[daterange]('2018-01-01 00:00:00.000', '2018-01-01 00:00:00.00001', 'ns', 100)
    -- **********************************************************************/
    create function dbo.daterange
    (
    @startdate datetime2,
    @enddate datetime2,
    @datepart nvarchar(3)='dd',
    @interval int=1
    )
    returns table as return
    with
    a(a) as (
    select 0 from (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(a)),
    b(rownum) as (
    select top(abs(case @datepart
    when 'ns' then datediff(ns, @enddate, @startdate)/@interval
    when 'mcs' then datediff(mcs,@enddate, @startdate)/@interval
    when 'ms' then datediff(ms, @enddate, @startdate)/@interval
    when 'ss' then datediff(ss, @enddate, @startdate)/@interval
    when 'mi' then datediff(mi, @enddate, @startdate)/@interval
    when 'hh' then datediff(hh, @enddate, @startdate)/@interval
    when 'dd' then datediff(dd, @enddate, @startdate)/@interval
    when 'ww' then datediff(ww, @enddate, @startdate)/@interval
    when 'mm' then datediff(mm, @enddate, @startdate)/@interval
    when 'qq' then datediff(qq, @enddate, @startdate)/@interval
    when 'yy' then datediff(yy, @enddate, @startdate)/@interval
    else datediff(dd, iif(@startdate < @enddate, @startdate, @enddate), iif(@startdate < @enddate, @enddate, @startdate))/@interval
    end) + 1)
    row_number() over (order by (select null)) - 1
    from a a, a b, a c, a d, a e, a f, a g, a h) -- a maximum of 16^8 (or 2^32) rows could be returned from this inline tally
    select case @datepart
    when 'ns' then dateadd(ns, t.addamount, @startdate)
    when 'mcs' then dateadd(mcs,t.addamount, @startdate)
    when 'ms' then dateadd(ms, t.addamount, @startdate)
    when 'ss' then dateadd(ss, t.addamount, @startdate)
    when 'mi' then dateadd(mi, t.addamount, @startdate)
    when 'hh' then dateadd(hh, t.addamount, @startdate)
    when 'dd' then dateadd(dd, t.addamount, @startdate)
    when 'ww' then dateadd(ww, t.addamount, @startdate)
    when 'mm' then dateadd(mm, t.addamount, @startdate)
    when 'qq' then dateadd(qq, t.addamount, @startdate)
    when 'yy' then dateadd(yy, t.addamount, @startdate)
    else dateadd(dd, t.addamount, @startdate)
    end [value]
    from b
    cross apply(values (iif(@startdate<@enddate, @interval*rownum, @interval*-rownum))) t(addamount)
    go

    Cheers!

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Jonathan AC Roberts

    SSCoach

    Points: 16992

    Hi scdecade,

    Thanks for the feedback, I'm glad you've found a use for the function.

    I didn't use "drop if exists" as this is a feature that was only introduced in SQL Server 2016, so it wouldn't work on earlier versions. Also, I find it good to alter a stored procedure or function instead of drop and create as the security settings for users won't be lost.

    I think you can squeeze your entire SP into one SQL query:

    SELECT COALESCE(crec.new_delivery_dt, crc.delivery_dt) delivery_dt
    FROM (SELECT TOP(1) c.delivery_dt start_dt,
    dateadd(qq, datediff(qq, 0, c.delivery_dt) + 1, -1) end_dt,
    freq_days frequency
    FROM calendars c
    WHERE c.qtr_id = @qtr_id) AS parms
    CROSS APPLY (SELECT [value] delivery_dt
    FROM dbo.daterange(parms.start_dt, parms.end_dt, 'dd', parms.frequency) crc
    WHERE crc.[value] < parms.end_dt) crc
    LEFT JOIN calendar_reschedules crec
    ON crec.old_delivery_dt = crc.delivery_dt
    AND crec.qtr_id = @qtr_id
    WHERE NOT EXISTS (SELECT *
    FROM calendar_cancellations cc
    WHERE cc.qtr_id = @qtr_id
    AND cc.cancel_delivery_dt = COALESCE(crec.new_delivery_dt, crc.delivery_dt))
    FOR JSON PATH;

     

  • Jeff Moden

    SSC Guru

    Points: 995102

    Nice function, Jonathan.  Well done!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jonathan AC Roberts

    SSCoach

    Points: 16992

    Jeff Moden wrote:

    Nice function, Jonathan.  Well done!

    Thank you Jeff, that's a great compliment coming from someone as experienced as you.

  • scdecade

    Mr or Mrs. 500

    Points: 531

    Your proc produced the correct output.  I'm still trying to get my head around cross apply.  It's kind of magical.  The last part, where not exists, I was really sure that wasn't going to work but it did!

    A couple of things:

    1. qtr_id is primary key of the 'calendars' table, so top(1) was not necessary (no way you could've known)
    2. "WHERE crc.[value] < parms.end_dt" was not necessary at all so I deleted it.  Your function obviated that necessity.  I wrote that but then I pasted it in with the code.  Sorry my butterfingers!
    3. The dates in our tables (calendar, calendar_reschedules, and calendar_cancellations) are all 'date' column types.  The output of the function was datetime so I cast it back to date.

    The proc looks like this now:

    drop proc if exists api_delivery_dates_view_get_test;
    go
    create proc api_delivery_dates_view_get_test
    @qtr_id int
    as
    set nocount on;

    with
    parms_cte(start_dt, end_dt, frequency) as (
    select
    delivery_dt start_dt,
    dateadd(qq, datediff(qq, 0, delivery_dt) + 1, -1) end_dt,
    freq_days frequency
    from
    calendars
    where
    qtr_id = @qtr_id),
    cr_cte(old_delivery_dt, new_delivery_dt) as (
    select
    old_delivery_dt,
    new_delivery_dt
    from
    calendar_reschedules
    where
    qtr_id=@qtr_id),
    cc_cte(delivery_dt) as (
    select
    cancel_delivery_dt
    from
    calendar_cancellations
    where
    qtr_id=@qtr_id)
    select
    coalesce(cc.new_delivery_dt, cal.delivery_dt) delivery_dt
    from
    parms_cte pc
    cross apply
    (select cast([value] as date) delivery_dt from dbo.daterange(pc.start_dt, pc.end_dt, 'dd', pc.frequency)) cal
    left join
    cr_cte cc on cc.old_delivery_dt = cal.delivery_dt
    where
    not exists (select * from cc_cte where delivery_dt = coalesce(cc.new_delivery_dt, cal.delivery_dt))
    for json path;
    go

    --exec api_delivery_dates_view_get_test 4

    As far as making objects d.i.e remember when this happened?

    https://siliconangle.com/2019/05/19/salesforce-recovers-outage-caused-faulty-database-script/

    This put the fear of the almighty into management here.  I was told to make sure this is as close to impossible as possible.  So we have a process that happens before users are assigned rights to objects/procedures.  I wonder if the dba survived with job intact.  Does anybody know?

    Thanks again Jonathan!

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Jonathan AC Roberts

    SSCoach

    Points: 16992

    scdecade wrote:

    "WHERE crc.[value] < parms.end_dt" was not necessary at all so I deleted it.  Your function obviated that necessity.  I wrote that but then I pasted it in with the code

    I'm not sure that you should remove that as the original code had it in.

    These two queries produce different results, @EndDate is not included if you add the where clause.

    declare @StartDate datetime2 = '20190821',
    @EndDate datetime2 = '20190828';

    select cast([value] as date) delivery_dt
    from dbo.DateRange(@StartDate,@EndDate,'dd',1);

    select cast([value] as date) delivery_dt
    from dbo.DateRange(@StartDate,@EndDate,'dd',1)
    where [value] < @EndDate;

    An alternative way of doing this without putting it in the where clause would be to subtract 1 day from @EndDate within the DateRange parameters:

    select cast([value] as date) delivery_dt 
    from dbo.DateRange(@StartDate,DATEADD(dd,-1,@EndDate),'dd',1)
  • rVadim

    Hall of Fame

    Points: 3922

    Thanks Jonathan, nice function. Which versions of SQL Server it will work with? It doesn't seem to work on 2008R2. I replaced IIF with CASE but now getting this:

    Msg 535, Level 16, State 0, Line 33
    The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

    --Vadim R.

  • Jonathan AC Roberts

    SSCoach

    Points: 16992

    rVadim wrote:

    Thanks Jonathan, nice function. Which versions of SQL Server it will work with? It doesn't seem to work on 2008R2. I replaced IIF with CASE but now getting this:

    Msg 535, Level 16, State 0, Line 33
    The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

    As it's written with IIF it would only work with SS 2012 or higher.

    datetime2 was introduced in SS 2008, so if you've changed the IIF to CASE I think it should work with 2008R2.

    From the error message, it looks like you have called it with a date range that would result in the datediff function used within the daterange function returning a value outside the range of (-2,147,483,648 to +2,147,483,647).

    What values were you using to call the DateRange function?

  • rVadim

    Hall of Fame

    Points: 3922

    Sample calls from function comment. The last one works but 4 prior produce that error.

    --Vadim R.

  • Jonathan AC Roberts

    SSCoach

    Points: 16992

    rVadim wrote:

    Sample calls from function comment. The last one works but 4 prior produce that error.

    I don't have access to a 2008 server, but maybe you could try it with the Start/End date parameters closer together to see if you can get it to return some results?

  • scdecade

    Mr or Mrs. 500

    Points: 531

    Jonathan AC Roberts wrote:

    scdecade wrote:

    "WHERE crc.[value] < parms.end_dt" was not necessary at all so I deleted it.  Your function obviated that necessity.  I wrote that but then I pasted it in with the code

    I'm not sure that you should remove that as the original code had it in.

    These two queries produce different results, @EndDate is not included if you add the where clause.

    declare @StartDate datetime2 = '20190821',
    @EndDate datetime2 = '20190828';

    select cast([value] as date) delivery_dt
    from dbo.DateRange(@StartDate,@EndDate,'dd',1);

    select cast([value] as date) delivery_dt
    from dbo.DateRange(@StartDate,@EndDate,'dd',1)
    where [value] < @EndDate;

    An alternative way of doing this without putting it in the where clause would be to subtract 1 day from @EndDate within the DateRange parameters:

    Jonathan AC Roberts wrote:

    scdecade wrote:

    "WHERE crc.[value] < parms.end_dt" was not necessary at all so I deleted it.  Your function obviated that necessity.  I wrote that but then I pasted it in with the code

    I'm not sure that you should remove that as the original code had it in.

    These two queries produce different results, @EndDate is not included if you add the where clause.

    declare @StartDate datetime2 = '20190821',
    @EndDate datetime2 = '20190828';

    select cast([value] as date) delivery_dt
    from dbo.DateRange(@StartDate,@EndDate,'dd',1);

    select cast([value] as date) delivery_dt
    from dbo.DateRange(@StartDate,@EndDate,'dd',1)
    where [value] < @EndDate;

    An alternative way of doing this without putting it in the where clause would be to subtract 1 day from @EndDate within the DateRange parameters:

    select cast([value] as date) delivery_dt 
    from dbo.DateRange(@StartDate,DATEADD(dd,-1,@EndDate),'dd',1)

    select cast([value] as date) delivery_dtfrom dbo.daterange(@startdate,dateadd(dd,-1,@enddate),'dd',1)

    The last one (but in lower case)

    • This reply was modified 3 months, 2 weeks ago by  scdecade.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • scdecade

    Mr or Mrs. 500

    Points: 531

    Have a look at this code:

    declare
    @start_dt date,
    @frequency int,
    @end_dt date;

    select
    @start_dt='2019-09-01',
    @frequency=14;

    /* the end_dt is always set to the last day of the quarter (for any arbitrary start_dt)*/
    select @end_dt=dateadd(qq, datediff(qq, 0, @start_dt)+1, -1);

    select @end_dt end_dt;
    -- returns --
    /*
    end_dt
    2019-09-30
    */

    /* create recursive cte with strict date inequality */
    with
    calendar_range_cte(delivery_dt) as (
    select @start_dt
    union all
    select dateadd(d, @frequency, delivery_dt)
    from calendar_range_cte
    where delivery_dt < @end_dt)
    select * from calendar_range_cte;

    -- returns --
    /*
    delivery_dt
    2019-09-01
    2019-09-15
    2019-09-29
    2019-10-13
    */

    /* create recursive cte with strict date inequality AND strict date inequality in accessor */
    with
    calendar_range_cte(delivery_dt) as (
    select @start_dt
    union all
    select dateadd(d, @frequency, delivery_dt)
    from calendar_range_cte
    where delivery_dt < @end_dt)
    select * from calendar_range_cte
    where
    delivery_dt <= @end_dt;

    -- returns --
    /*
    delivery_dt
    2019-09-01
    2019-09-15
    2019-09-29
    */

    It returns 3 results:

    1. the end date of the quarter
    2. A cte with a strict inequality on end date within the definition of the cte
    3. A cte with a strict inequality in the cte and within the select accessor.

    The accessor of the cte always delivers the next value in the recursion.  #2 returns '2019-10-13' which is the next value after the inequality inside the cte.  It is Sql Server's behavior obviously but I guess I never understood why this was the case.  When I saw your code it seemed to click I could ditch the second where clause.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Jonathan AC Roberts

    SSCoach

    Points: 16992

    scdecade wrote:

    The accessor of the cte always delivers the next value in the recursion.  #2 returns '2019-10-13' which is the next value after the inequality inside the cte.  It is Sql Server's behavior obviously but I guess I never understood why this was the case.  When I saw your code it seemed to click I could ditch the second where clause.

    Yes, I see now, I didn't look at your original code too carefully

  • Aaron N. Cutshall

    SSCrazy Eights

    Points: 8738

    Personally, I would avoid recursive CTEs whenever possible. By using the Tally, it replaces the hidden loop that the recursive CTE performs with an efficient set-based manner. Take a look at Jeff Moden's articles on recursive CTEs and their hidden costs.

    You also mentioned "I'm still trying to get my head around cross apply." Think of a cross apply as an inner join where the join criteria consists of "ON 1=1". Essentially, the records get applied across all records in the rest of the join. It's a pretty neat trick to use, but it must be used properly otherwise you could wind up with way more records than you expected!

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply