Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2010
    Posts
    11

    Unanswered: Returning a current and Previous value together

    Hi

    I have a table that contains read_values that I want to return the latest read along with the previous read.

    My table and some of the data contained in it looks like below:

    Act_Ref Read ReadID ReadDate
    BEG0022 4508 100011 20100901
    BEG0022 4556 100014 20100902
    BEG0022 4403 100010 20100801
    BEG0025 4598 100015 20100902
    BEG0025 4568 100306 20100905
    BEG0025 4500 100306 20100801
    BEG0123 4578 100012 20100901
    BEG0123 4590 100017 20100905
    BEG0456 4603 100018 20100905
    BEG0456 4544 100013 20100901

    I only want to return the last two reads by Act_Ref using the Read_ID field, so my outcome would look something like:

    Act_Ref Prev Curr
    BEG0022 4508 4556
    BEG0025 4568 4598
    BEG0123 4578 4590
    BEG0456 4544 4603

    I can produce a query to get the MAX read to produce the current read, but how would I choose the previous read as well?

    Any advice would be much appreciated.

    Thanks in advance

    Ben

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    Create table #readTable(
        Act_Ref    CHAR(10) NOT NULL,
        Read_    INT    NOT NULL,
        ReadID    INT NOT NULL,
        ReadDate DATE NOT NULL
    )
    
    INSERT INTO #readTable(Act_Ref, Read_, ReadID, ReadDate) VALUES
    ('BEG0022', 4508, 100011, '2010-09-01'), 
    ('BEG0022', 4556, 100014, '2010-09-02'), 
    ('BEG0022', 4403, 100010, '2010-08-01'), 
    ('BEG0025', 4598, 100015, '2010-09-02'), 
    ('BEG0025', 4568, 100306, '2010-09-05'), 
    ('BEG0025', 4500, 100306, '2010-08-01'), 
    ('BEG0123', 4578, 100012, '2010-09-01'), 
    ('BEG0123', 4590, 100017, '2010-09-05'), 
    ('BEG0456', 4603, 100018, '2010-09-05'), 
    ('BEG0456', 4544, 100013, '2010-09-01')
    
    with temp as
    (select Act_Ref, 
        Read_, 
        ReadID, 
        row_number() over(partition by Act_Ref order by ReadDate desc) as RowNum
    from #readTable)
    SELECT
        T1.Act_Ref,
        COALESCE(T2.Read_, T1.Read_) as Prev,
        T1.Read_ as Curr
    FROM temp as T1
        LEFT OUTER JOIN temp as T2 ON 
            T1.Act_Ref = T2.Act_Ref AND
            T2.RowNum = 2
    WHERE T1.RowNum = 1
    ORDER BY T1.Act_Ref ASC
    My result differs from your example.

    Yours BEG0025 4568 4598
    Mine BEG0025 4598 4568
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2010
    Posts
    11
    Hi Wim

    Thanks again for this, it works perfectly after fitting it in my code.

    I understand using the WITH Temp condition now, it was something I hadn't used previously.

    Thanks again

    Ben

Posting Permissions

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