Member 14547089 Ответов: 2

Расчет раннего и позднего прибытия для выполнения работы

Я пытаюсь рассчитать производительность доставки - я застрял в нескольких областях, и я надеюсь, что смогу получить некоторую помощь!

У меня есть приблизительная дата отгрузки, дата Appt и фактическая дата отгрузки.

Я измеряю производительность склада, поэтому в большинстве случаев отгрузки уходят в тот же день. Но в других случаях они могут уйти на несколько дней раньше или позже.

Проблема, с которой я сталкиваюсь, - это правильный вывод. Я хочу показать значения в DD:HH:MM, но синтаксис DateDiff не дает мне точного дня для использования:

Например, груз должен был уйти 6/3/2019 @ в 8 утра, но не покинул склад до 7/22/2019 @ 6:30 утра. В этом случае DateDiff вычисляет 49 дней, когда на самом деле его 48 дней опаздывают на 22 часа и 30 минут. Вот пример некоторого синтаксиса, который я использую:

Что я уже пробовал:

create table #test (
  EstimatedShipDate datetime,
  AppointmentShipDate datetime,
  ActualShipDate datetime
  );

insert into #test values ('2019-07-01 11:00', '2019-07-01 11:00','2019-06-30 10:30');
insert into #test values ('2019-07-08 13:45', null,'2019-07-01 22:00');
insert into #test values ('2019-07-09 15:00', null,'2019-07-10 15:00');
insert into #test values ('2019-07-03 15:00', null,'2019-07-04 15:00');
insert into #test values ('2019-07-08 15:00', null,'2019-07-08 15:00');
insert into #test values ('2019-07-08 15:00', null,'2019-07-08 22:00');
insert into #test values ('2019-07-03 08:00', null,'2019-07-04 15:00');
insert into #test values ('2019-07-03 08:00', null,'2019-07-03 06:30');
insert into #test values ('2019-06-03 08:00', null,'2019-07-22 06:30');
--insert into test values (3, '2012-01-02 12:00');

Select 
EstimatedShipDate,
AppointmentShipDate,
ActualShipDate,


DATEDIFF(DAY,ISNULL(CAST(AppointmentShipDate as DateTime),CAST(EstimatedShipDate as DateTime)), CAST(ActualShipDate as DateTime)) as Days,

convert(varchar, CAST(ActualShipDate as DateTime)-ISNULL(CAST(AppointmentShipDate as DateTime),CAST(EstimatedShipDate as DateTime)),108) as DateSubtract_Convert,

convert(varchar,ISNULL(CAST(AppointmentShipDate as DateTime),CAST(EstimatedShipDate as DateTime)-CAST(ActualShipDate as DateTime) ),108) as DateSubtract_ConvertEarly
from #TEST


Используя приведенный выше пример вывод выглядит следующим образом:

EstimatedShipDate	2019-06-03 08:00:00.000
AppointmentShipDate	null
ActualShipDate	2019-07-22 06:30:00.000
Days	49	
DateSubtract_Convert	22:30:00
DateSubtract_ConvertEarly	01:30:00


Любой совет очень ценится!

Afzaal Ahmad Zeeshan

Элементарная арифметика.

2 Ответов

Рейтинг:
15

Richard Deeming

Просто: вычислите общую разницу в минутах и используйте деление и оператор модуля[^] чтобы преобразовать это в дни, часы и минуты.

SELECT
    EstimatedShipDate,
    AppointmentShipDate,
    ActualShipDate,
    DateDiff(minute, IsNull(AppointmentShipDate, EstimatedShipDate), ActualShipDate) As TotalMinutes,
    CAST(FLOOR(DateDiff(minute, IsNull(AppointmentShipDate, EstimatedShipDate), ActualShipDate) / 1440) As int) As Days,
    CAST(FLOOR((DateDiff(minute, IsNull(AppointmentShipDate, EstimatedShipDate), ActualShipDate) % 1440) / 60) As int) As Hours,
    CAST(FLOOR(DateDiff(minute, IsNull(AppointmentShipDate, EstimatedShipDate), ActualShipDate) % 60) As int) As Minutes
FROM
    #Test
;
Для вашего примера строки, которая дает: 
EstimatedShipDate    2019-06-03 08:00:00.000
AppointmentShipDate  null
ActualShipDate       2019-07-22 06:30:00.000
TotalMinutes         70470
Days                 48
Hours                22
Minutes              30
NB: DateDiff Не довольно работайте так, как вы могли бы ожидать, как описано в этот ответ StackOverflow[^]. Он подсчитывает количество раз, когда вы проходите указанную границу.

Так, например, между 2018/12/31 23:59:59 и 2019/01/01 00:00:01, он вернет разницу в 1 для minute, hour, day, month и year.

MadMyche

+5

Member 14547089

ОМГ, спасибо тебе!! Я не так хорошо разбираюсь в коде, поэтому я забираю его у других-это очень полезно! +5 тебе!

Рейтинг:
0

MadMyche

Richard's ответ правильный; и для специальных запросов или в отчете это был бы правильный путь

Первое, что вам нужно понять, это то, что элементы DateTime на самом деле являются числами, и что любая математика, которую вы делаете с ними, является преобразованием в число, выполните математику над числом и преобразуйте его обратно в объект DateTime.

SQL-серверы в целом оптимизированы для общих функций CRUD. Когда дело доходит до выполнения функций, манипуляций, математики, это не лучшее место для этого.

Источники данных - это просто источники данных. То, что вы делаете с этими данными, должно быть сделано в приложении, которое использует сами данные; и обычно на уровне представления

Например; если вы используете C#, вы можете использовать переменную, определенную как TimeSpan для интервалов, которые вам нужны.

// Two DateTime objects
DateTime EstimatedShipDate = DateTime.Parse("06/03/2019 08:00");
DateTime ActualShipDate = DateTime.Parse("07/22/2019 06:30");

// Difference between the 2 objects
TimeSpan AmountOverDue = ActualShipDate - EstimatedShipDate;

// "Presentation Layer" version of the overdue time
string OverDue = AmountOverDue.ToString(@"dd\:hh\:mm");
Рекомендации:
Период Структуры (Система) | Майкрософт Документы[^]
Пользовательские строки формата TimeSpan - .NET | Microsoft Docs[^]