How to determine a percentage gain or loss for a series of people in a table in SQL

I’ve posted a few questions about a system I’m trying to write, and feel bad for asking so many questions. However, I’m still quite new (or revisiting from years ago) to the finer elements of SQL Server, so please forgive and help me!

I have imported data from a JSON into a table, which has a whole host of different information contained within it, one of which being a person’s weight recorded over time. I have the following code to produce this:

SELECT ServiceUserID, PersonID, ServiceUser, DateDone, [Weight]
FROM
(    SELECT ServiceUserID, PersonID, ServiceUser, DateDone, LEFT(SUBSTRING(cn.Fragment, PATINDEX('%[0-9.-]%', cn.Fragment), 8000),
           PATINDEX('%[^0-9.-]%', SUBSTRING(cn.Fragment, PATINDEX('%[0-9.-]%', cn.Fragment), 8000) + 'X') -1) AS [Weight], ra.ScoreMeaning
FROM [JSONCareNotes-HOME] cn 

LEFT OUTER JOIN [JSONRAs-HOME] ra ON ra.PersonID = cn.ServiceUserID

WHERE cn.SliderData LIKE '%weigh%'
and cn.Fragment NOT LIKE '%weighed%'
) AS SubQueryAlias

WHERE NOT ([Weight] IS NULL OR [Weight] = ' ')
ORDER BY ServiceUser, DateDone DESC

What I get from this is (a sample):

7e54d569-0ad2-43a9-aada-5f37480fdfd4    NULL    Resident 1      2019-11-16  83.1
19e0d4b1-8fe4-4480-b99b-5d067123121e    NULL    Resident 2      2019-11-16  63.8
ba2a406f-2e28-4186-a5a0-95d93bb20ca9    NULL    Resident 3      2019-11-10  54
ecfa663b-3dd2-4aef-b25c-e43dd6b82ebb    NULL    Resident 4      2019-11-16  81.5
8a5880af-1d7f-4b96-ae28-4de4fb1a4685    NULL    Resident 5      2019-11-28  60.9
8a5880af-1d7f-4b96-ae28-4de4fb1a4685    NULL    Resident 5      2019-11-21  61
8a5880af-1d7f-4b96-ae28-4de4fb1a4685    NULL    Resident 5      2019-11-01  62.05
91495cd7-054d-47d5-9be0-dd3123342f19    NULL    Resident 6      2019-11-16  52.9
be79adef-01c2-4fe9-86b1-9d42fe9fe012    NULL    Resident 7      2019-11-28  45.8

I would like then the following output, for the sake of an example, Resident 5:

Resident Name   Date        Value   %age Change
Resident 5      2019-11-01  62.05   n/a              
                2019-11-21  61      -1.69 
                2019-11-28  60.9    -1.85

Ultimately, I’m wanting to be able to say from the first date, or from a date entered via a variable to either the last date or another defined end date variable, what the percentage change has been for each resident in the list. Most residents will have numerous entries, I’ve just tried to give an example of one.

If this was in something like Delphi (old school, I know) then this would be relatively easy. However, for clarity reasons I’m trying to do as much of the processing in SQL Server. Can anyone offer any guidance? I’m aware of using LAG to perhaps find the previous row to use as a compare, but I’m finding it hard to get my head around going through the entire table for EACH resident at a time, and then generating the sort of list above. Or, is it better to just go through the entire table, and running a tally for each person (which would seem to be a much more complicated route).

Thanks Ant

EDIT (20/12)

Thanks to @Zhorov, I am now very close. However, whereas before I did seem to be getting accurate results up to 10 entries before a div/0 came along, I am now getting wild scores and I’m sure it’s because I’ve messed up along the way of implementing your suggestions. The code now is

;WITH cte AS (SELECT ServiceUserID, ra.PersonID, ServiceUser, cn.DateDone, TRY_CAST((LEFT(SUBSTRING(cn.Fragment, PATINDEX('%[0-9.-]%', cn.Fragment), 8000),
           PATINDEX('%[^0-9.-]%', SUBSTRING(cn.Fragment, PATINDEX('%[0-9.-]%', cn.Fragment), 8000) + 'X') -1)) AS REAL) AS [Weight], ra.ScoreMeaning
FROM [JSONCareNotes-HOME] cn 
LEFT OUTER JOIN [JSONRAs-HOME] ra ON ra.PersonID = cn.ServiceUserID
WHERE cn.SliderData LIKE '%weigh%'
AND cn.Fragment NOT LIKE '%weighed%'
)     
SELECT 
    ServiceUser,
    DateDone,
    [Weight],
    CASE 
       WHEN FIRST_VALUE([Weight] ) OVER (PARTITION BY [ServiceUser] ORDER BY DateDone ASC) = 0 THEN 0
       ELSE CONVERT(
               numeric(10, 2),
               [Weight] - FIRST_VALUE([Weight]) OVER (PARTITION BY [ServiceUser] ORDER BY DateDone ASC) / 
               FIRST_VALUE([Weight]) OVER (PARTITION BY [ServiceUser] ORDER BY DateDone ASC) * 
               100.0
            ) 
    END
    AS [Percentage]
