1

As the title says i want to display only rows where we see that the attribute "Value" changes compared to previous rows

SELECT   * 
            
    FROM Therapy
    
WHERE Therapy.DataTypeId = @PumpTherapyDataType
    AND Therapy.ConsultId = 5
    AND Therapy.Is_Active = 1

this is the result set of the query below:

Id          Hours   TherapyType     Value   Is_Active
            
9704        24      1               0.5     true    
9705        1       1               0.5     true    
9706        2       1               0.5     true    
9707        1       1               0.5     true    
9708        4       1               0.6     true    
9709        5       1               0.75    true    
97010       8       1               0.75    true

and this is the expected result

Id          Hours   TherapyType     Value   Is_Active
            
9704        24      1               0.5     true    
9708        4       1               0.6     true    
9709        5       1               0.75    true
JohnnyJoe
  • 15
  • 5

2 Answers2

1

You want to use lag(). I'm not sure how "previous" is defined. Assuming it is based on id:

select t.*
from (select t.*,
             lag(value) over (order by id) as prev_value
      from therapy t
     ) t
where prev_value is null or prev_value <> value;

Here is a db<>fiddle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • first of all thank you, and 2 questions. Is it really necessary to rename therapy as t inside that subquery? And this "lag(value)..." needs to be t.value or it can be therapy.value? . Because you are renaming it inside and outside the subquery as t – JohnnyJoe Feb 19 '21 at 23:00
  • (1) Not necessary. Just convenient. (2) You need to qualify the name with the alias for the table. If you don't define an alias, then the table name is used as the default alias. – Gordon Linoff Feb 19 '21 at 23:05
  • makes sense and the .....prev_value <> value . It always recognizes the "value" outside of the sub-query becasue Therapy inside the subquery is renamed to t as well as the subquery itself? thats why it recognizes the "value" outsisde of it? Im just kinda messed up with the renaming here – JohnnyJoe Feb 19 '21 at 23:11
  • @JohnnyJoe . . . The columns are not qualified and there is only one table referenced in the query. There is no ambiguity. – Gordon Linoff Feb 19 '21 at 23:12
  • i get the error "String was not recognized as a valid Boolean" maybe something about the subquery? EDIT: Yes it's the subquery giving me that error – JohnnyJoe Feb 19 '21 at 23:26
  • @JohnnyJoe . . . I added a db<>fiddle. to illustrate the solution. – Gordon Linoff Feb 19 '21 at 23:32
1

here is it functionally

;WITH CTE AS (
SELECT *, prev = LAG([Value] , 1 , 0) OVER(Order By Id)
FROM Therapy
)
SELECT CTE.Id , CTE.Hours , CTE.TherapyType , CTE.Value , CTE.Is_Active FROM CTE
WHERE prev != Value
Karcan
  • 173
  • 1
  • 5