With SQL partitions min and max values are easy to find, but how is Gain or Loss determined over a partition?

This brings in the time element to compare min and max. If max occurs later in time than min, that would be a "Gain". If min occurs later, that would be a "Loss".

How might the GainorLoss column be calculated?

CREATE TABLE Weights (id int, date date, person varchar(40), Weight int);

INSERT INTO Weights VALUES (1, '2022-09-01', 'Alice', 100); 
INSERT INTO Weights VALUES (2, '2022-10-01', 'Alice', 105); 
INSERT INTO Weights VALUES (3, '2022-11-01', 'Alice', 110); 
INSERT INTO Weights VALUES (4, '2022-12-01', 'Alice', 115); 
INSERT INTO Weights VALUES (5, '2022-09-01', 'Peter', 150); 
INSERT INTO Weights VALUES (6, '2022-10-01', 'Peter', 145); 
INSERT INTO Weights VALUES (7, '2022-11-01', 'Peter', 140); 
INSERT INTO Weights VALUES (8, '2022-12-01', 'Peter', 135);

select  
 person
 , date
 , weight
 , min(Weight) OVER (PARTITION BY person) as minWeight
 , max(Weight) OVER (PARTITION BY person) as maxWeight 
 --if max weight occurs after min weight, then "Gain" ELSE "Loss" AS GainorLoss
 from weights

Desired output:

CodePudding user response：

You can use FIRST_VALUE to get the first or the last value based on an order.

The example below calculates 2 GainOrLess results.
The 2nd is what you described. The 1st is what I think you want.
But with the current sample data they give same result.

select *
, CASE 
  WHEN LastWeight > FirstWeight THEN 'Gain' 
  WHEN LastWeight < FirstWeight THEN 'Loss' 
  ELSE 'Same' 
  END AS [GainOrLoss1]
, CASE 
  WHEN MaxWeightDate > MinWeightDate THEN 'Gain' 
  WHEN MaxWeightDate < MinWeightDate THEN 'Loss' 
  ELSE 'Same' 
  END AS [GainOrLoss2]
from 
(
select  
 person
 , [date] 
 , weight
 , FIRST_VALUE(Weight) OVER (PARTITION BY person ORDER BY [date], id) as FirstWeight
 , FIRST_VALUE(Weight) OVER (PARTITION BY person ORDER BY [date] DESC, id DESC) as LastWeight 
 , FIRST_VALUE([date]) OVER (PARTITION BY person ORDER BY Weight, id) as MinWeightDate
 , FIRST_VALUE([date]) OVER (PARTITION BY person ORDER BY Weight DESC, id DESC) as MaxWeightDate
 from weights
) q
ORDER BY person, [date]

person | date       | weight | FirstWeight | LastWeight | MinWeightDate | MaxWeightDate | GainOrLoss1 | GainOrLoss2
:----- | :--------- | -----: | ----------: | ---------: | :------------ | :------------ | :---------- | :----------
Alice  | 2022-09-01 |    100 |         100 |        115 | 2022-09-01    | 2022-12-01    | Gain        | Gain       
Alice  | 2022-10-01 |    105 |         100 |        115 | 2022-09-01    | 2022-12-01    | Gain        | Gain       
Alice  | 2022-11-01 |    110 |         100 |        115 | 2022-09-01    | 2022-12-01    | Gain        | Gain       
Alice  | 2022-12-01 |    115 |         100 |        115 | 2022-09-01    | 2022-12-01    | Gain        | Gain       
Peter  | 2022-09-01 |    150 |         150 |        135 | 2022-12-01    | 2022-09-01    | Loss        | Loss       
Peter  | 2022-10-01 |    145 |         150 |        135 | 2022-12-01    | 2022-09-01    | Loss        | Loss       
Peter  | 2022-11-01 |    140 |         150 |        135 | 2022-12-01    | 2022-09-01    | Loss        | Loss       
Peter  | 2022-12-01 |    135 |         150 |        135 | 2022-12-01    | 2022-09-01    | Loss        | Loss       

db<>fiddle here

