code, Sql Server - rextester

Language: Layout:

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

GO
/****** Object:  Table [dbo].[exampleTx]    Script Date: 1/2/2017 1:37:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[exampleTx](
	[userId] [int] NOT NULL,
	[year] [int] NULL,
	[month] [int] NULL,
	[monthname] [nvarchar](30) NULL,
	[name] [varchar](100) NOT NULL,
	[surname] [varchar](100) NOT NULL,
	[annualCapping] [money] NOT NULL,
	[amount] [money] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (1, 2015, 1, N'January', N'Joe', N'Black', 500.0000, 40.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (1, 2015, 2, N'February', N'Joe', N'Black', 500.0000, 40.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (1, 2015, 3, N'March', N'Joe', N'Black', 500.0000, 40.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (1, 2015, 4, N'April', N'Joe', N'Black', 500.0000, 40.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (1, 2015, 5, N'May', N'Joe', N'Black', 500.0000, 40.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (1, 2015, 6, N'June', N'Joe', N'Black', 500.0000, 40.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (1, 2015, 7, N'July', N'Joe', N'Black', 500.0000, 40.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (1, 2015, 8, N'August', N'Joe', N'Black', 500.0000, 40.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (1, 2015, 9, N'September', N'Joe', N'Black', 1000.0000, 40.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (1, 2015, 10, N'October', N'Joe', N'Black', 1000.0000, 40.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (1, 2015, 11, N'November', N'Joe', N'Black', 1000.0000, 40.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (1, 2015, 12, N'December', N'Joe', N'Black', 1000.0000, 40.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (1, 2016, 1, N'January', N'Joe', N'Black', 1000.0000, 150.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (1, 2016, 2, N'February', N'Joe', N'Black', 1000.0000, 30.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (1, 2016, 3, N'March', N'Joe', N'Black', 1000.0000, 40.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (2, 2015, 1, N'January', N'Paul', N'White', 800.0000, 40.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (2, 2015, 2, N'February', N'Paul', N'White', 800.0000, 40.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (2, 2015, 3, N'March', N'Paul', N'White', 800.0000, 40.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (2, 2015, 4, N'April', N'Paul', N'White', 800.0000, 40.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (2, 2015, 5, N'May', N'Paul', N'White', 800.0000, 40.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (2, 2015, 6, N'June', N'Paul', N'White', 800.0000, 40.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (2, 2015, 7, N'July', N'Paul', N'White', 800.0000, 40.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (2, 2015, 8, N'August', N'Paul', N'White', 800.0000, 40.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (2, 2016, 1, N'January', N'Paul', N'White', 800.0000, 99.9000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (2, 2016, 2, N'February', N'Paul', N'White', 800.0000, 10.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (2, 2016, 3, N'March', N'Paul', N'White', 800.0000, 20.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (2, 2016, 4, N'April', N'Paul', N'White', 800.0000, 30.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (2, 2016, 5, N'May', N'Paul', N'White', 800.0000, 40.0000)
GO
INSERT [dbo].[exampleTx] ([userId], [year], [month], [monthname], [name], [surname], [annualCapping], [amount]) VALUES (2, 2016, 6, N'June', N'Paul', N'White', 800.0000, 40.0000)
GO

SELECT *,
       RemainingBalance = COALESCE(new_amt, AnnualCapping) - Sum(amount)OVER (partition BY userid, year ORDER BY month)
FROM   exampleTx a
       LEFT JOIN (SELECT Min(month)                               min_month,
                         Min(annualcapping) * ( Count(1) / 12.0 ) AS new_amt
                  FROM   exampleTx
                  WHERE  month >= 9) b
              ON a.month >= b.min_month
WHERE  userid = 1
       AND year = 2015

View schema
Execution time: 0,05 sec, rows selected: 12, rows affected: 29, absolute service time: 0,19 sec