CODEWITHSUNDEEP
sqlsql-server-2008-r2
user867621
user867621

Reputation: 1197

How do I grab the most recent price change from table

I have a sql table that is an audit trail. I am trying to grab the most recent time the price was changed.

Using this query I have the following data

select    id as id_1
          ,item_no, price
          ,post_dt
          ,post_tm
          ,aud_action 
from      iminvaud_sql 
where     item_no = '1-ADVENT ENGLISH' 
          and aud_action in ('B','C') 
order     by id desc

id_1    item_no                        price    post_dt                  post_tm              aud_action

221 1-ADVENT ENGLISH                2.000000    2014-08-18 00:00:00.000 1900-01-01 10:19:35.113 C
218 1-ADVENT ENGLISH                2.000000    2014-08-18 00:00:00.000 1900-01-01 10:19:35.110 B
217 1-ADVENT ENGLISH                2.000000    2014-08-18 00:00:00.000 1900-01-01 10:01:47.163     C
216 1-ADVENT ENGLISH                3.000000    2014-08-18 00:00:00.000 1900-01-01 10:01:46.757     B
59  1-ADVENT ENGLISH                3.000000    2013-08-19 00:00:00.000 1900-01-01 13:23:32.950     C
58  1-ADVENT ENGLISH                1.000000    2013-08-19 00:00:00.000 1900-01-01 13:23:32.890     B

The system writes a B for a before change and a C for the change so in a sense the B and C records are grouped. For this example I wanted to get the 217 record because it was the most recent price change.

Upvotes: 0

Views: 367

Answers (4)

Paul Maxwell
Paul Maxwell

Reputation: 35593

To get id_1 = 217 from that list isn't TOP 1 order date/time DESC alone, because that ID is the "second most recent" C record

So, you could USE ROW_NUMBER(), or use TOP 2 then TOP 1

SELECT
      *
FROM (
            SELECT
                  id                                                      AS id_1
                , item_no
                , price
                , post_dt
                , post_tm
                , aud_action
                , ROW_NUMBER() OVER (ORDER BY post_dt DESC, post_tm DESC) AS Rn
            FROM iminvaud_sql
            WHERE item_no = '1-ADVENT ENGLISH'
                  AND aud_action = 'C'
            ORDER BY
                  post_dt DESC, post_tm DESC
      ) sq
WHERE RN = 2
;

or

SELECT TOP 1
      *
FROM (
            SELECT TOP 2
                  id AS id_1
                , item_no
                , price
                , post_dt
                , post_tm
                , aud_action
            FROM iminvaud_sql
            WHERE item_no = '1-ADVENT ENGLISH'
                  AND aud_action = 'C'
            ORDER BY
                  post_dt DESC, post_tm DESC
      ) sq
ORDER BY
      post_dt ASC, post_tm ASC
;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Your query is essentially:

select s.*
from iminvaud_sql s
where s.item_no = '1-ADVENT ENGLISH' and s.aud_action in ('B','C') 
order by id desc;

As far as I can tell, the "B" and "C" are not adding any information. Instead, let's look at the first occurrence of the most recent price. I will base this on id. The following will work if the prices are monotonic (either always increasing or decreasing):

select top 1 s.*
from iminvaud_sql s
where exists (select 1
              from iminvaud_sql s2
              where s2.item_no = s.item_no and s2.aud_action in ('B','C') and s2.price = s.price
             ) and
      s.aud_action in ('B','C') and s.item_no = '1-ADVENT ENGLISH'
order by s.id_1 asc;

If this isn't the case, you can use a trick. The trick is to take the difference between row_number() and row_number() partitioned by price. The largest values for the difference will be the most recent price.

select top 1 s.*
from (select s.*,
             (row_number() over (order by id_1) -
              row_number() over (partition by price order by id_1)
             ) as pricegroup
      from iminvaud_sql s
      where s2.aud_action in ('B','C') and s.item_no = '1-ADVENT ENGLISH'
     ) s
order by price_group, s.id_1 asc;

Upvotes: 1

Ruslan Veselov
Ruslan Veselov

Reputation: 337

I added more data... what if this was the case.. If I order by date and time I would get 221 but the price didn't change – user867621 4 mins ago

If price didn't change, why do the system write that:

221 1-ADVENT ENGLISH                2.000000    2014-08-18 00:00:00.000 1900-01-01 10:19:35.113 C
218 1-ADVENT ENGLISH                2.000000    2014-08-18 00:00:00.000 1900-01-01 10:19:35.110 B

Upvotes: 1

Donal
Donal

Reputation: 32713

Not sure what your requirement is - but if you just want the top row - then use top:

select select top 1 id as id_1, item_no, price, post_dt, post_tm, aud_action 
from iminvaud_sql where item_no = '1-ADVENT ENGLISH              ' 
and aud_action in ('B','C') order by post_dt desc, post_tm desc

Upvotes: 1

Related Questions