CODEWITHSUNDEEP
sqlt-sql
joe
joe

Reputation: 1473

How to backfill missing Date in SQL Sever

I have a simple table below. I am trying to count the time (in days/hours/mins/ and total hours between arrival dates and dept dates.

CREATE TABLE TEMP 
(
    Flight_ID INT,
    Arrival_Dt DATETIME,
    Dept_DT DATETIME
)

INSERT INTO temp 
VALUES ('123', '10/14/2018 09:25 pm', '10/14/2018 11:21 pm'),
       ('123', '10/14/2018 11:43 pm', NULL),
       ('123', '10/14/2018 11:44 pm', '10/16/2018 08:04 am')

Here is my code:

SELECT
    ID,
    ARRIVAL_DT,
    DEPT_DT,
    DATEDIFF(SECOND, ARRIVAL_DT, DEPT_DT) / (60 * 60 * 24) AS D,
    DATEDIFF(SECOND, ARRIVAL_DT, DEPT_DT) / (60 * 60) % 24 AS H,
    DATEDIFF(SECOND, ARRIVAL_DT, DEPT_DT) / (60) % 60      AS M,
    CAST(DEPT_DT - ARRIVAL_DT AS FLOAT) * 24               AS TOTAL_HRS
FROM 
    TEMP

The issue I have is the NULL date in the Dept_Dt column. The date for the NULL value should be 10/14/2018 11:44 PM (one minute after 10/14/2018 11:43pm).

Currently, I am using the GETDATE() function to replace the null values. But doing so would inflate the time exponentially.

Here is the example:

CREATE TABLE TEMP1 
(
    ID INT,
    ARRIVAL_DT DATETIME,
    DEPT_DT DATETIME
)

INSERT INTO TEMP1 
VALUES ('123', '10/14/2018 09:25 PM', '10/14/2018 11:21 PM'),
       ('123', '10/14/2018 11:43 PM', '09/09/2019 09:00 AM'),
       ('123', '10/14/2018 11:44 PM', '10/16/2018 08:04 AM')

As you can see, the middle record has very high in total hours (329 days, 9 hours, 17 mins, total 7905 hours)

Ideally, the accurate time is as follows:

CREATE TABLE TEMP2 
(
    ID INT,
    ARRIVAL_DT DATETIME,
    DEPT_DT DATETIME
)

INSERT INTO TEMP2 
VALUES ('123', '10/14/2018 09:25 PM', '10/14/2018 11:21 PM'),
       ('123', '10/14/2018 11:43 PM', '10/14/2018 11:44 PM'),
       ('123', '10/14/2018 11:44 PM', '10/16/2018 08:04 AM')

SELECT
    ID,
    ARRIVAL_DT,
    DEPT_DT,
    DATEDIFF(SECOND, ARRIVAL_DT, DEPT_DT) / (60 * 60 * 24) AS D,
    DATEDIFF(SECOND, ARRIVAL_DT, DEPT_DT) / (60 * 60) % 24 AS H,
    DATEDIFF(SECOND, ARRIVAL_DT, DEPT_DT) / (60) % 60      AS M,
    CAST(DEPT_DT - ARRIVAL_DT AS FLOAT) * 24               AS TOTAL_HRS
FROM 
    #TEMP2

Question is - is there a way to find the null values then replace its next arrival date? or some direction would be greatly appreciated. I am sorry to present a lengthy example that might have caused some confusion.

Upvotes: 0

Views: 318

Answers (2)

daShier
daShier

Reputation: 2116

Have you tried:

    UPDATE temp SET DEPT_DT = DATEADD(minute, 1, ARRIVAL_DT)
    WHERE DEPT_DT IS NULL

This sets the departure date to one minute after the arrival date in the case where the departure date is currently NULL.

If you are trying to just move it into TEMP1 then:

    INSERT INTO TEMP1 (ID,  ARRIVAL_DT, DEPT_DT)
    (SELECT Flight_ID, ARRIVAL_DT,
    ISNULL(DEPT_DT, DATEADD(minute, 1, ARRIVAL_DT))
    FROM temp)

Upvotes: 0

PeterHe
PeterHe

Reputation: 2766

You can use LEAD function to achieve this:

SELECT Flight_ID,
    Arrival_Dt,
    ISNULL(Dept_DT,LEAD(Arrival_Dt) OVER(PARTITION BY Flight_ID ORDER BY Arrival_Dt)) AS Dept_DT
FROM temp

Upvotes: 2

Related Questions