CodePudding user response：

I think the result would be more accurate if you measure the original weight with the current weight.
Or you can measure the status from the prior weight and the current weight.
It's up to you to choose, or you could just use both methods, they can be combined off course.
And I also added a resulttype for when the weight did not changed, when there is no gain and no loss

This method uses the original weight and the current weight to determine the gain-or-loss status

declare @Weights TABLE (id int, [date] date, person varchar(40), Weight int)

INSERT INTO @Weights values 
(1, '2022-09-01', 'Alice', 100), (2, '2022-10-01', 'Alice', 105), 
(3, '2022-11-01', 'Alice', 110), (4, '2022-12-01', 'Alice', 115), 
(5, '2022-09-01', 'Peter', 150), (6, '2022-10-01', 'Peter', 145), 
(7, '2022-11-01', 'Peter', 140), (8, '2022-12-01', 'Peter', 135)


select t.person,
       t.[date],
       t.weight,
       t.minWeight,
       t.maxWeight,
       t.Original,
       case when t.Original > t.weight then 'Loss'
            when t.Original < t.weight then 'Gain'
            else 'Unchanged'
       end as GainOrLoss
from   ( select  w.person,
                 w.[date],
                 w.weight,
                 min(w.Weight) OVER (PARTITION BY w.person) as minWeight,
                 max(w.Weight) OVER (PARTITION BY w.person) as maxWeight,
                 FIRST_VALUE(w.Weight) OVER (PARTITION BY person ORDER BY [date], id) as Original
         from    @weights w
       ) t
  

with this result

-------|----------|--------|-----------|-----------|--------|---------
person |date      |weight  |minWeight  |maxWeight  |Original GainOrLoss
-------|----------|--------|-----------|-----------|--------|---------
Alice  |2022-09-01| 100    |100        |115        |100     |Unchanged
Alice  |2022-10-01| 105    |100        |115        |100     |Gain
Alice  |2022-11-01| 110    |100        |115        |100     |Gain
Alice  |2022-12-01| 115    |100        |115        |100     |Gain
Peter  |2022-09-01| 150    |135        |150        |150     |Unchanged
Peter  |2022-10-01| 145    |135        |150        |150     |Loss
Peter  |2022-11-01| 140    |135        |150        |150     |Loss
Peter  |2022-12-01| 135    |135        |150        |150     |Loss

You can test and change is in this DBFiddle

EDIT

If you want to see the weight changes with regards to the prior weight, you can use the LAG funtion.
In my option this would be the best method to follow up someone's weight change.

This method uses the prior weight and the current weight to determine the gain-or-loss status

select t.person,
       t.[date],
       t.priorWeight,
       t.weight,
       case when t.priorWeight > t.weight then 'Loss'
            when t.priorWeight < t.weight then 'Gain'
            else 'nochange'
       end as GainOrLoss,
       t.minWeight,
       t.maxWeight
from   ( select  w.person,
                 w.[date],
                 w.weight,
                 min(w.Weight) OVER (PARTITION BY w.person) as minWeight,
                 max(w.Weight) OVER (PARTITION BY w.person) as maxWeight,
                 lag(w.Weight) over (partition by person order by [date]) as priorWeight
         from    @weights w
       ) t

-------|----------|------------|-------|----------|---------|---------
person |date      |priorWeight |weight |GainOrLoss|minWeight|maxWeight
-------|----------|------------|-------|----------|---------|---------
Alice  |2022-09-01|            |100    |nochange  |100      |115
Alice  |2022-10-01|100         |105    |Gain      |100      |115
Alice  |2022-11-01|105         |110    |Gain      |100      |115
Alice  |2022-12-01|110         |115    |Gain      |100      |115
Peter  |2022-09-01|            |150    |nochange  |135      |150
Peter  |2022-10-01|150         |145    |Loss      |135      |150
Peter  |2022-11-01|145         |140    |Loss      |135      |150
Peter  |2022-12-01|140         |135    |Loss      |135      |150

And again a DBFiddle you can use to play with this query

You can combine the 2 methods offcourse