Calculating early and late arrival for performance

Question

0.00/5 (No votes)

See more:

I am trying to calculate shipping performance- Im getting stuck in several areas and I hope i can get some help!

I have an Estimated Ship Date, an Appt Date, and an Actual Ship date.

Im measuring warehouse performance, so in most cases shipments leave same day. But in others, they may leave a few days early or late.

The problem that i am having is the correct output. I want to show the Values in DD:HH:MM, but the syntax for DateDiff doesn't give me an accurate day to use:

For Example, a shipment was supposed to leave on 6/3/2019 @ at 8 am, but didnt leave the warehouse until 7/22/2019 @ 6:30 AM. In this case, DateDiff calcs 49 days, when really its 48 days 22 hours and 30 minutes late. Here is an example of some of the syntax i am using:

What I have tried:

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

Using the example above output looks like:

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

Any advice is greatly appreciated!

Posted 1-Aug-19 6:10am

Member 14547089

Updated 1-Aug-19 8:42am

v2

Add a Solution

Comments

Afzaal Ahmad Zeeshan 1-Aug-19 13:06pm

Basic arithmetic.

2 solutions

Solution 2

Richard's answer is correct; and for ad-hoc queries or in a report this would be the way to go

The first thing you need to understand is that DateTime items are actually numbers, and that any math you are doing with them is a conversion to the number, perform the math on the number, and convert it back to a DateTime object.

SQL servers in general are optimized for the general CRUD functions. When it comes to performing functions, manipulations, math; it is not the best place to do it.

Data sources are just that- sources of data. What you do with that data should be done in the application that is using the data itself; and usually at the Presentation Layer

For example; if you were using C#, you could use a variable defined as a TimeSpan for the intervals you need.

C#

// 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");

References:
TimeSpan Struct (System) | Microsoft Docs[^]
Custom TimeSpan format strings - .NET | Microsoft Docs[^]

Posted 1-Aug-19 8:42am

MadMyche

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2019-08-01T07:36:00

Simple: calculate the total difference in minutes, and use division and the modulus operator[^] to convert that to days, hours, and minutes.

SQL

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
;

For your example row, that gives:

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 doesn't quite work as you might expect, as explained in this StackOverflow answer[^]. It counts the number of times you pass the specified boundary.

So, for example, between 2018/12/31 23:59:59 and 2019/01/01 00:00:01, it will return a difference of 1 for minute, hour, day, month and year.

Calculating early and late arrival for performance

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Calculating early and late arrival for performance

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us