SQL Server Performance

Help select multirecord wtih condition

Discussion in 'General Developer Questions' started by PeterNguyen, May 11, 2007.

  1. PeterNguyen New Member

    Hi
    I have table
    Date DN Meter1 Meter2 Meter3
    ----------------------------------------
    01/02/2007 1234567 123 123 123
    02/02/2007 1234567 125 126 127
    03/02/2007 1234567 126 127 129
    04/02/2007 1234567 127 128 130
    01/02/2007 1234566 23 23 123
    02/02/2007 1234566 25 26 127
    03/02/2007 1234566 26 27 129
    04/02/2007 1234566 37 28 130
    01/02/2007 1234565 3 23 1
    02/02/2007 1234565 35 36 27
    03/02/2007 1234565 56 37 29
    04/02/2007 1234565 97 98 130

    I want select DN (in date 01/02/2007 and in date 04/02/2007 ) where ( Meter1( in date 04/02/2007) - Meter1 (in date 01/02/2007) > 5

    Please help

    Thanks
  2. Adriaan New Member

    If you want to match the results from these two queries -

    SELECT t1.DN, t1.Meter1
    FROM MyTable t1
    WHERE t1.Date = '2007-02-04'

    SELECT t2.DN, t2.Meter1
    FROM MyTable t2
    WHERE t2.Date = '2007-02-01'

    - where DN is identical, and the difference between Meter1 > 5, you have two options:

    (1) Use a self-join:

    SELECT t1.DN, t1.Meter1, t2.Meter1
    FROM MyTable t1
    INNER JOIN MyTable t2 ON t1.DN = t2.DN
    WHERE t1.Date = '2007-02-04' AND t2.Date = '2007-02-01'
    AND ((t1.Meter1 - t2.Meter1) > 5)

    (2) Use derived tables:

    SELECT t1.DN, t1.Meter1, t2.Meter1
    FROM (SELECT DN, Meter1 FROM MyTable WHERE Date = '2007-02-04') t1
    INNER JOIN (SELECT DN, Meter1 FROM MyTable WHERE Date = '2007-02-01') t2
    ON t1.DN = t2.DN
    WHERE (t1.Meter1 - t2.Meter1) > 5

    They produce the same result, so use whichever you find easiest to understand.
  3. PeterNguyen New Member

    Thank Adriaan very much !
    it work

Share This Page