Преобразование формулы excel в SQL-запрос

Это файл Excel, в котором я могу использовать формулу для получения TAT ORIG :

введите описание изображения здесь

 =IF((X3-W3)*24<=24,(X3-W3)*24, IF(AND(WEEKDAY(W3,2)<6,WEEKDAY(X3,2)<6),(NETWORKDAYS(W3,X3)-1+MOD(X3,1)-MOD(W3,1))*24, IF(OR(WEEKDAY(W3,2)>5,WEEKDAY(X3,2)>5),(NETWORKDAYS(W3,X3)*24)))) 

это формула, которую я использовал для получения значения для TAT ORIG в EXCEL

Мне нужна такая же формула для преобразования или трюка, чтобы получить то же значение в существующей таблице SQL, с именем столбца tat_orig

в этой таблице мне нужно, чтобы tat_orig рассчитывался с помощью SQL-запроса:

введите описание изображения здесь

Вау, я не ожидал, что это займет так много времени. Вот что у меня есть:

 --Network days shim IF OBJECT_ID(N'NETWORKDAYS', N'FN') IS NOT NULL DROP FUNCTION dbo.NETWORKDAYS; GO CREATE FUNCTION dbo.NETWORKDAYS(@d1 datetime, @d2 datetime ) RETURNS int AS BEGIN DECLARE @w1 int = DATEPART(weekday, @d1); DECLARE @w2 int = DATEPART(weekday, @d1); DECLARE @dd float = FLOOR(DATEDIFF(ms, @d1, @d2) / 86400000.0); -- network days is based on a holidays table; I just added this date arbitrarily so that -- the results match what Excel says DECLARE @holidays TABLE(holiday datetime); INSERT INTO @holidays VALUES ('2016-06-15'); RETURN (@dd + @w2 - @w1) / 7 * 5 + @w2 - @w1 + 1 + IIF(@w2 = 7, -1, 0) + IIF(@w1 = 1, -1, 0) + (SELECT COUNT(*) FROM @holidays WHERE @d1 <= holiday AND holiday < @d2); END GO -- turn around time shim IF OBJECT_ID(N'TURNAROUND', N'FN') IS NOT NULL DROP FUNCTION dbo.TURNAROUND; GO CREATE FUNCTION dbo.TURNAROUND(@d1 datetime, @d2 datetime) RETURNS float AS BEGIN DECLARE @w1 int = DATEPART(weekday, @d1); DECLARE @w2 int = DATEPART(weekday, @d1); DECLARE @nd int = dbo.NETWORKDAYS(@d1, @d2); DECLARE @hd float = DATEDIFF(ms, @d1, @d2) / 3600000.0; DECLARE @td float = DATEDIFF(ms, CAST(@d1 AS TIME), CAST(@d2 AS TIME)) / 86400000.0; RETURN ( IIF(@hd <= 24.0, @hd, IIF(@w1 < 6 AND @w2 < 6, 24 * (@nd - 1 + @td), IIF(@w2 > 5 OR @w1 > 5, 24 * @nd, 0)))); END GO -- the data DECLARE @items TABLE ( time_created datetime, time_responded datetime ); INSERT INTO @items VALUES ('2016-06-10 15:42:00.000', '2016-06-15 03:03:00.000'), ('2016-06-15 01:28:00.000', '2016-06-15 03:03:00.000'), ('2016-06-14 07:46:00.000', '2016-06-15 03:03:00.000'), ('2016-07-04 05:35:25.000', '2016-07-04 19:05:48.000'), ('2016-07-04 04:56:09.000', '2016-07-04 18:29:28.000'), ('2016-07-04 09:15:33.000', '2016-07-04 22:08:43.000'), ('2016-07-04 08:44:24.000', '2016-07-04 21:40:57.000'), ('2016-07-04 07:14:51.000', '2016-07-04 21:39:24.000'); -- the results SELECT time_created, time_responded, dbo.TURNAROUND(time_created, time_responded) AS [TAT Orig] FROM @items; 

Трудная часть заключалась в определении арифметики даты. Вам не нужно объявлять функции – они доступны для ясности и вычисления средних значений, но технически вы должны иметь возможность использовать возвращаемые значения в SELECT .

Кстати, вам не повезло, если ваш вычисленный столбец принимает значения из следующей строки – это невозможно в SQL, но это близко.

Надеюсь, это поможет!

РЕДАКТИРОВАТЬ:

