Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004

    Unanswered: Joining records with nearest datetimes

    How would I match datetimes in records structured as follows:

    Record1 AccountNo           StartDateTime            EndDateTime
    1          1234                  4/30/2012 8:00 AM            NULL
    2          1234                         NULL                   5/15/2012 8:00 AM

    Desired Result:

    AccountNo           StartDateTime                 EndDateTime
     1234                  4/30/2012 8:00 AM       5/15/2012 8:00 AM

    Of course there are multiple accounts, about 2100 in this case but they very by time periods, and multiple start and stop dates for an account. I need to get the start times and match them w/ the nearest end times but AFTER the value of the start time. Nearest end times must be forced to correspond to the nearest start time but there are some start times w/o end times and end times w/o start times due to user data entry errors. I need a solution that handles this. It is ok w/ the customer to make the assumption of nearest times supposedly going together so they can show the users the errors. I am on SQL Server 2008 R2. Thanks.

    Last edited by Pat Phelan; 03-19-13 at 16:34. Reason: added vbulletin tags for formatting

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    The simplest answer would be:
    CREATE TABLE #DolphinDave (
       Record1          INT         NOT NULL
    ,  AccountNo        INT         NOT NULL
    ,  StartDateTime    DATETIME        NULL
    ,  EndDateTime      DATETIME        NULL
    INSERT INTO #DolphinDave
       SELECT 1, 1234, '4/30/2012 8:00 AM', NULL                UNION ALL
       SELECT 2, 1234, NULL,                '5/15/2012 8:00 AM'
    SELECT a.Record1, a.AccountNo, a.StartDateTime
    ,  (SELECT MIN(b.EndDateTime)
          FROM #DolphinDave AS b
          WHERE  a.AccountNo = b.AccountNo
             AND a.StartDateTime < b.endDateTime)
       FROM #DolphinDave AS a
       WHERE  a.StartDateTime IS NOT NULL
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2004
    I have morphed into this:

    SELECT a.AccountNo
    , a.StartDateTime
    , (
    SELECT MIN(b.EndDateTime)
    FROM #TableTemp AS b
    WHERE a.AccountNo = b.AccountNo
    AND a.StartDateTime < b.EndDateTime
    ) AS EndDateTime
    FROM #TableTemp AS a
    WHERE a.StartDateTime IS NOT NULL
    SELECT a.AccountNo
    , (
    SELECT MIN(b.StartDateTime)
    FROM #TableTemp AS b
    WHERE a.AccountNo = b.AccountNo
    AND a.StartDateTime < b.EndDateTime
    ) AS StartDateTime
    , a.EndDateTime
    FROM #TableTemp AS a
    WHERE a.EndDateTime IS NOT NULL
    AND a.EndDateTime NOT IN ( -- Removed time not in this set:
    SELECT (
    SELECT MIN(b.EndDateTime)
    FROM #TableTemp AS b
    WHERE a.AccountNo = b.AccountNo
    AND a.StartDateTime < b.EndDateTime
    ) AS EndDateTime
    FROM #TableTemp AS a
    WHERE a.StartDateTime IS NOT NULL
    AND (
    SELECT MIN(b.EndDateTime)
    FROM #TableTemp AS b
    WHERE a.AccountNo = b.AccountNo
    AND a.StartDateTime < b.EndDateTime
    ORDER BY AccountNo
    , StartDateTime
    , EndDateTime

    ........which gives me this:

    No AccountNo StartDate EndDate
    1 1234 NULL 2/22/13 12:00 PM
    2 1234 2/15/13 10:00 PM 2/18/13 4:00 PM -- this one should be NULL
    3 1234 2/16/13 2:00 AM 2/18/13 4:00 PM -- this one should be NULL
    4 1234 2/16/13 10:00 PM 2/18/13 4:00 PM -- this is correct

    5 5678 NULL 4/26/12 8:00 AM
    6 5678 NULL 4/30/12 2:00 AM
    7 5678 4/30/12 8:00 AM 5/8/12 2:00 PM
    8 5678 5/8/12 7:30 PM NULL
    9 5678 5/10/12 8:00 AM NULL
    10 5678 5/20/12 8:00 AM NULL

    I am very close. If I use a time it should be eliminated from matching to another time. So for the above example the EndDate 2/18/13 4:00 PM matching to StartDate 2/16/13 10:00 PM is what I am after. That is the trick. I think I can get the outliers based on what you provided earlier but I may need to find the minimum datediff in minutes where StartDate is before the EndDate and then throw that back against the data set and eliminate the timestamps that are already used. Make sense? Thanks so much.

    If this works I can sell it to my boss and help pay for Mike Wallace .

    Go Dolphins!


  4. #4
    Join Date
    Apr 2012
    Try something like this:

    with CTE_R as
            ROW_NUMBER() OVER(PARTITION BY AccountNo 
                              ORDER BY COALESCE(StartDateTime, EndDateTime)) as RowNum
        from MyTable
        coalesce(t.EndDateTime, n.EndDateTime)
    from CTE_R as t
    left join CTE_R as n
        on n.AccountNo = t.AccountNo and
           n.RowNum = t.RowNum + 1
    left join CTE_R as p
        on p.AccountNo = t.AccountNo and
           p.RowNum = t.RowNum - 1
        (t.RowNum = 1) or
        (t.StartDateTime is not null) or
        (t.EndDateTime is not null and p.EndDateTime is not null)
    Hope this helps.

  5. #5
    Join Date
    Jan 2013
    Provided Answers: 1

    Think about a MERGE and valid DDL ...

    How would I match DATETIMEs in records {sic: rows are not records} structured as follows:
    You might want to learn ISO-8601 since it is the only format allowed in ANSI/ISO Standard SQL as well as lots of other IT standards. The ISO temporal model uses a duration shown as (start_time, end_time) pairs; a NULL end_time means the duration is still current. What you have is a scratch tape or clip board sign-on sheet written in T-SQL!

    First create the valid table. I will only show the basic constraints and a key:

    CREATE TABLE Foobar_Accounts
    (acct_nbr CHAR(4) NOT NULL,
    something_start_timestamp DATETIME(0) NOT NULL,
    PRIMARY KEY (acct_nbr, something_start_timestamp)
    something_end_timestamp DATETIME(0),
    CHECK(something_start_timestamp < something_end_timestamp),

    Load the rows with a start timestamp. This will also load any complete rows that might be on the scratch tape.

    INSERT INTO Foobar_Accounts
    SELECT acct_nbr, something_start_timestamp, something_end_timestamp
    FROM Scratch_Tape
    WHERE something_start_timestamp IS NOT NULL;

    Now merge the end time rows. If you have multiple matches, this will return a cardinality violation error. If you have no ending time, this will crate a new row.

    MERGE INTO Foobar_Accounts AS Target
    USING (SELECT acct_nbr, something_start_timestamp, something_end_timestamp
    FROM Foobar_Scratch_Tape) AS Source
    ON Source.acct_nbr = Target.acct_nbr
    AND Source.something_end_timestamp IS NULL
    SET something_end_timestamp
    = (SELECT MIN(S.something_end_timestamp)
    FROM Source AS S
    WHERE S.something_end_timestamp
    >= Target.something_start_timestamp)
    VALUES (Source.acct_nbr, Source.something_start_timestamp, Source.something_end_timestamp);


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts