Как написать функцию Navision CALCDATE в TSQL
Вот определение функции Navision CALCDATE :
NewDate := CALCDATE(DateExpression [, Date])
Принимает два параметра:
- DateExpression, который в Navision имеет тип DateFormula, который в SQL хранится как varchar (32)
- Дата, что в SQL означает DateTime. Чтобы упростить проблему, я предполагаю, что это не обязательно (в Navsion это так)
Мне нужно получить доступ к данным Navision из SQL и выполнить некоторые вычисления на основе хранимых там DateFormulas. Я знаю, как написать такую функцию в SQL CLR, поэтому, пожалуйста, не включайте это в свой ответ.
- Как написать функцию CALCDATE в TSQL?
- [Необязательно] Можно ли сделать это только в запросе SQL, используя некоторые (вспомогательные) CTE (это позволит мне не добавлять функцию в базу данных Navsion, а следовательно, не изменять ее)?
- Является ли SQL CLR моим единственным вариантом?
Основная проблема, с которой я столкнулся с этой функцией, заключается в том, что кажется, что это нужно сделать в следующих шагах:
- Анализировать DateExpression
- Примените результат синтаксического анализа к параметру Date и создайте новую дату.
Но как заняться парсингом?
Например, DateExpression = '-CW+1W+1D' означает, что мы должны сделать 3 вещи с нашим параметром Date именно в таком порядке:
- Найти последний понедельник (начало текущей недели) '-CW'
- Добавьте 1 неделю (7 дней) '+1W'
- Добавить 1 день '+1D'
Это может быть записано в более короткой форме «+ CW + 1D», что означает следующий понедельник и 1 день. Обычно этот varchar (32) может содержать 1 или несколько таких выражений, и они должны применяться в указанном порядке к параметру Date.
Как разобрать такой varchar (32) в SQL?
В C# я бы сделал это в цикле или рекурсивно, но как это сделать в SQL (наборах)?
Как преобразовать такой структурированный varchar (32) в набор формул. Возможно, мне нужно как-то преобразовать его в XML и запросить его с помощью функций SQL Server XML - но как?
РЕДАКТИРОВАТЬ: Как я хочу его использовать?
Я хочу использовать эту функцию как часть более крупного запроса.
Например (упрощенно):
select ...
from [Issued Reminder Header] as reminder
join [Reminder Terms] as terms on ...
join [Reminder Level] as level on ...
cross apply dbo.CalcDate(level.[Grace Period], reminder.[Posting Date]) as calculated
Я имею в виду этот уровень. [Льготный период] находится в таблице, которая может редактироваться пользователями и может быть изменена в любой момент. Таким образом, я не могу использовать какие-либо ярлыки и предварительно рассчитать несколько значений.
1 ответ
Этот ответ здесь в первую очередь как пример того, насколько глупо было бы пытаться реализовать на чистом SQL (а также потому, что я считал это интересной задачей).
Он не предназначен для использования в каких-либо производственных мощностях, и я даже не уверен, что он должным образом реализует функциональность или будет работать во всех возможных сценариях, которые
calcdate позволяет.
SQL
declare @d date = '19960521';
declare @t table(Expression varchar(32),ExpectedValue date);
insert into @t values
('-CW+1W+1D','19960528')
,('CQ+1M-10D','19960720')
,('+CW+1D','19960528')
,('+CW+1WD','19960528')
,('CM+30D','19960630')
,('-WD2','19960514')
,('WD3','19960522')
,('-D3','19960503')
,('-D27','19960427')
,('D3','19960603')
,('D27','19960527')
,('10D','19960531')
,('1W','19960528')
,('W2','19970106')
,('-W2','19960108')
,('M12','19961201')
,('M2','19970201')
,('-M2','19960201')
,('Q4','19961001')
,('Q2','19970401')
,('-Q2','19960401')
;
with v as
(
select Expression
,ExpectedValue
,stuff(replace(replace(case when left(Expression,1) not in('+','-') then '+' else '' end + Expression
,'+','|+'
)
,'-','|-'
)
,1,1,''
) + '|||' as Fmt
from @t
)
,p as
(
select Expression
,ExpectedValue
,Fmt
,left(v.Fmt,charindex('|',v.Fmt,0)-1) as p1
,substring(v.Fmt
,charindex('|',v.Fmt,0)+1
,(charindex('|',v.Fmt,charindex('|',v.Fmt,0)+1)-1) - (charindex('|',v.Fmt,0))
) as p2
,replace(substring(v.Fmt
,charindex('|',v.Fmt,charindex('|',v.Fmt,0)+1)+1
,999
)
,'|'
,''
) as p3
from v
)
select @d as GivenDate
,p.Expression
,p.ExpectedValue
,cast(v3.retp3 as date) as ReturnedValue
,case when p.ExpectedValue = v3.retp3 then 'Match' else 'No Match' end as ValueCheck
from p
outer apply(values(case when substring(p1,2,1) = 'C' -- <Prefix><Unit>
then case substring(p1,3,2)
when 'D' then @d
when 'WD' then @d
when 'W' then dateadd(week, datediff(week,0,@d) + case when left(p1,1) = '+' then 1 else 0 end, 0)
when 'M' then case when left(p1,1) = '+' then eomonth(@d) else dateadd(day,1,eomonth(@d,-1)) end
when 'Q' then dateadd(day,case when left(p1,1) = '+' then -1 else 0 end,dateadd(quarter, datediff(quarter,0,@d) + case when left(p1,1) = '+' then 1 else 0 end, 0))
when 'Y' then dateadd(year, datediff(year,0,@d) + case when left(p1,1) = '+' then 1 else 0 end, 0)
else ''
end
when isnumeric(substring(p1,2,1)) = 1 -- <Number><Unit>
then case when right(p1,2) = 'WD'
then dateadd(day,cast(replace(p1,'WD','') as int),@d)
else case right(p1,1)
when 'D' then dateadd(day,cast(replace(p1,'D','') as int),@d)
when 'W' then dateadd(week,cast(replace(p1,'W','') as int),@d)
when 'M' then dateadd(month,cast(replace(p1,'M','') as int),@d)
when 'Q' then dateadd(quarter,cast(replace(p1,'Q','') as int),@d)
when 'Y' then dateadd(year,cast(replace(p1,'Y','') as int),@d)
end
end
when isnumeric(substring(p1,2,1)) = 0 -- <Unit><Number>
then case when substring(p1,2,2) = 'WD'
then dateadd(day,right(p1,1)-1,dateadd(week, datediff(week,0,@d) - case when left(p1,1) = '-' then 1 else 0 end, 0))
else case substring(p1,2,1)
when 'D' then dateadd(day,abs(cast(replace(p1,'D','') as int))-1,dateadd(month, datediff(month,0,@d) + case when abs(cast(replace(p1,'D','') as int)) < day(@d) then 1 else 0 end + case when sign(cast(replace(p1,'D','') as int)) = -1 then -1 else 0 end, 0))
when 'W' then dateadd(week,datediff(week,0,dateadd(week,abs(cast(replace(p1,'W','') as int))-1,datefromparts(year(@d) + case when abs(cast(replace(p1,'W','') as int)) <= datepart(week,@d) then 1 else 0 end + case when sign(cast(replace(p1,'W','') as int)) = -1 then -1 else 0 end,1,1))), 0)
when 'M' then datefromparts(year(@d) + case when abs(cast(replace(p1,'M','') as int)) <= month(@d) then 1 else 0 end + case when sign(cast(replace(p1,'M','') as int)) = -1 then -1 else 0 end,abs(cast(replace(p1,'M','') as int)),1)
when 'Q' then datefromparts(year(@d) + case when abs(cast(replace(p1,'Q','') as int)) <= datepart(quarter,@d) then 1 else 0 end + case when sign(cast(replace(p1,'Q','') as int)) = -1 then -1 else 0 end,((abs(cast(replace(p1,'Q','') as int))-1)*3)+1,1)
when 'Y' then datefromparts(abs(cast(replace(p1,'Y','') as int)),1,1)
end
end
else ''
end
)
) as v1(retp1)
outer apply(values(case right(p2,1)
when 'D' then dateadd(day,cast(replace(replace(p2,'W',''),'D','') as int),retp1)
when 'W' then dateadd(day,cast(replace(p2,'W','') as int) * 7,retp1)
when 'M' then dateadd(month,cast(replace(p2,'M','') as int),retp1)
when 'Q' then dateadd(quarter,cast(replace(p2,'Q','') as int),retp1)
when 'Y' then dateadd(year,cast(replace(p2,'Y','') as int),retp1)
else retp1
end
)
) as v2(retp2)
outer apply(values(case right(p3,1)
when 'D' then dateadd(day,cast(replace(replace(p3,'W',''),'D','') as int),retp2)
when 'W' then dateadd(day,cast(replace(p3,'W','') as int) * 7,retp2)
when 'M' then dateadd(month,cast(replace(p3,'M','') as int),retp2)
when 'Q' then dateadd(quarter,cast(replace(p3,'Q','') as int),retp2)
when 'Y' then dateadd(year,cast(replace(p3,'Y','') as int),retp2)
else retp2
end
)
) as v3(retp3);