locked
Detect if a range overlaps another range RRS feed

  • Question

  • Hi. We have a table in our Production system that is a Parameter / Value lookup based on a character versio of an integer range (please don't ask...):

    Range_Begin, Range_End, Parameter, Value
    30001,30030,Para1,Value1
    30001,30030,Para2,Value2
    30031,30100,Para1,SomeOtherValue

    As new data rows come in I put them in a staging table - same layout with a "Disposition" column on the end to report on the results of new data:
    Range_Begin, Range_End, Parameter, Value, Disposition
    30020,30040,Para1,Value13,?????
    30001,30030,Para2,Value34,Replace
    40031,50100,Para4,AnotherValue,Add

    I need a query that will check each row in the staging against each row in the Production table and "Fail" (SET [Disposition] = 'Fail' ) the row if either the
    Range_Begin or Range_End is within a range & parameter row in the Production table.
    The first row in the staging table about should fail because it's
    Range_Begin is inside an existing row with the same Parameter value.

    I took a stab at it (testing only
    [Range_Begin] to start with):
    UPDATE t
    SET t.[Disposition] = 'Fail'
    FROM Staging_Table AS t
    WHERE t.[Range_Begin] BETWEEN (SELECT DISTINCT
    [Range_Begin], [Range_End]
    FROM [Production]
    WHERE [Parameter] = t.[Parameter] )

    But no luck...

    Thanks in advance for your ideas!
    John
    Wednesday, December 10, 2008 11:17 PM

Answers

  • Try:

     

    update staging

    set disposition = 'fail'

    where exists (

    select

    *

    from

    production as p

    where

    staging.parameter = p.parameter

    and staging.range_begin <= p.range_end

    and staging.range_end >= p.range_begin

    )

    go

     

     

    AMB

    Wednesday, December 10, 2008 11:49 PM