Sql-server – NULL out value in table 1, depending on a time-log in table 2

sql serversql-server-2012table

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.

Best Answer

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 )
SELECT *
FROM (
        VALUES('12:01', 100),
              ('12:02', 110),
              ('12:03', 115),
              ('12:04', 100)
     ) x (myDateTime, Produced)


INSERT #crap2 ( myStartDateTime, myEndDateTime )
SELECT *
FROM (
        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;

Related Solutions

Sql-server – SQL Server 2008 – Return records in chronological order, given a separation factor

Prior to SQL 2008, the most common solution was to use a UDF to calculate the great-circle distance between two points on a sphere. The Haversine formula is probably the most commonly used method.

Of course the Earth is not actually a perfect sphere, but this was considered "good enough" for most uses.

In SQL 2008, as you anticipated, such calculations are simplified and made more accurate by the introduction of the Geography and Geometry data types. Here's a brief sample of how you can use them to simplify distance calculations.

DECLARE @locations TABLE(locname VARCHAR(100), coord geography)
DECLARE @loc1 geography
DECLARE @loc2 geography


INSERT INTO @locations 
VALUES('HOME', geography::Point(-81.810194, 41.478156, 4326))   --Note: Lat, Long, SRID
                                                                --The 4326 is the SRID (spatial reference id) used by SQL as 
                                                                --a reference to the WGS 84 Standard. This is the same reference
                                                                --used by the GPS system
INSERT INTO @locations 
VALUES('WORK', geography::Point(-81.687771, 41.498227, 4326))

SELECT * FROM @locations

SELECT @loc1 = coord FROM @locations WHERE locname = 'HOME'
SELECT @loc2 = coord FROM @locations WHERE locname = 'WORK'

SELECT @loc1.STDistance(@loc2) * 3.2808399  --STDistance is in meters so we multiply to convert to feet 

SELECT @loc1.STIntersects(@loc2.STBuffer(300 / 3.2808399)) as isWithin300Ft  --This formula returns True when the point @loc1 intersects with the 300ft buffer zone around @loc2

The SRID is the key to the improved accuracy. The WGS 84 specification to which it refers includes a standardized coordinate system and a reference ellipsoid. In other words, it accounts for the non-spherical nature of the Earth, giving better results than a pure spherical Great Circle calculation.

If GIS accuracy is important to your work, this is the simplest way to implement it in SQL 2008.

Sql-server – Will one nvarchar column affect query performance

Without knowing more details, it's impossible to tell, but you might be suffering from implicit conversion. There are a number of sites with information on the topic that you can use to help troubleshoot your particular query.

The short answer is that no, you should not need to convert all columns to nvarchar. If you are trying to join an nvarchar column to a varchar column, however, you could see a major performance impediment.

Related Question