August 20, 2019 at 9:22 pm
Comments posted to this topic are about the item A DateRange Table Valued function
August 20, 2019 at 11:30 pm
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_idint
as
declare
@start_dtdate,
@frequencyint,
@end_dtdate;
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;
goYour 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_idint
as
declare
@start_dtdate,
@frequencyint,
@end_dtdate;
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
August 21, 2019 at 11:45 am
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;
August 21, 2019 at 11:50 am
Nice function, Jonathan. Well done!
--Jeff Moden
Change is inevitable... Change for the better is not.
August 21, 2019 at 1:48 pm
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:
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_idint
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
August 21, 2019 at 3:39 pm
"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)
August 21, 2019 at 3:48 pm
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.
August 21, 2019 at 3:55 pm
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?
August 21, 2019 at 3:58 pm
Sample calls from function comment. The last one works but 4 prior produce that error.
--Vadim R.
August 21, 2019 at 6:08 pm
Steve Collins 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:
Steve Collins 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)
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 21, 2019 at 7:24 pm
Have a look at this code:
declare
@start_dtdate,
@frequencyint,
@end_dtdate;
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:
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
August 21, 2019 at 8:36 pm
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
August 29, 2019 at 2:30 pm
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!
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply