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:

Table 2

How are your sorting your rows to identify the timeline of data changes? — iamdave, Jun 05 '17 at 09:30
Rows are sorted with date column which i did not included. I'll edit the table. — Rafał Kobiela, Jun 05 '17 at 09:31
What is your desired output? I am assuming you will want this across multiple `INDEX_ID` values? Do you want the `1` flag in just those rows that are a different price or a list of `INDEX_ID`s with a `Changed/Not Changed` flag? — iamdave, Jun 05 '17 at 09:36
Yes, i wat to do it with multiple INDEX_ID. I want 1 flag in rows where price is temporary lower. — Rafał Kobiela, Jun 05 '17 at 09:41
How is the 'Price to return to after a temporary change' defined? What if you had a row between your two `26.49` values that was `45`? — iamdave, Jun 05 '17 at 09:45

McNets · Accepted Answer · 2017-06-05T09:50:57.350

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

edited Jun 05 '17 at 09:50

answered Jun 05 '17 at 09:33

McNets

10,352
3
32
61

I am not sure this does what is asked. It shows where a price has changed, but not where a price has changed *and then changed back* to what it was originally. – iamdave Jun 05 '17 at 09:39
I don't think i will work. I have to check multiple rows because i want to check if tere are any preoids with temporary lower price. – Rafał Kobiela Jun 05 '17 at 09:39
Pelase, add your desired result, it's not clear to me. – McNets Jun 05 '17 at 09:41
I added the table with desired result. – Rafał Kobiela Jun 05 '17 at 09:50

Check values in all previous rows in SQL table

1 Answers1