Allowance, Sql Server

Language: Layout:

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

select @@version as 'sql server version'

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 = AnnualCapping - SUM(amount) OVER (
partition  by userid ,year, annualcapping ORDER BY userid, year,month) 
 from
 exampleTx
 where userid = 1 and year = 2015

View schema
Execution time: 0,03 sec, rows selected: 13, rows affected: 29, absolute service time: 0,19 sec