Including previous rows in query calculation

Question

I have a query I'm putting together, and need to compare the % difference in Weight for Clients across the past 1 month and past 6 months.

For example comparing for 1 month between these two values below. The latest entry for that client and the most recent entry in the previous month. If there is no entry in the previous month, then N/A would be shown.

16/07/2017 19:00    63.08
18/06/2017 15:00    62.54

The % difference is 0.8634473936680511.

For 6 month between these two values below. The latest entry for that client and the most recent entry in the month 6 months ago. If there is no entry in month 6 months ago, then N/A would be shown.

16/07/2017 19:00    63.08
19/02/2017 13:45    61.5

The % difference is 3.3251433251433276

and basically repeating that for each weight entry through-out the list to calculate the 1 and 6 month difference.

ClientId    DateOccurred                Weight
5037        2017-07-16 19:00:00.000     63.08
5037        2017-07-03 16:15:00.000     61.86
5037        2017-06-18 15:00:00.000     62.54
5037        2017-06-05 16:25:00.000     62.20
5037        2017-05-21 15:00:00.000     61.88
5037        2017-05-14 14:30:00.000     62.54
5037        2017-05-07 15:30:00.000     62.04
5037        2017-04-30 16:30:00.000     61.62
5037        2017-04-23 16:10:00.000     62.42
5037        2017-04-16 16:30:00.000     61.96
5037        2017-04-09 14:57:00.000     62.30
5037        2017-04-02 18:45:00.000     61.50
5037        2017-03-26 15:14:00.000     61.22
5037        2017-03-12 15:05:00.000     61.06
5037        2017-03-05 19:30:00.000     61.20
5037        2017-02-19 13:45:00.000     61.50
5037        2017-02-12 15:35:00.000     61.56
5037        2017-01-29 10:10:00.000     59.68
5037        2017-01-22 16:00:00.000     60.50
5037        2017-01-15 13:30:00.000     60.80
5037        2017-01-08 18:00:00.000     64.08
5037        2017-01-01 14:05:00.000     62.62
5037        2016-12-18 15:40:00.000     63.26
5037        2016-12-11 14:27:00.000     62.80
5037        2016-12-04 19:00:00.000     62.14
5037        2016-11-20 15:25:00.000     61.26
5037        2016-11-06 19:00:00.000     61.72
5037        2016-10-30 13:30:00.000     60.82
5037        2016-10-23 16:05:00.000     61.44
5037        2016-10-17 15:32:00.000     60.48
5037        2016-10-09 18:49:00.000     59.80
5037        2016-10-02 13:30:00.000     59.00
5037        2016-09-25 11:23:00.000     59.60
5037        2016-09-11 13:58:00.000     58.38
5037        2016-09-04 16:00:00.000     60.23
5037        2016-08-28 16:25:00.000     59.46
5037        2016-08-21 14:45:00.000     61.48
5037        2016-08-14 18:20:00.000     61.04
5037        2016-07-24 13:58:00.000     60.28
5037        2016-07-17 15:00:00.000     59.96
5037        2016-07-10 13:45:00.000     60.34
5037        2016-07-03 14:12:00.000     59.70
5037        2016-06-26 14:45:00.000     58.92
5037        2016-06-20 18:45:00.000     53.40
5037        2016-06-06 12:48:00.000     58.52

CREATE TABLE [dbo].[WeightEntries](
[ResidentId] [int] NULL,
[DateOccurred] [datetime] NULL,
[Weight] [decimal](6, 2) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A61D00D2F000 AS DateTime), CAST(58.52 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A62B0134FD90 AS DateTime), CAST(53.40 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A63100F31290 AS DateTime), CAST(58.92 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A63800EA0240 AS DateTime), CAST(59.70 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A63F00E297D0 AS DateTime), CAST(60.34 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A64600F73140 AS DateTime), CAST(59.96 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A64D00E629E0 AS DateTime), CAST(60.28 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A662012E1FC0 AS DateTime), CAST(61.04 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A66900F31290 AS DateTime), CAST(61.48 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A670010E89D0 AS DateTime), CAST(59.46 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A6770107AC00 AS DateTime), CAST(60.23 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A67E00E629E0 AS DateTime), CAST(58.38 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A68C00BB9770 AS DateTime), CAST(59.60 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A69300DE7920 AS DateTime), CAST(59.00 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A69A013616D0 AS DateTime), CAST(59.80 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A6A200FFFB40 AS DateTime), CAST(60.48 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A6A801090B90 AS DateTime), CAST(61.44 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A6AF00DE7920 AS DateTime), CAST(60.82 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A6B601391C40 AS DateTime), CAST(61.72 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A6C400FE0F10 AS DateTime), CAST(61.26 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A6D201391C40 AS DateTime), CAST(62.14 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A6D900EE20F0 AS DateTime), CAST(62.80 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A6E001022DC0 AS DateTime), CAST(63.26 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A6EE00E81610 AS DateTime), CAST(62.62 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A6F50128A180 AS DateTime), CAST(64.08 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A6FC00DE7920 AS DateTime), CAST(60.80 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A7030107AC00 AS DateTime), CAST(60.50 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A70A00A78AA0 AS DateTime), CAST(59.68 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A7180100CE30 AS DateTime), CAST(61.56 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A71F00E297D0 AS DateTime), CAST(61.50 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A72D014159A0 AS DateTime), CAST(61.20 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A73400F890D0 AS DateTime), CAST(61.06 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A74200FB09A0 AS DateTime), CAST(61.22 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A7490134FD90 AS DateTime), CAST(61.50 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A75000F65E50 AS DateTime), CAST(62.30 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A757010FE960 AS DateTime), CAST(61.96 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A75E010A6B20 AS DateTime), CAST(62.42 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A765010FE960 AS DateTime), CAST(61.62 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A76C00FF6EA0 AS DateTime), CAST(62.04 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A77300EEF3E0 AS DateTime), CAST(62.54 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A77A00F73140 AS DateTime), CAST(61.88 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A789010E89D0 AS DateTime), CAST(62.20 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A79600F73140 AS DateTime), CAST(62.54 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A7A5010BCAB0 AS DateTime), CAST(61.86 AS Decimal(6, 2)))
INSERT [dbo].[WeightEntries] ([ResidentId], [DateOccurred], [Weight]) VALUES (5037, CAST(0x0000A7B201391C40 AS DateTime), CAST(63.08 AS Decimal(6, 2)))

Answer 1

One way you could do this is with a CTE using a partitioned ROW_NUMBER() to determine the most recent entry for each month and some subqueries to determine the difference at certain intervals. For example:

WITH CTE(ResidentId, DateOccurred, [Weight]) AS
(
    SELECT ResidentId, DateOccurred, [Weight]
    FROM 
    (
        SELECT *, LatestEachMonth = ROW_NUMBER() OVER (PARTITION BY ResidentId, DATEDIFF(MONTH, 0, DateOccurred) ORDER BY DateOccurred DESC)
        FROM WeightEntries
    ) AS T
    WHERE LatestEachMonth = 1
)
SELECT ResidentId,
    DateOccurred,
    [Weight],
    OneMonthPercentage = 100.0 * [Weight] / (SELECT [Weight] FROM CTE WHERE ResidentId = C.ResidentId AND DATEDIFF(MONTH, DateOccurred, C.DateOccurred) = 1) - 100,
    SixMonthPercentage = 100.0 * [Weight] / (SELECT [Weight] FROM CTE WHERE ResidentId = C.ResidentId AND DATEDIFF(MONTH, DateOccurred, C.DateOccurred) = 6) - 100
FROM CTE AS C;