Я добавил дату diff большой размер. Я добавил тестовые данные за один год.

 --Big datediff shim IF OBJECT_ID(N'DATEDIFFBIG', N'FN') IS NOT NULL DROP FUNCTION dbo.DATEDIFFBIG; GO CREATE FUNCTION DATEDIFFBIG(@d1 datetime, @d2 datetime) RETURNS bigint AS BEGIN RETURN CONVERT(bigint, DATEDIFF(day, @d1, @d2)) * 86400000 - DATEDIFF(second, DATEADD(day, DATEDIFF(day, 0, @d1), 0), @d1) * 1000 + DATEDIFF(second, DATEADD(day, DATEDIFF(day, 0, @d2), 0), @d2) * 1000; END GO --Network days shim IF OBJECT_ID(N'NETWORKDAYS', N'FN') IS NOT NULL DROP FUNCTION dbo.NETWORKDAYS; GO CREATE FUNCTION dbo.NETWORKDAYS(@d1 datetime, @d2 datetime) RETURNS int AS BEGIN DECLARE @w1 int = DATEPART(weekday, @d1); DECLARE @w2 int = DATEPART(weekday, @d1); DECLARE @dd float = FLOOR(dbo.DATEDIFFBIG(@d1, @d2) / 86400000.0); -- network days is based on a holidays table; I just added this date arbitrarily so that -- the results match what Excel says DECLARE @holidays TABLE(holiday datetime); INSERT INTO @holidays VALUES ('2016-06-15'); RETURN (@dd + @w2 - @w1) / 7 * 5 + @w2 - @w1 + 1 + IIF(@w2 = 7, -1, 0) + IIF(@w1 = 1, -1, 0) + (SELECT COUNT(*) FROM @holidays WHERE @d1 <= holiday AND holiday < @d2); END GO -- turn around time shim IF OBJECT_ID(N'TURNAROUND', N'FN') IS NOT NULL DROP FUNCTION dbo.TURNAROUND; GO CREATE FUNCTION dbo.TURNAROUND(@d1 datetime, @d2 datetime) RETURNS float AS BEGIN DECLARE @w1 int = DATEPART(weekday, @d1); DECLARE @w2 int = DATEPART(weekday, @d1); DECLARE @nd int = dbo.NETWORKDAYS(@d1, @d2); DECLARE @hd float = dbo.DATEDIFFBIG(@d1, @d2) / 3600000.0; DECLARE @td float = dbo.DATEDIFFBIG(CAST(@d1 AS TIME), CAST(@d2 AS TIME)) / 86400000.0; RETURN ( IIF(@hd <= 24.0, @hd, IIF(@w1 < 6 AND @w2 < 6, 24 * (@nd - 1 + @td), IIF(@w2 > 5 OR @w1 > 5, 24 * @nd, 0)))); END GO -- the data DECLARE @items TABLE ( time_created datetime, time_responded datetime ); INSERT INTO @items VALUES ('2016-06-10 15:42:00.000', '2016-06-15 03:03:00.000'), ('2016-06-15 01:28:00.000', '2016-06-15 03:03:00.000'), ('2016-06-14 07:46:00.000', '2016-06-15 03:03:00.000'), ('2016-07-04 05:35:25.000', '2016-07-04 19:05:48.000'), ('2016-07-04 04:56:09.000', '2016-07-04 18:29:28.000'), ('2016-07-04 09:15:33.000', '2016-07-04 22:08:43.000'), ('2016-07-04 08:44:24.000', '2016-07-04 21:40:57.000'), ('2016-07-04 07:14:51.000', '2016-07-04 21:39:24.000'), ('2015-07-04 07:14:51.000', '2016-07-04 21:39:24.000'); -- the results SELECT time_created, time_responded, dbo.TURNAROUND(time_created, time_responded) AS [TAT Orig] FROM @items; 
  • Скрипт для импорта постоянно изменяющегося файла CSV Excel в SQL Server Express 2008
  • SQL Server: растягивает значения до явных ячеек
  • Сохранение файла excel в папке приложения и загрузка содержимого на SQL Server с использованием оконных форм и C #
  • как использовать запрос mdx в excel
  • Проблема при прохождении параметра в хранимой процедуре
  • Как избежать ошибок, когда несколько пользователей увеличивают числовое поле в SQL Server
  • MIN / MAX не работает с столбцом, вытолкнутым из SQL
  • нужна помощь для добавления рабочего листа к существующему xlsx через модуль PowerShell «PSExcel»
  • GetValue () не считывает десятичные значения, когда первая строка является строкой
  • Как импортировать сложные реляционные данные в SQL Server из Excel
  • SQL Server AVG и Excel AVERAGE дают разные результаты?
  • Interesting Posts

    Ограничить доступ к файлу Excel, открытому программой C #

    Индекс Excel отсутствует на листе, но включен в формулу

    Как записать событие удаления в excel

    Замена существующего листа в Excel 2007 с использованием VBA

    Пароль как функция и время работы не работают правильно

    В Excel есть ли способ связать выпадающее меню с конкретными данными?

    Excel Userform, Сохранить на рабочий стол

    Генерировать excel или html с помощью coldfusion из формата JSON

    Excel VBA для цикла именованный список

    Возможно ли считывать данные из нескольких источников данных в SSIS, строки соединений которых сохраняются в таблице SQL?

    Как перетаскивать столбцы, но менять букву столбца?

    Столбец не выполняет формулу Excel / C # / EPPlus

    В EXCEL, как мне преобразовать значение времени J2000 в стандартную дату и военное время?

    Excel, статистика консолидации автоматического обновления

    Блокировка данных в таблице Excel

    Давайте будем гением компьютера.