T-SQL query to get the number of days when an item was at the current price

Question

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-15'), ('123ABC', 22, '2011-01-13'), ('456DEF', 22, '2011-01-13') 

Task: list a single sec_no with the last price ( amount ) and the number of days that was in the current price. In this case

Result:

 sec_no amount no_of_days_at_price 123ABC 25 3 eg 01-18 to 01-20 456DEF 22 1 eg 01-13 

Answer 1

 select a.sec_no, a.amount, min(price_date) as FirstDateAtPrice, No_of_days_at_price = COALESCE(DATEDIFF(d, c.price_date, a.price_date),0) from ( select *, ROW_NUMBER() over (partition by sec_no order by price_date desc) rn from @sec_temp) a outer apply ( select top 1 * from @sec_temp b where a.sec_no=b.sec_no and a.amount <> b.amount order by b.price_date desc ) c where a.rn=1 

In subquery A, the largest-1-per-group is issued, that is, the most recent price record for each sec_no. Subquery C finds the first previous record that has a different price for the same sec_no. The difference in the two dates is the search for number of days . If you want it to be no previous date, change the end of the line COALESCE to 1 instead of 0.

EDITED to clarify the issue

To start counting from the first date equal to the current bid, use this query instead

 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 

AND FINALLY If the required result is actually days between

• The first date when the price is equal to the current; and
• Today

Then the only part that needs to be changed is this:

  No_of_days_at_price = 1 + DATEDIFF(d, min(price_date), getdate()) 

Answer 2

Here's one approach, first looking for the latest price, and then the last price that was different:

 select secs.sec_no , latest.amount as price , case when previous.price_date is null then 1 else datediff(day, previous.price_date, latest.price_date) end as days_at_price from ( select distinct sec_no from @sec_temp ) secs cross apply ( select top 1 amount , price_date from @sec_temp where sec_no = secs.sec_no order by price_date desc ) latest outer apply ( select top 1 price_date from @sec_temp where sec_no = secs.sec_no and amount <> latest.amount order by price_date desc ) previous 

Fingerprints:

 sec_no price days_at_price 123ABC 25,00 5 456DEF 22,00 1