FROM cte 
WHERE NOT([Weight] IS NULL) OR [Weight] = ' '
ORDER BY ServiceUser, DateDone ASC

This gives me the following results:

Resident 1  2019-11-16  83.1    -16.90
Resident 2  2019-11-16  63.8    -36.20
Resident 3  2019-11-10  54      -46.00
Resident 4  2019-11-16  81.5    -18.50
Resident 5  2019-11-01  62.05   -37.95
Resident 5  2019-11-21  61      -39.00
Resident 5  2019-11-28  60.9    -39.10
Resident 6  2019-11-16  52.9    -47.10
Resident 7  2019-11-14  40      -60.00
Resident 7  2019-11-21  42      -58.00
Resident 7  2019-11-28  45.8    -54.20
Resident 8  2019-11-21  48.1    -51.90
Resident 8  2019-11-28  50.2    -49.80

I’m missing something somewhere! Can anyone spot it for me? I think it’s not resetting for each new resident?

Thanks

Answer

It’s a complicated example, but if I understand you correctly, the next approach, using FIRST_VALUE() and percentage calculations may help here:

Sample data:

CREATE TABLE Data (
    ServiceUser nvarchar(50), 
    DateDone date, 
    [Weight] numeric(10, 2)
)
INSERT INTO Data 
    (ServiceUser, DateDone, [Weight])
VALUES
    (N'Resident 1', '20191116', 83.1),
    (N'Resident 2', '20191116', 63.8),
    (N'Resident 3', '20191110', 54),
    (N'Resident 4', '20191116', 81.5),
    (N'Resident 5', '20191128', 60.9),
    (N'Resident 5', '20191121', 61),
    (N'Resident 5', '20191101', 62.05),
    (N'Resident 6', '20191116', 52.9),
    (N'Resident 7', '20191128', 45.8)

Statement:

;WITH cte AS (
    SELECT * FROM Data
    -- OR your complex statement 
)
SELECT 
    ServiceUser,
    DateDone,
    [Weight],
    CASE 
       WHEN FIRST_VALUE([Weight]) OVER (PARTITION BY [ServiceUser] ORDER BY DateDone ASC) = 0 THEN 0
       ELSE CONVERT(
               numeric(10, 2),
               ([Weight] - FIRST_VALUE([Weight]) OVER (PARTITION BY [ServiceUser] ORDER BY DateDone ASC)) / 
               FIRST_VALUE([Weight]) OVER (PARTITION BY [ServiceUser] ORDER BY DateDone ASC) * 
               100.0
            ) 
    END AS [Percentage]
FROM cte    

Results:

------------------------------------------
ServiceUser DateDone    Weight  Percentage
------------------------------------------
Resident 1  2019-11-16  83.10    0.00
Resident 2  2019-11-16  63.80    0.00
Resident 3  2019-11-10  54.00    0.00
Resident 4  2019-11-16  81.50    0.00
Resident 5  2019-11-01  62.05    0.00
Resident 5  2019-11-21  61.00   -1.69
Resident 5  2019-11-28  60.90   -1.85
Resident 6  2019-11-16  52.90    0.00
Resident 7  2019-11-28  45.80    0.00

Update:

It seems, that your [Weight] column holds numbers as text. Try to convert these values with TRY_CONVERT() (numeric(10, 2) is just a test data type, use an appropriate numeric data type). One possible approach is:

SELECT ServiceUserID, PersonID, ServiceUser, DateDone, [Weight]
FROM (
    SELECT 
        ServiceUserID, 
        PersonID, 
        ServiceUser, 
        DateDone, 
        TRY_CONVERT(
            numeric(10, 2),
            LEFT(SUBSTRING(cn.Fragment, PATINDEX('%[0-9.-]%', cn.Fragment), 8000), PATINDEX('%[^0-9.-]%', SUBSTRING(cn.Fragment, PATINDEX('%[0-9.-]%', cn.Fragment), 8000) + 'X') -1)
        ) AS [Weight], 
        ra.ScoreMeaning
    FROM [JSONCareNotes-HOME] cn 
    LEFT OUTER JOIN [JSONRAs-HOME] ra ON ra.PersonID = cn.ServiceUserID
    WHERE cn.SliderData LIKE '%weigh%' and cn.Fragment NOT LIKE '%weighed%'
    ) AS SubQueryAlias
WHERE [Weight] IS NOT NULL
ORDER BY ServiceUser, DateDone DESC