Check values in all previous rows in SQL table

Question

I have the following SQL table:

declare @t table(START_DATE datetime,INDEX_ID int, GROSS_SALES_PRICE decimal(10,2));
insert into @t values
 ('20150619',10000410,38)
,('20170311',10000410,26.49)
,('20170312',10000410,26.49)
,('20170317',10000410,38)
,('20170318',10000410,38)
,('20170321',10000410,38);

I would like to check if there are any temporary changes in GROSS_SALES_PRICE

For example, in this table I have a price 38, then I have two rows with 26.49 and then 38 again. There can be any number of lower price rows so I think I have to check multiple previous rows?

I would like to make third column with value of 1 if this situation happened:

Answer 1

WITH maxPrice AS
(
    SELECT INDEX_ID, MAX(GROSS_SALES_PRICE) MaxPrice
    FROM   @t
    GROUP BY INDEX_ID
)
SELECT t.INDEX_ID, t.GROSS_SALES_PRICE,
       CASE WHEN GROSS_SALES_PRICE < maxPrice.MaxPrice THEN 1 ELSE 0 END AS [CHANGED]
FROM   @t t
INNER JOIN maxPrice
ON    maxPrice.INDEX_ID = t.INDEX_ID;
GO

INDEX_ID | GROSS_SALES_PRICE | CHANGED
-------: | :---------------- | ------:
10000410 | 38.00             |       0
10000410 | 26.49             |       1
10000410 | 26.49             |       1
10000410 | 38.00             |       0
10000410 | 38.00             |       0
10000410 | 38.00             |       0

dbfiddle here