This 
/****** Object: Table [dbo].[RetrieveTrialBalanceTEST] Script Date: 29/12/2021 17:04:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RetrieveTrialBalanceTEST](
[Company] [int] NOT NULL,
[MainAccount] [int] NOT NULL,
[PortFolioProject] [int] NULL,
[TransactionCurrency] [nvarchar](3) NOT NULL,
[AccountingOpeningBalance] [numeric](38, 6) NULL,
[AccountingNetChangeAmount] [numeric](38, 6) NULL,
[TransactionOpeningBalance] [numeric](38, 6) NULL,
[TransactionNetChangeAmount] [numeric](38, 6) NULL,
[Month] [int] NULL,
[Period] [nvarchar](4000) NULL,
[Year] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'AUD', CAST(-141194.000000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-205000.000000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), 1, N'JAN', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'BMD', CAST(-27255.140000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-27255.140000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), 1, N'JAN', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'CAD', CAST(-2428.080000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-3220.000000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), 1, N'JAN', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'CHF', CAST(-220514.900000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-210500.000000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), 1, N'JAN', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'EUR', CAST(-1213310.030000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-1084078.320000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), 1, N'JAN', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'GBP', CAST(-2449684.590000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-1895760.520000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), 1, N'JAN', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'USD', CAST(8235414.760000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(8235414.760000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), 1, N'JAN', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 20698, N'USD', CAST(-3162799.570000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-3162799.570000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), 1, N'JAN', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 20701, N'USD', CAST(-8538.750000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-8538.750000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), 1, N'JAN', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 20706, N'CAD', CAST(-9189.700000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-12000.000000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), 1, N'JAN', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'AUD', CAST(0.000000 AS Numeric(38, 6)), CAST(-17038.670000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-22378.080000 AS Numeric(38, 6)), 4, N'APR', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'AUD', CAST(0.000000 AS Numeric(38, 6)), CAST(-115.330000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-148.800000 AS Numeric(38, 6)), 5, N'MAY', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'AUD', CAST(0.000000 AS Numeric(38, 6)), CAST(-16453.140000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-21946.300000 AS Numeric(38, 6)), 7, N'JUL', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'AUD', CAST(0.000000 AS Numeric(38, 6)), CAST(-72603.980000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-100100.880000 AS Numeric(38, 6)), 8, N'AUG', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'BMD', CAST(0.000000 AS Numeric(38, 6)), CAST(-39.500000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-39.500000 AS Numeric(38, 6)), 5, N'MAY', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'CAD', CAST(0.000000 AS Numeric(38, 6)), CAST(-16671.250000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-20634.560000 AS Numeric(38, 6)), 4, N'APR', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'CAD', CAST(0.000000 AS Numeric(38, 6)), CAST(-27757.730000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-33660.000000 AS Numeric(38, 6)), 6, N'JUN', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'CAD', CAST(0.000000 AS Numeric(38, 6)), CAST(-13185.850000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-16632.000000 AS Numeric(38, 6)), 7, N'JUL', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'CHF', CAST(0.000000 AS Numeric(38, 6)), CAST(-13370.400000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-12000.000000 AS Numeric(38, 6)), 2, N'FEB', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'CHF', CAST(0.000000 AS Numeric(38, 6)), CAST(-5417.500000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-5000.000000 AS Numeric(38, 6)), 4, N'APR', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'CHF', CAST(0.000000 AS Numeric(38, 6)), CAST(-84489.200000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-76000.000000 AS Numeric(38, 6)), 6, N'JUN', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'CHF', CAST(0.000000 AS Numeric(38, 6)), CAST(-10898.000000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-10000.000000 AS Numeric(38, 6)), 7, N'JUL', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'CHF', CAST(0.000000 AS Numeric(38, 6)), CAST(-34316.100000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-31500.000000 AS Numeric(38, 6)), 8, N'AUG', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'EUR', CAST(0.000000 AS Numeric(38, 6)), CAST(-60065.090000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-49407.820000 AS Numeric(38, 6)), 1, N'JAN', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'EUR', CAST(0.000000 AS Numeric(38, 6)), CAST(-60479.040000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-49863.170000 AS Numeric(38, 6)), 2, N'FEB', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'EUR', CAST(0.000000 AS Numeric(38, 6)), CAST(-12680.530000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-10630.000000 AS Numeric(38, 6)), 3, N'MAR', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'EUR', CAST(0.000000 AS Numeric(38, 6)), CAST(-147617.210000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-124488.790000 AS Numeric(38, 6)), 4, N'APR', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'EUR', CAST(0.000000 AS Numeric(38, 6)), CAST(-60692.420000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-49850.040000 AS Numeric(38, 6)), 5, N'MAY', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'EUR', CAST(0.000000 AS Numeric(38, 6)), CAST(-72969.800000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-59846.750000 AS Numeric(38, 6)), 6, N'JUN', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'EUR', CAST(0.000000 AS Numeric(38, 6)), CAST(-67855.430000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-57475.380000 AS Numeric(38, 6)), 7, N'JUL', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'EUR', CAST(0.000000 AS Numeric(38, 6)), CAST(-89161.800000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-75624.430000 AS Numeric(38, 6)), 8, N'AUG', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'GBP', CAST(0.000000 AS Numeric(38, 6)), CAST(-358602.930000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-263658.790000 AS Numeric(38, 6)), 1, N'JAN', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'GBP', CAST(0.000000 AS Numeric(38, 6)), CAST(-342658.990000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-246690.900000 AS Numeric(38, 6)), 2, N'FEB', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'GBP', CAST(0.000000 AS Numeric(38, 6)), CAST(-24548.700000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-17629.940000 AS Numeric(38, 6)), 3, N'MAR', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'GBP', CAST(0.000000 AS Numeric(38, 6)), CAST(-22602.180000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-16337.060000 AS Numeric(38, 6)), 4, N'APR', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'GBP', CAST(0.000000 AS Numeric(38, 6)), CAST(-408572.870000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-289051.220000 AS Numeric(38, 6)), 5, N'MAY', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'GBP', CAST(0.000000 AS Numeric(38, 6)), CAST(-81834.950000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-58166.600000 AS Numeric(38, 6)), 6, N'JUN', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'GBP', CAST(0.000000 AS Numeric(38, 6)), CAST(-656762.450000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-476738.700000 AS Numeric(38, 6)), 7, N'JUL', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'GBP', CAST(0.000000 AS Numeric(38, 6)), CAST(-184405.370000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-134115.420000 AS Numeric(38, 6)), 8, N'AUG', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'USD', CAST(0.000000 AS Numeric(38, 6)), CAST(426074.270000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(426074.270000 AS Numeric(38, 6)), 1, N'JAN', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'USD', CAST(0.000000 AS Numeric(38, 6)), CAST(466995.850000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(466995.850000 AS Numeric(38, 6)), 2, N'FEB', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'USD', CAST(0.000000 AS Numeric(38, 6)), CAST(42436.370000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(42436.370000 AS Numeric(38, 6)), 3, N'MAR', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'USD', CAST(0.000000 AS Numeric(38, 6)), CAST(211274.660000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(211274.660000 AS Numeric(38, 6)), 4, N'APR', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'USD', CAST(0.000000 AS Numeric(38, 6)), CAST(467332.520000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(467332.520000 AS Numeric(38, 6)), 5, N'MAY', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'USD', CAST(0.000000 AS Numeric(38, 6)), CAST(324603.360000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(324603.360000 AS Numeric(38, 6)), 6, N'JUN', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'USD', CAST(0.000000 AS Numeric(38, 6)), CAST(908661.630000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(908661.630000 AS Numeric(38, 6)), 7, N'JUL', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 696, N'USD', CAST(0.000000 AS Numeric(38, 6)), CAST(523337.300000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(523337.300000 AS Numeric(38, 6)), 8, N'AUG', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 20698, N'USD', CAST(0.000000 AS Numeric(38, 6)), CAST(-7406.250000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-7406.250000 AS Numeric(38, 6)), 1, N'JAN', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 20698, N'USD', CAST(0.000000 AS Numeric(38, 6)), CAST(-50487.420000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-50487.420000 AS Numeric(38, 6)), 2, N'FEB', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 20698, N'USD', CAST(0.000000 AS Numeric(38, 6)), CAST(-4525.400000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-4525.400000 AS Numeric(38, 6)), 3, N'MAR', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 20698, N'USD', CAST(0.000000 AS Numeric(38, 6)), CAST(-1927.850000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-1927.850000 AS Numeric(38, 6)), 4, N'APR', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 20698, N'USD', CAST(0.000000 AS Numeric(38, 6)), CAST(-55464.080000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-55464.080000 AS Numeric(38, 6)), 6, N'JUN', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 20698, N'USD', CAST(0.000000 AS Numeric(38, 6)), CAST(-143506.760000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-143506.760000 AS Numeric(38, 6)), 7, N'JUL', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 20698, N'USD', CAST(0.000000 AS Numeric(38, 6)), CAST(-142850.050000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-142850.050000 AS Numeric(38, 6)), 8, N'AUG', 2021)
GO
INSERT [dbo].[RetrieveTrialBalanceTEST] ([Company], [MainAccount], [PortFolioProject], [TransactionCurrency], [AccountingOpeningBalance], [AccountingNetChangeAmount], [TransactionOpeningBalance], [TransactionNetChangeAmount], [Month], [Period], [Year]) VALUES (1234, 123456, 20701, N'USD', CAST(0.000000 AS Numeric(38, 6)), CAST(-681.740000 AS Numeric(38, 6)), CAST(0.000000 AS Numeric(38, 6)), CAST(-681.740000 AS Numeric(38, 6)), 3, N'MAR', 2021)
GO
SELECT * FROM dbo.RetrieveTrialBalanceTEST ORDER BY Month
I hope I've understood you correctly. If so, this is my attempt ...
I've reconciled the output and it correctly flows the balances forward month on month by each attribute grouping. I saw no point in aggregating currencies and it made sense to aggregate on each attribute.
Also, personally, I'd store that data in another field. Adding the data back to the original field, for me, is not required and muddies the water on what is from the source and what is calculated.
You could just build a view on top of your table which is essentially what my answer does.
I hope that's what you're after anyway.
Note: I've included the AccountOpen and Account Net Change in the current month's AccountBalance (ABal), and this is accumulated for each subsequent month in each year, starting over with the next year.
The same applies to the TransactionOpen and Transaction Net Change, etc.
But let's adjust the logic to apply the Net Change rows after any Opening Balance rows to start each year.
Later, we can (if required) fill in any missing monthly data with the last available month's calculation in this year, as needed.
The following result is based on the given data in the question, plus some duplicated rows copied with 2022 dates, to show how the calculation restarts for each year.
Also the result is based on adding the following filter to the query, to show just the 'EUR' related results, to simplify the review.
Notice also that I didn't group the common (Open/Net Change) rows within the same month, but that's easily done. I simply summed them together with a window function. This is only seen in January, in the current data, where the Open and Net Change are both non-0.
