I have two tables in MS SQL Server 2012. The first is a minute by minute production record which looks like (can't guarantee every single minute gets its own record, but never two records for one minute):

[myDateTime] [Produced]
12:01           100
12:02           110
12:03           115
12:04           100

The second table is a journal-style log of whether to consider the [Produced] column from Table 1 or not.

[myStartDateTime] [myEndDateTime]
     12:02            12:03
     12:47            12:58

I want to be able to look through each row of Table 1 and either take the [Produced] column, or take a NULL value, depending on if that minute falls within a record of Table 2 (ie between myStartDateTime and myEndDateTime INCLUSIVE).

In the example above, that would look like:

[myDateTime] [Produced]
12:01           100
12:02           NULL
12:03           NULL
12:04           100

I can't seem to get this to work (in code, or in my head). In my head I think I should join on myStartDateTime and join on myEndDateTime and make a new column called [Include] and set it to 1 or 0 depending on whether I want to include it or not. Each table might have 300 - 400 thousand rows and everything I am trying is slow.

Any help would be appreciated.


This will get you the result you want, but it's a strange requirement.

If you want to make it fast, we'd need a lot more information, like table and index definitions, query plans, etc. "It's slow" isn't particularly helpful.

CREATE TABLE #crap1 (myDateTime TIME, Produced INT)

CREATE TABLE #crap2 (myStartDateTime TIME, myEndDateTime TIME )

INSERT #crap1 ( myDateTime, Produced )
        VALUES('12:01', 100),
              ('12:02', 110),
              ('12:03', 115),
              ('12:04', 100)
     ) x (myDateTime, Produced)

INSERT #crap2 ( myStartDateTime, myEndDateTime )
        VALUES('12:02', '12:03'),
              ('12:47', '12:58')
     ) x ( myStartDateTime, myEndDateTime )

SELECT c.myDateTime, CASE WHEN c2.myStartDateTime IS NULL THEN c.Produced
                          ELSE NULL
                     END AS Whatever
    FROM #crap1 AS c
    LEFT JOIN #crap2 AS c2
    ON  c.myDateTime >= c2.myStartDateTime
        AND c.myDateTime <= c2.myEndDateTime; 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.