T-SQL query to obtain prior price of an item, variance from current price and date of prior price

Declare @sec_temp table 
(
 sec_no varchar(10),
 amount money,
 price_date date
)

insert @sec_temp
values
    ('123ABC', 25, '2011-01-20'), 
    ('123ABC', 25, '2011-01-19'), 
    ('123ABC', 25, '2011-01-18'), 
    ('123ABC', 20, '2011-01-17'),     
    ('123ABC', 开发者_StackOverflow中文版20, '2011-01-15'),
    ('123ABC', 22, '2011-01-13'),
    ('456DEF', 22, '2011-01-13'),
    ('456DEF', 30, '2011-01-11')

Problem: T-SQL query to obtain prior price of an item, variance from current price and date of prior price. "Prior Price" is defined as the date when the amount for an item changed prior to the current amount

Result:

**sec_no   current_Amount    Current_Price_Date   No_of_days_at_Current_price   prior_amount    prior_price_date   No_of_days_at_prior_price** 
123ABC       20              2011-01-20                      19                    20              2011-01-15             3

456DEF       22              2011-01-13                      24                    30              2011-01-11             2

Current Code (gets the data for current_price_date and current_amount):

(Thanks to cyberkiwi)

select
 sec_no,
 amount,
 No_of_days_at_price = 1 + DATEDIFF(d, min(price_date), max(price_date))
from (
    select *,
  ROW_NUMBER() over (partition by sec_no order by price_date desc) rn,
  ROW_NUMBER() over (partition by sec_no, amount order by price_date desc) rn2
    from @sec_temp
) X
WHERE rn=rn2
group by sec_no, amount

---

select
 X.sec_no,
 Current_Amount = X.amount,
 Current_Price_Date = X.price_date,
 No_of_days_at_current_price = DATEDIFF(d, X.price_date, getdate()),
 prior_amount = Y.amount,
 prior_price_date = Y.price_date,
 No_of_days_at_prior_price = DATEDIFF(d, Y.price_date, X.price_date)
from (
  select *,
  ROW_NUMBER() over (partition by sec_no order by price_date desc) rn
  from @sec_temp
) X
outer apply (
    select top(1) b.* from @sec_temp b
    where b.price_date < X.price_date and b.amount != X.amount
    and b.sec_no = X.sec_no
    order by b.price_date desc
    ) Y
WHERE rn=1

---

Another solution:

WITH grouped AS (
  SELECT
    *,
    price_duration = max_price_date - min_price_date + 1,
    rownum = ROW_NUMBER() OVER (PARTITION BY sec_no ORDER BY max_price_date DESC)
  FROM (
    SELECT
      sec_no,
      amount,
      min_price_date = MIN(price_date),
      max_price_date = MAX(price_date)
    FROM @sec_temp
    GROUP BY sec_no, amount
  ) s
)
SELECT
  g1.sec_no,
  current_Amount =              g1.amount,
  Current_Price_Date =          g1.max_price_date,
  No_of_days_at_Current_price = g1.price_duration,
  prior_amount =                g2.amount,
  prior_price_date =            g2.min_date_time,
  No_of_days_at_prior_price =   g2.price_duration
FROM grouped AS g1
  LEFT JOIN grouped AS g2 ON g1.sec_no = g2.sec_no AND g2.rownum = 2
WHERE g1.rownum = 1
ORDER BY g1.sec_no

As you can see, number of days is calculated somewhat differently from how it is done in your current script. Basically it is similar, though, it just assigns the rownums after grouping, not before, as in your query. The different way has been chosen because the rownums are also used to join the prior price data with the current price data.