Typical SQL Server relational database implementations that need to support mission critical workloads need to implement high availability (HA) and disaster recovery (DR) strategies with demanding high performance and low latency requirements. Additionally, there is an increasing need for real-time insights into the transactional data to support quick business decisions. However, the reporting and dashboarding workloads end up causing performance problems on the OLTP systems. To support both types of workloads, the database implementation extracts data out of the OLTP system and loads it into separate databases that supported operational reporting in real-time on the incoming transactional data. This increases complexity, costs and results in delays in the availability of data in the reporting and dashboards.
SQL Server 2016 supports a number of capabilities that enable real-time reporting and dashboarding on a high performance, low latency, HADR OLTP database. With these capabilities hosting service providers can offer a high performance database to their customers. This implementation leverages the following capabilities:
For a Highly Efficient and Performant Database scenario, it is important to have an in depth understanding of:
Once the access requirements have been finalized, the next step is to work with a Database Administrator to design and implement the final database deployment. The scenario detailed in this document leverages three key features of SQL Server 2016 which are listed below.
AlwaysOn Availability Groups is an enterprise-level high-availability and disaster recovery solution introduced in SQL Server 2012 to enable you to maximize availability for one or more user databases. AlwaysOn Availability Groups requires that the SQL Server instances reside on Windows Server Failover Clustering (WSFC) nodes.
For more information, see AlwaysOn Availability Groups (SQL Server).
The columnstore index is the standard for storing and querying large data warehousing fact tables. It uses column-based data storage and query processing to achieve up to 10x query performance gains in your data warehouse over traditional row-oriented storage, and up to 10x data compression over the uncompressed data size. Beginning with SQL Server 2016, columnstore indexes enable operational analytics, the ability to run performant real-time analytics on a transactional workload.
For more information, see Columnstore Indexes Guide.
Fabrikam Investments provide security portfolio management services for millions of customers in the US.
The HSP analyzed the workload using the Query Store and determined that the database and tables will need to be reconstructed and the existing data migrated from the existing database.
For more information see: Monitoring Performance By Using the Query Store
The following diagram provides the foreign key relations in the Fabrikam Investments database tables.
Creating the Customer Database
USE [master]
GO
CREATE DATABASE [FabrikamInvestmentsDb]
CONTAINMENT = NONE
ON PRIMARY
(
NAME = N'FabrikamInvestmentsDb',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\FabrikamInvestmentsDb.mdf',
SIZE = 8192KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 65536KB
)
LOG ON
(
NAME = N'FabrikamInvestmentsDb_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\FabrikamInvestmentsDb_log.ldf',
SIZE = 8192KB,
MAXSIZE = 2048GB,
FILEGROWTH = 65536KB
)
GO
-- Create the memory optimized Filegroup and Container
ALTER DATABASE FabrikamInvestmentsDb ADD
FILEGROUP FabrikamInvestmentsDb_mod CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE FabrikamInvestmentsDb ADD
FILE (name='FabrikamInvestments_mod1',
filename='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\FabrikamInvestmentsDb_mod1')
TO FILEGROUP FabrikamInvestmentsDb_mod
GO
ALTER DATABASE FabrikamInvestmentsDb SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON
GO
Make a full backup of the FabrikamInvestmentsDb database
Click the OK button
Click the OK button
Add the database to the appropriate availability group
Click the next button
Select the FabrikamInvestmentsDb Database
Select Full Synchronization
Connect to all the SQL Servers
Connect using Windows Authentication
Click the Next button
Click the Next button
Click the Finish button
Wait for the Availability Group updates to complete
Verify all processing was successful
The following statements will configure the load balancing as follows:
Create the Failover Load Balanced Routing Tables
USE master
GO
-- Set DATA1-SQL1 to allow read only connections
ALTER AVAILABILITY GROUP [TestAG]
MODIFY REPLICA ON N'DATA1-SQL1'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
-- Set DATA1-SQL1 readonly routing url
ALTER AVAILABILITY GROUP [TestAG]
MODIFY REPLICA ON N'DATA1-SQL1'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://Data1.SQL1.Site1.net:5022'));
-- Set DATA1-SQL2 to allow read only connections
ALTER AVAILABILITY GROUP [TestAG]
MODIFY REPLICA ON N'DATA1-SQL2'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
-- Set DATA1-SQL2 readonly routing url ALTER AVAILABILITY GROUP [TestAG]
MODIFY REPLICA ON N'DATA1-SQL2'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://Data1.SQL2.Site1.net:5022'));
-- Set DATA2-SQL1 to allow read only connections
ALTER AVAILABILITY GROUP [TestAG]
MODIFY REPLICA ON N'DATA2-SQL1'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
-- Set DATA2-SQL1 readonly routing url
ALTER AVAILABILITY GROUP [TestAG]
MODIFY REPLICA ON N'DATA2-SQL1'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://Data2.SQL1.Site1.net:5022'));
-- Set DATA2-SQL2 to allow read only connections
ALTER AVAILABILITY GROUP [TestAG]
MODIFY REPLICA ON N'DATA2-SQL2'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
-- Set DATA2-SQL2 readonly routing url
ALTER AVAILABILITY GROUP [TestAG]
MODIFY REPLICA ON N'DATA2-SQL2'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://Data2.SQL2.Site1.net:5022'));
-- Setup the routing --
-- If DATA1-SQL1 is the primary replica
ALTER AVAILABILITY GROUP [TestAG]
MODIFY REPLICA ON N'DATA1-SQL1'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('DATA2-SQL1','DATA2-SQL2'),'DATA1-SQL2')));
-- If DATA1-SQL2 is the primary replica
ALTER AVAILABILITY GROUP [TestAG]
MODIFY REPLICA ON N'DATA1-SQL2'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('DATA2-SQL1','DATA2-SQL2'),'DATA1-SQL1')));
-- If DATA2-SQL1 is the primary replica
ALTER AVAILABILITY GROUP [TestAG]
MODIFY REPLICA ON N'DATA2-SQL1'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('DATA1-SQL1','DATA1-SQL2'),'DATA2-SQL2')));
-- If DATA2-SQL2 is the primary replica
ALTER AVAILABILITY GROUP [TestAG]
MODIFY REPLICA ON N'DATA2-SQL2'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('DATA1-SQL1','DATA1-SQL2'),'DATA2-SQL1')));
GO
USE [FabrikamInvestmentsDb]
GO
-----------------------------------------------------------------------------------------
-- The Order Type table contains the types of orders as an integer (Buy and Sell).
-----------------------------------------------------------------------------------------
CREATE TABLE [dbo].[OrderType]
(
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
[Name] [varchar](10) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA
);
-----------------------------------------------------------------------------------------
-- The Risk Type table contains risk type of an account (LOW, MEDIUM or HIGH).
-----------------------------------------------------------------------------------------
CREATE TABLE [dbo].[RiskType]
(
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
[Name] [varchar](10)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
-----------------------------------------------------------------------------------------
-- The Account Manager table contains information relating to an account manager.
-----------------------------------------------------------------------------------------
CREATE TABLE [dbo].[AccountManager]
(
[ID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
[FirstName] [varchar](20) NOT NULL,
[LastName] [varchar](20) NOT NULL,
[RiskSpecialty] [int] NOT NULL CONSTRAINT FK_AccountManager_RiskType FOREIGN KEY REFERENCES RiskType (ID)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO
-----------------------------------------------------------------------------------------
-- The Customer Account table contains information relating to a customer.
-----------------------------------------------------------------------------------------
CREATE TABLE [dbo].[CustomerAccount]
(
[ID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
[FirstName] [varchar](20) NOT NULL,
[LastName] [varchar](20) NOT NULL,
[Email] [varchar](50) NOT NULL,
[Address] [varchar](50) NOT NULL,
[City] [varchar](20) NOT NULL,
[State] [varchar](2) NOT NULL,
[Zip] [varchar](10) NOT NULL,
[Phone] [varchar](20) NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO
-----------------------------------------------------------------------------------------
-- The Managed Account contains information related to a managed account.
-----------------------------------------------------------------------------------------
CREATE TABLE [dbo].[ManagedAccount]
(
[ID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
[Name] [varchar](10),
[RiskLevel] [int] NOT NULL CONSTRAINT FK_ManagedAccount_RiskType FOREIGN KEY REFERENCES RiskType (ID),
[CashAvailable] [money] DEFAULT('0.00')
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO
-----------------------------------------------------------------------------------------
-- The Managed Account Open Orders table contains all open orders and the status
-- (Completed).
-----------------------------------------------------------------------------------------
CREATE TABLE [dbo].[ManagedAccountOpenOrders]
(
[ID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
[ManagedAccountID] [bigint] NOT NULL CONSTRAINT FK_ManagedAccountOpenOrders_ManagedAccount FOREIGN KEY REFERENCES ManagedAccount (ID),
[OrderType] [int] NOT NULL,
[CompanySymbol] [varchar](10) NOT NULL,
[Shares] [int] NOT NULL,
[Price] [money] NOT NULL,
[Completed] [bit] DEFAULT(0) NOT NULL,
[CreatedDate] [datetime] NOT NULL DEFAULT(getdate())
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
-----------------------------------------------------------------------------------------
-- The Managed Account Completed Transactions contain information from the external
-- exchange relating to all orders that have been completed on the exchange.
-----------------------------------------------------------------------------------------
CREATE TABLE [dbo].[ManagedAccountCompletedTransactions]
(
[ID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
[OpenOrderID] [bigint] NOT NULL CONSTRAINT FK_ManagedAccountCompletedTransactions_ManagedAccountOpenOrders FOREIGN KEY REFERENCES ManagedAccountOpenOrders (ID),
[CompanySymbol] [varchar](10) NOT NULL,
[Shares] [int] NOT NULL,
[Price] [money] NOT NULL,
[CreatedDate] [datetime] NOT NULL DEFAULT(getdate())
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
-----------------------------------------------------------------------------------------
-- The Customer Account Managed Account contains a relation between the customer account
-- and the managed accounts. A customer may have multiple managed accounts.
-----------------------------------------------------------------------------------------
CREATE TABLE [dbo].[CustomerAccountManagedAccount]
(
[ID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
[CustomerAccountID] [bigint] NOT NULL CONSTRAINT FK_CustomerAccountManagedAccount_CustomerAccount FOREIGN KEY REFERENCES CustomerAccount (ID),
[ManagedAccountID] [bigint] NOT NULL CONSTRAINT FK_CustomerAccountManagedAccount_ManagedAccount FOREIGN KEY REFERENCES ManagedAccount (ID),
[AccountManagerID] [bigint] NOT NULL CONSTRAINT FK_CustomerAccountManagedAccount_AccountManager FOREIGN KEY REFERENCES AccountManager (ID),
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO
-----------------------------------------------------------------------------------------
-- The Managed Account Positions contains all stock positions held in a managed account.
-----------------------------------------------------------------------------------------
CREATE TABLE [dbo].[ManagedAccountPositions]
(
[ID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
[ManagedAccountID] [bigint] NOT NULL CONSTRAINT FK_ManagedAccountPositions_ManagedAccount FOREIGN KEY REFERENCES ManagedAccount (ID),
[CompanySymbol] [varchar](10) NOT NULL,
[Shares] [int] NOT NULL,
[Date] [datetime] NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO
The embedded file below contains all table data insert statements used for this scenario while debugging the new database deployment.
USE [FabrikamInvestmentsDb]
GO
INSERT INTO [dbo].[OrderType]
([Name])
VALUES
('BUY'),
('SELL')
GO
INSERT INTO [dbo].[RiskType]
([Name])
VALUES
('LOW'),
('MEDIUM'),
('HIGH')
GO
INSERT INTO [dbo].[AccountManager]
([FirstName],[LastName],[RiskSpecialty])
VALUES
('John', 'Doe', 1),
('Steve', 'Smith', 2),
('Jane', 'Doe', 3)
GO
INSERT INTO [dbo].[CustomerAccount]
([FirstName],[LastName],[Email],[Address],[City],[State],[Zip],[Phone])
VALUES
('Peter', 'James', 'pjames@hotmail.com', '111 Newport Way', 'Nowhere', 'NV', '99999', '555-555-5555'),
('David', 'Smith', 'davidsmith@hotmail.com', '101 Bellevue Way', 'Bellevue', 'WA', '99999', '444-444-4444'),
('Daniel', 'Peterson', 'danp@hotmail.com', '2751 North 1250 East', 'Denver', 'CO', '99999', '333-333-3333')
GO
INSERT INTO [dbo].[ManagedAccount]
([Name],[RiskLevel],[CashAvailable])
VALUES
('401K', 3, '1050000.00'),
('Personal', 2, '1100000.00'),
('IRA', 1, '2030000.00')
GO
INSERT INTO [dbo].[CustomerAccountManagedAccount]
([CustomerAccountID],[ManagedAccountID],[AccountManagerID])
VALUES
(1,1,3),
(2,2,2),
(3,3,1)
GO
INSERT INTO [dbo].[ManagedAccountPositions]
([ManagedAccountID],[CompanySymbol],[Shares],[Date])
VALUES
(1,'AAA',1000,getdate()),
(1,'AAB',1000,getdate()),
(1,'AAC',1000,getdate()),
(1,'AAD',1000,getdate()),
(1,'AAE',1000,getdate()),
(1,'AAF',1000,getdate()),
(1,'AAG',1000,getdate()),
(1,'AAH',1000,getdate()),
(1,'AAI',1000,getdate()),
(1,'AAJ',1000,getdate()),
(1,'AAK',1000,getdate()),
(1,'AAL',1000,getdate()),
(1,'AAM',1000,getdate()),
(1,'BBB',1000,getdate()),
(1,'BBC',1000,getdate()),
(1,'BBD',1000,getdate()),
(1,'BBE',1000,getdate()),
(1,'BBF',1000,getdate()),
(1,'BBG',1000,getdate()),
(1,'BBH',1000,getdate()),
(1,'BBI',1000,getdate()),
(1,'BBJ',1000,getdate()),
(1,'BBK',1000,getdate()),
(1,'BBL',1000,getdate()),
(2,'AAA',1000,getdate()),
(2,'AAB',1000,getdate()),
(2,'AAC',1000,getdate()),
(2,'AAD',1000,getdate()),
(2,'AAE',1000,getdate()),
(2,'AAF',1000,getdate()),
(2,'AAG',1000,getdate()),
(2,'AAH',1000,getdate()),
(2,'AAI',1000,getdate()),
(2,'AAJ',1000,getdate()),
(2,'AAK',1000,getdate()),
(2,'AAL',1000,getdate()),
(2,'AAM',1000,getdate()),
(2,'BBB',1000,getdate()),
(2,'BBC',1000,getdate()),
(2,'BBD',1000,getdate()),
(2,'BBE',1000,getdate()),
(2,'BBF',1000,getdate()),
(2,'BBG',1000,getdate()),
(2,'BBH',1000,getdate()),
(2,'BBI',1000,getdate()),
(2,'BBJ',1000,getdate()),
(2,'BBK',1000,getdate()),
(2,'BBL',1000,getdate()),
(3,'AAA',1000,getdate()),
(3,'AAB',1000,getdate()),
(3,'AAC',1000,getdate()),
(3,'AAD',1000,getdate()),
(3,'AAE',1000,getdate()),
(3,'AAF',1000,getdate()),
(3,'AAG',1000,getdate()),
(3,'AAH',1000,getdate()),
(3,'AAI',1000,getdate()),
(3,'AAJ',1000,getdate()),
(3,'AAK',1000,getdate()),
(3,'AAL',1000,getdate()),
(3,'AAM',1000,getdate()),
(3,'BBB',1000,getdate()),
(3,'BBC',1000,getdate()),
(3,'BBD',1000,getdate()),
(3,'BBE',1000,getdate()),
(3,'BBF',1000,getdate()),
(3,'BBG',1000,getdate()),
(3,'BBH',1000,getdate()),
(3,'BBI',1000,getdate()),
(3,'BBJ',1000,getdate()),
(3,'BBK',1000,getdate()),
(3,'BBL',1000,getdate())
GO
INSERT INTO [dbo].[ManagedAccountOpenOrders]
([ManagedAccountID],[OrderType],[CompanySymbol],[Shares],[Price])
VALUES
(3,2,'BBF',100,1005.3),
(3,2,'AAH',100,230.4),
(3,2,'AAK',100,195.9),
(2,2,'BBI',100,808.5),
(2,2,'AAB',100,381.9),
(3,1,'AAC',100,813.1),
(1,1,'BBJ',100,283),
(1,2,'BBG',100,827.3),
(3,2,'AAM',100,196.7),
(2,1,'AAK',100,177.5),
(2,2,'AAK',100,173.6),
(2,1,'AAC',100,845.3),
(3,2,'AAJ',100,221),
(1,2,'AAK',100,151.2),
(3,2,'AAF',100,601.7),
(3,2,'BBL',100,503.9),
(2,2,'AAJ',100,216.8),
(3,2,'BBL',100,539.9),
(1,1,'BBE',100,389.1),
(3,1,'AAG',100,1060),
(3,1,'AAD',100,149.4),
(1,2,'AAA',100,323.6),
(1,1,'BBI',100,729.3),
(2,2,'AAI',100,741.1),
(3,2,'BBL',100,515.1),
(3,1,'AAD',100,117.6),
(1,2,'BBB',100,782.9),
(3,2,'AAG',100,1022.4),
(1,2,'BBB',100,779),
(2,2,'AAI',100,749.1),
(2,1,'BBK',100,1072),
(1,1,'AAE',100,793.1),
(1,2,'BBI',100,788.9),
(2,1,'AAA',100,264.4),
(3,2,'BBC',100,984.6),
(1,1,'AAM',100,255.2),
(2,2,'AAA',100,253.4),
(3,2,'BBL',100,529.5),
(1,2,'AAM',100,194.1),
(2,2,'BBL',100,483.6),
(2,2,'AAF',100,574.3),
(2,2,'AAJ',100,149.1),
(1,2,'AAG',100,1026.7),
(2,1,'BBE',100,378),
(2,1,'AAA',100,301.7),
(1,2,'AAE',100,796.8),
(3,1,'BBL',100,450.7),
(1,2,'BBF',100,1046.1),
(1,2,'BBB',100,782.8),
(2,1,'AAB',100,387.9),
(2,1,'AAE',100,817.8),
(2,1,'BBG',100,856.6),
(3,1,'BBG',100,863.1),
(2,1,'BBJ',100,336.3),
(3,1,'AAG',100,1055.9),
(2,1,'BBC',100,981.5),
(3,2,'AAL',100,279.4),
(1,2,'BBC',100,1028.1),
(3,1,'AAC',100,805.6),
(3,1,'AAI',100,787.7),
(2,1,'AAE',100,734.6),
(2,1,'BBB',100,813.1),
(1,1,'BBH',100,605.3),
(2,2,'AAE',100,819.5),
(1,2,'AAB',100,394.8),
(1,1,'AAC',100,863.2),
(1,1,'AAE',100,817.2),
(1,2,'AAB',100,392.9),
(2,1,'AAF',100,526.1),
(2,1,'AAC',100,801.2),
(1,2,'AAD',100,157.2),
(2,2,'BBJ',100,281.7),
(3,1,'AAF',100,601.5),
(2,1,'BBF',100,1052.1),
(2,1,'BBH',100,607.7),
(2,1,'BBC',100,990.2),
(1,2,'AAA',100,273.3),
(2,2,'BBH',100,599.2),
(3,2,'AAE',100,773.4),
(2,2,'AAD',100,111.1),
(3,2,'BBH',100,584.1),
(1,2,'BBF',100,1074.8),
(3,1,'AAL',100,299),
(2,2,'AAE',100,773.5),
(3,2,'AAC',100,809.4),
(3,2,'BBE',100,390.2),
(2,1,'AAE',100,802.8),
(2,2,'BBD',100,606.1),
(3,2,'BBL',100,479),
(2,1,'BBB',100,765.4),
(2,2,'BBB',100,826.2),
(1,2,'BBH',100,614.9),
(3,2,'BBF',100,1068.8),
(1,1,'AAC',100,836.3),
(1,2,'AAE',100,763.3),
(1,2,'AAH',100,226.4),
(1,2,'AAF',100,600),
(3,1,'AAM',100,238.5),
(1,1,'AAE',100,818.7),
(2,2,'BBK',100,1079.6),
(3,2,'BBD',100,675.6),
(2,2,'BBF',100,1024.6),
(3,2,'AAC',100,826.2),
(2,1,'AAB',100,331.9),
(1,1,'BBB',100,781.6),
(1,2,'AAI',100,814.5),
(3,2,'AAK',100,213.4),
(1,1,'BBJ',100,309.5),
(1,2,'AAL',100,303.4),
(2,1,'AAF',100,578.8),
(2,2,'AAI',100,810.8),
(1,2,'AAG',100,1050.6),
(2,1,'BBH',100,596),
(2,1,'AAK',100,145.1),
(3,1,'BBB',100,808.5),
(2,1,'AAC',100,812.8),
(2,1,'AAE',100,757.7),
(2,2,'AAI',100,811.5),
(2,1,'BBB',100,830.3),
(1,1,'AAE',100,814),
(3,1,'BBH',100,580),
(3,1,'AAI',100,819.5),
(2,2,'AAJ',100,174.5),
(3,2,'BBG',100,784.8),
(1,2,'BBG',100,819.6),
(3,1,'AAI',100,743.6),
(2,2,'BBE',100,341.1),
(2,2,'AAJ',100,166.8),
(2,1,'BBL',100,466.6),
(2,2,'BBI',100,756.4),
(1,2,'AAK',100,162.7),
(1,2,'AAK',100,193.8),
(1,2,'AAK',100,155.5),
(2,1,'BBJ',100,334.6),
(2,2,'BBH',100,598.1),
(2,1,'BBJ',100,353.4),
(2,1,'AAG',100,1010.2),
(2,2,'BBG',100,798.6),
(2,1,'BBJ',100,297.9),
(3,1,'BBK',100,1036.5),
(1,1,'BBE',100,396.9),
(1,2,'BBG',100,819.7),
(2,1,'AAL',100,333.6),
(1,2,'AAI',100,795.4),
(3,2,'AAK',100,147.1),
(1,2,'AAH',100,303.6),
(3,2,'AAG',100,995.3),
(2,2,'AAI',100,783.4),
(2,1,'BBF',100,1094.9),
(2,1,'AAF',100,529.4),
(2,1,'AAI',100,802),
(3,2,'AAG',100,978.6),
(1,1,'AAD',100,186),
(1,1,'AAL',100,278.2),
(3,1,'BBB',100,832.2),
(1,2,'AAL',100,348.3),
(3,1,'AAK',100,197.3),
(3,2,'BBK',100,1041),
(1,1,'BBF',100,1067.9),
(1,1,'AAG',100,986.8),
(2,2,'BBK',100,1055),
(1,2,'AAE',100,781.2),
(3,2,'AAG',100,993.7),
(3,2,'AAC',100,792.6),
(1,1,'AAB',100,347.4),
(1,1,'AAM',100,197.6),
(3,1,'AAI',100,783.5),
(3,1,'AAF',100,571),
(3,1,'AAC',100,874.8),
(2,1,'AAJ',100,191.2),
(3,2,'BBJ',100,354.8),
(3,1,'AAG',100,985.8),
(1,1,'AAL',100,328.5),
(3,1,'BBG',100,821.5),
(1,1,'BBC',100,1052.4),
(3,2,'BBC',100,1024.1),
(1,1,'AAH',100,248.4),
(1,2,'AAD',100,182.4),
(1,1,'BBL',100,525.5),
(3,1,'BBJ',100,323.3),
(1,1,'BBJ',100,356.1),
(1,1,'AAE',100,805.7),
(1,2,'BBD',100,640.8),
(3,1,'BBL',100,458.1),
(3,2,'AAA',100,296.8),
(2,2,'BBL',100,481),
(1,1,'AAM',100,263.8),
(1,2,'AAD',100,131.6),
(3,2,'BBG',100,861.3),
(1,1,'AAI',100,770.5),
(2,2,'BBK',100,1017.2),
(2,2,'AAC',100,880.3),
(2,1,'BBH',100,620.6),
(1,1,'BBL',100,461.8),
(1,1,'BBE',100,380.6),
(3,1,'AAM',100,224.2),
(1,2,'BBE',100,328.8),
(2,1,'BBB',100,820.2),
(1,2,'BBI',100,764.1),
(3,1,'BBD',100,610.8),
(2,1,'BBK',100,1038.1),
(1,1,'BBK',100,1066),
(2,1,'AAG',100,1030.6),
(3,1,'AAG',100,1007.1),
(3,1,'AAB',100,342.1),
(3,2,'AAM',100,243.1),
(2,1,'AAB',100,359.4),
(2,2,'AAM',100,221.7),
(3,2,'BBD',100,616.9),
(1,2,'AAH',100,238.2),
(3,1,'AAE',100,782.7),
(2,2,'BBJ',100,361.8),
(3,1,'BBK',100,1056),
(1,1,'AAK',100,145.6),
(1,2,'AAF',100,541.5),
(2,1,'AAA',100,310.9),
(1,1,'AAF',100,535),
(3,2,'AAJ',100,142),
(1,1,'AAD',100,152.6),
(3,1,'AAM',100,270),
(1,2,'AAK',100,153.9),
(2,1,'AAD',100,138.9),
(1,1,'AAK',100,191.6),
(3,1,'BBC',100,1017.2),
(1,1,'AAK',100,179),
(2,1,'AAL',100,352.1),
(1,2,'BBH',100,633.8),
(3,1,'AAD',100,154.6),
(2,2,'AAH',100,281.1),
(1,2,'AAF',100,565.9),
(2,2,'AAB',100,358.2),
(3,1,'AAF',100,577.6),
(1,2,'AAG',100,1012.5),
(1,2,'BBJ',100,346.4),
(2,2,'AAF',100,537.2),
(1,1,'BBC',100,989.7),
(2,1,'BBJ',100,295.2),
(1,2,'AAL',100,277.5),
(1,2,'BBE',100,386.9),
(3,2,'BBD',100,688.7),
(3,1,'BBE',100,392.4),
(2,1,'AAD',100,152.9),
(2,2,'AAM',100,195.1),
(2,1,'BBI',100,792.9),
(1,1,'BBJ',100,351.7),
(3,2,'AAL',100,287.9),
(3,2,'BBE',100,350.3),
(2,1,'AAF',100,548.5),
(3,1,'AAG',100,1057.1),
(2,1,'AAD',100,117.8),
(3,1,'AAM',100,210.2),
(2,2,'BBJ',100,286.1),
(2,2,'AAE',100,748.6),
(1,1,'BBC',100,1009.3),
(3,2,'AAF',100,591.9),
(3,2,'AAK',100,132.9),
(3,2,'AAF',100,608.2),
(3,2,'AAH',100,229.5),
(3,2,'AAM',100,257.1),
(1,1,'AAL',100,304.7),
(2,2,'AAA',100,247.9),
(2,1,'AAE',100,815.9),
(1,1,'AAG',100,1045.5),
(2,1,'AAB',100,378.2),
(2,2,'BBF',100,1073.5),
(1,1,'AAK',100,213.6),
(2,2,'AAH',100,273.4),
(2,1,'AAA',100,315),
(2,1,'AAK',100,219.4),
(1,2,'AAG',100,1065.8),
(1,2,'BBK',100,1052.6),
(3,1,'AAG',100,986.4),
(2,1,'BBH',100,599.4),
(1,1,'AAA',100,295.5),
(3,1,'AAF',100,522.4),
(3,1,'AAE',100,804.2),
(1,2,'BBC',100,986.8),
(2,1,'BBC',100,1055.9),
(2,1,'AAJ',100,195.3),
(2,1,'AAJ',100,166.8),
(2,1,'BBE',100,346.7),
(3,2,'AAC',100,819.1),
(3,2,'AAB',100,375.3),
(3,1,'BBB',100,812.6),
(2,2,'BBF',100,1025.2),
(3,1,'AAH',100,249.9),
(2,1,'BBB',100,802.8),
(3,1,'BBH',100,662.8),
(2,1,'AAC',100,879.7),
(2,2,'AAD',100,124.2),
(1,1,'AAG',100,1000.2),
(3,1,'AAC',100,803.8),
(2,2,'BBB',100,784.4),
(3,1,'BBL',100,497.1),
(2,1,'AAK',100,211.2),
(1,1,'BBF',100,1095),
(2,1,'AAB',100,385.2),
(3,2,'AAC',100,834.3),
(1,1,'BBJ',100,362),
(1,1,'AAI',100,816.7),
(2,2,'AAB',100,377.1),
(1,1,'AAF',100,583.4),
(3,1,'AAE',100,760.6),
(3,2,'BBJ',100,357.4),
(2,2,'BBD',100,657.5),
(1,2,'AAA',100,261.3),
(3,1,'BBG',100,860.7),
(1,1,'BBC',100,1025.6),
(1,1,'AAD',100,150.1),
(1,2,'BBJ',100,365.3),
(3,1,'BBI',100,803.3),
(1,2,'BBH',100,667.4),
(2,2,'AAJ',100,164.8),
(2,1,'AAA',100,283.6),
(3,2,'BBC',100,1052.3),
(2,1,'AAD',100,173.6),
(2,1,'AAJ',100,162.6),
(1,1,'BBC',100,987.4),
(1,1,'BBI',100,764.6),
(3,2,'AAA',100,288.7),
(3,2,'AAB',100,365.3),
(2,1,'AAF',100,575.1),
(1,2,'BBH',100,646.8),
(1,1,'BBG',100,858.8),
(3,1,'BBH',100,643.1),
(2,1,'BBB',100,794.3),
(1,2,'BBJ',100,354),
(2,1,'AAD',100,176.3),
(2,1,'AAA',100,304.4),
(3,2,'BBE',100,310.7),
(1,1,'AAC',100,799.1),
(1,2,'AAH',100,288.1),
(2,2,'AAL',100,274.9),
(1,1,'AAF',100,592.1),
(1,1,'AAD',100,180.5),
(1,2,'BBF',100,1070.2),
(1,2,'BBI',100,729.3),
(1,2,'AAA',100,308.2),
(2,2,'AAL',100,362.3),
(1,1,'AAA',100,251.5),
(1,2,'AAA',100,284.4),
(1,2,'AAJ',100,205.7),
(3,1,'BBB',100,808.3),
(2,2,'AAJ',100,132.4),
(3,2,'AAB',100,350.9),
(3,2,'AAK',100,153.9),
(1,2,'AAI',100,805.7),
(1,1,'AAH',100,298.8),
(1,1,'BBG',100,835.6),
(1,1,'AAI',100,753.1),
(2,2,'BBI',100,788.2),
(1,2,'AAE',100,776.5),
(3,1,'BBG',100,790.8),
(1,2,'AAD',100,154.3),
(2,1,'AAG',100,1015.2),
(3,1,'BBK',100,1007.9),
(1,2,'AAI',100,817.8),
(2,2,'AAK',100,172.1),
(3,1,'AAI',100,750.4),
(1,1,'BBL',100,470),
(3,1,'AAI',100,761.9),
(1,1,'AAM',100,197.8),
(3,1,'BBE',100,398.2),
(2,1,'BBG',100,820.5),
(3,1,'AAD',100,191.3),
(2,1,'BBL',100,461),
(3,2,'BBC',100,1057),
(1,2,'AAK',100,181.4),
(2,2,'AAM',100,242.9),
(2,2,'AAI',100,753.4),
(1,1,'BBL',100,512.6),
(2,2,'BBI',100,787.2),
(3,1,'AAG',100,996.3),
(3,1,'AAC',100,821.2),
(2,2,'AAE',100,736.7),
(3,2,'BBL',100,510.1),
(2,1,'BBE',100,317.2),
(3,1,'BBF',100,1074.4),
(1,2,'BBD',100,653.5),
(1,2,'BBL',100,492.3),
(1,2,'BBE',100,360.2),
(1,1,'AAG',100,979.2),
(2,2,'AAJ',100,144.8),
(2,2,'AAK',100,169.7),
(1,1,'BBK',100,1039.2),
(1,1,'BBK',100,1061.8),
(2,1,'BBI',100,784.9),
(1,2,'AAI',100,782.7),
(3,2,'AAF',100,553.2),
(1,2,'BBE',100,378.1),
(1,1,'AAE',100,804.5),
(2,1,'AAA',100,295),
(2,1,'BBE',100,355.3),
(1,2,'AAC',100,859.8),
(2,1,'AAF',100,588.9),
(3,2,'AAE',100,758),
(1,2,'BBF',100,1079.6),
(1,2,'AAJ',100,140.6),
(3,1,'BBE',100,318.9),
(1,1,'AAH',100,242.1),
(1,2,'BBK',100,1032.5),
(1,2,'AAA',100,265.9),
(3,2,'BBK',100,1024.5),
(2,1,'BBE',100,343.4),
(3,2,'BBE',100,355.5),
(1,1,'AAM',100,186.2),
(2,1,'BBB',100,786.4),
(1,1,'AAH',100,291.1),
(3,1,'BBJ',100,315.4),
(2,2,'BBJ',100,345.5),
(2,1,'AAB',100,405),
(2,1,'AAE',100,746.3),
(2,2,'AAJ',100,216.9),
(3,2,'BBK',100,1071.1),
(1,1,'AAL',100,331.8),
(3,1,'BBE',100,333.4),
(2,1,'BBB',100,852.2),
(1,1,'AAI',100,784.2),
(1,1,'BBG',100,788.4),
(3,2,'AAI',100,760.5),
(1,2,'BBG',100,826.9),
(2,1,'BBJ',100,316.7),
(2,1,'AAD',100,130),
(1,1,'BBF',100,1083.9),
(2,1,'BBB',100,798.8),
(2,1,'AAC',100,803.8),
(1,1,'AAK',100,142.4),
(2,2,'AAB',100,362.7),
(2,1,'AAM',100,238.1),
(3,1,'AAE',100,801.2),
(2,1,'AAK',100,205.2),
(2,2,'AAH',100,313.5),
(1,2,'BBD',100,665.6),
(3,2,'AAF',100,550.2),
(1,1,'AAI',100,751.1),
(1,1,'AAD',100,142.3),
(1,2,'BBC',100,1044.1),
(1,1,'AAE',100,800.8),
(2,1,'BBL',100,533.8),
(1,1,'AAC',100,821.5),
(2,2,'BBK',100,1037.7),
(1,1,'AAK',100,209.1),
(2,2,'AAF',100,529.7),
(1,2,'AAM',100,238.2),
(3,2,'AAL',100,349.7),
(2,1,'BBL',100,508.8),
(1,1,'AAF',100,574.9),
(2,2,'BBJ',100,348.4),
(1,1,'BBL',100,461.6),
(2,1,'AAG',100,1020.1),
(1,1,'AAE',100,758),
(3,1,'BBD',100,631),
(3,1,'AAJ',100,135),
(3,1,'AAC',100,864.3),
(1,1,'AAE',100,796.5),
(2,2,'AAB',100,397.1),
(3,1,'BBD',100,606.5),
(2,1,'AAH',100,252.5),
(3,2,'AAG',100,1064),
(2,1,'AAD',100,143.7),
(1,2,'AAB',100,400.8),
(1,1,'AAM',100,258.9),
(2,1,'BBI',100,743.1),
(3,2,'AAG',100,1036.8),
(1,2,'BBC',100,1052.7),
(3,1,'BBG',100,865.8),
(2,1,'AAA',100,290.6),
(2,1,'BBH',100,632.8),
(3,2,'AAF',100,568.5),
(1,1,'BBB',100,789.1),
(2,1,'BBI',100,767.3),
(2,2,'BBL',100,472.2),
(3,1,'AAE',100,788.6),
(1,1,'AAI',100,799.3),
(1,1,'BBG',100,867.9),
(3,2,'BBH',100,632.6),
(1,1,'AAJ',100,164),
(1,1,'BBB',100,818.5),
(2,1,'AAE',100,734.9),
(3,1,'BBJ',100,344.5),
(2,2,'AAI',100,784.2),
(2,1,'AAG',100,1006.5),
(1,2,'BBB',100,791.7),
(3,2,'AAE',100,741.5),
(2,2,'AAI',100,778),
(2,1,'AAH',100,274),
(2,1,'BBG',100,869.7),
(1,1,'BBH',100,653),
(3,1,'AAB',100,377.4),
(2,1,'BBJ',100,307.7),
(2,2,'BBG',100,813.8),
(3,1,'BBG',100,829.7),
(1,2,'AAG',100,1047),
(1,2,'AAC',100,840.8),
(3,1,'BBL',100,493.1),
(2,1,'AAA',100,293.2),
(3,2,'BBF',100,1069.8),
(2,2,'AAF',100,559.2),
(1,1,'AAI',100,793.5),
(3,1,'BBC',100,1051.7),
(1,2,'AAM',100,199.2),
(2,1,'BBD',100,694.6),
(3,1,'AAB',100,347),
(1,1,'BBI',100,802.8),
(1,2,'BBG',100,787.9),
(3,1,'AAB',100,326.2),
(1,2,'BBE',100,368),
(2,1,'AAM',100,213.5),
(3,1,'BBH',100,656.2),
(1,2,'BBI',100,737.5),
(2,1,'AAM',100,228.3),
(2,2,'AAD',100,145),
(3,1,'AAC',100,816.5),
(3,2,'BBI',100,747.9),
(3,1,'AAH',100,297.2),
(3,2,'AAB',100,370.7),
(3,2,'BBG',100,827),
(3,1,'BBC',100,973.9),
(2,2,'BBB',100,779.5),
(2,2,'AAE',100,766.4),
(2,1,'AAG',100,1062.5),
(2,1,'BBE',100,367.9),
(3,2,'BBE',100,309.4),
(1,1,'BBL',100,466.6),
(2,1,'BBH',100,663.2),
(3,1,'AAK',100,211.7),
(1,1,'AAB',100,363.2),
(2,1,'AAB',100,327.9),
(2,2,'AAB',100,344.3),
(3,2,'AAM',100,223.6),
(1,1,'AAI',100,815.3),
(2,1,'AAM',100,268.7),
(1,1,'AAB',100,409.7),
(3,2,'AAA',100,255.7),
(3,2,'AAE',100,786.9),
(3,2,'AAM',100,236.1),
(2,1,'BBI',100,734),
(1,2,'AAD',100,156.1),
(3,1,'AAB',100,326.2),
(3,2,'BBL',100,489.9),
(1,1,'BBD',100,687.4),
(3,2,'BBD',100,664.8),
(1,1,'AAB',100,409),
(2,2,'AAD',100,157.1),
(1,1,'AAF',100,599.4),
(3,2,'AAK',100,217),
(3,1,'BBB',100,783.1),
(2,2,'BBE',100,360.6),
(1,2,'BBE',100,376.2),
(1,2,'BBH',100,661.5),
(2,2,'AAF',100,523.6),
(3,1,'BBG',100,842),
(1,1,'BBC',100,989.6),
(2,2,'AAL',100,298.2),
(2,1,'AAB',100,409.3),
(3,2,'BBF',100,1050.4),
(3,1,'AAA',100,334.7),
(3,2,'BBG',100,791.5),
(2,2,'BBG',100,861.1),
(2,1,'AAD',100,124.3),
(2,1,'BBF',100,1058.1),
(3,2,'AAD',100,164),
(1,2,'BBB',100,768.4),
(3,2,'AAD',100,151.3),
(1,2,'AAC',100,808.7),
(3,1,'BBB',100,811.9),
(1,2,'AAM',100,222),
(1,1,'BBD',100,622.9),
(1,2,'AAD',100,165.7),
(1,2,'BBB',100,784.7),
(1,1,'AAG',100,1019.4),
(3,2,'BBB',100,851.2),
(1,1,'AAA',100,247.8),
(3,1,'AAM',100,253.2),
(2,1,'AAA',100,289),
(2,1,'AAC',100,817.8),
(1,2,'AAH',100,285.8),
(1,1,'AAC',100,834.3),
(1,1,'AAL',100,301.1),
(3,1,'AAF',100,609.8),
(3,1,'BBC',100,1029.4),
(3,1,'AAD',100,190.4),
(1,2,'AAF',100,549.6),
(1,1,'AAF',100,548.9),
(2,2,'AAA',100,282),
(1,2,'AAI',100,756),
(2,1,'BBH',100,639.9),
(3,1,'AAB',100,384.9),
(2,2,'BBJ',100,288.4),
(1,1,'AAI',100,744),
(1,1,'AAK',100,182.5),
(3,1,'AAB',100,408.9),
(2,1,'AAE',100,789.4),
(1,1,'AAG',100,1056.8),
(1,2,'AAB',100,398.6),
(2,1,'BBB',100,790.4),
(2,1,'BBD',100,658.6),
(2,1,'BBE',100,355.5),
(2,2,'BBF',100,1033.1),
(2,2,'AAE',100,761.3),
(2,1,'AAK',100,180.3),
(3,2,'BBJ',100,280.2),
(2,1,'BBI',100,815.2),
(1,2,'BBB',100,806.8),
(1,1,'AAK',100,171),
(2,2,'BBK',100,1010.9),
(3,1,'BBL',100,508.4),
(1,2,'AAE',100,775.5),
(3,1,'AAI',100,806.8),
(3,2,'BBC',100,1028.2),
(2,2,'BBJ',100,349.4),
(2,2,'BBH',100,621.9),
(2,2,'AAE',100,803.6),
(3,1,'AAG',100,1055.8),
(3,1,'AAD',100,153.5),
(2,2,'AAC',100,853.8),
(3,1,'BBB',100,786.8),
(1,1,'AAK',100,198.2),
(2,2,'AAH',100,228.7),
(3,2,'AAB',100,409.3),
(1,1,'BBE',100,386.3),
(2,2,'BBC',100,1032.6),
(2,1,'BBK',100,1011.7),
(3,2,'BBG',100,828.4),
(1,1,'BBF',100,1065.2),
(2,2,'AAL',100,319.8),
(2,2,'AAE',100,764.5),
(3,1,'BBL',100,461.5),
(2,2,'BBI',100,810.3),
(1,1,'AAC',100,804.9),
(3,1,'BBL',100,515.4),
(3,1,'AAI',100,750.4),
(3,1,'AAD',100,126.8),
(3,1,'AAE',100,821.2),
(1,1,'BBI',100,767.3),
(3,1,'AAB',100,379),
(2,1,'AAE',100,815.4),
(3,2,'AAH',100,297.9),
(1,2,'BBB',100,839.3),
(3,1,'AAE',100,764.4),
(2,2,'BBG',100,836.2),
(2,1,'AAC',100,809.8),
(3,2,'BBF',100,1085.8),
(2,1,'BBJ',100,318.8),
(3,1,'AAM',100,211.2),
(2,1,'BBK',100,1015.1),
(1,2,'BBE',100,324.7),
(2,2,'BBL',100,518),
(2,2,'AAK',100,167.9),
(2,2,'BBD',100,619),
(2,1,'BBD',100,609.6),
(2,2,'AAL',100,347.8),
(1,2,'AAF',100,562.4),
(3,1,'AAH',100,254.7),
(1,2,'BBI',100,770.3),
(1,2,'BBE',100,385.3),
(2,2,'BBB',100,851.8),
(2,2,'AAL',100,314.4),
(1,1,'BBB',100,836.8),
(2,1,'AAL',100,358),
(1,2,'BBK',100,1068.9),
(3,1,'BBL',100,497.4),
(3,1,'AAK',100,181.5),
(3,1,'BBJ',100,333.7),
(3,1,'BBD',100,690.8),
(3,1,'BBD',100,663.7),
(3,2,'AAI',100,763.3),
(3,1,'BBF',100,1077.5),
(3,1,'AAK',100,170.9),
(3,2,'BBC',100,1044.4),
(1,1,'AAG',100,1046.8),
(3,1,'BBB',100,802.8),
(1,1,'AAL',100,354.6),
(2,2,'BBL',100,513.4),
(1,1,'AAG',100,1009.9),
(3,2,'AAK',100,187.8),
(2,2,'BBI',100,802),
(3,1,'AAE',100,784.4),
(1,1,'AAI',100,754.4),
(2,1,'BBJ',100,328.2),
(1,1,'AAA',100,277.2),
(3,1,'BBK',100,1061.7),
(1,1,'AAE',100,795.5),
(3,2,'AAM',100,253.9),
(2,2,'AAA',100,257.3),
(2,2,'BBC',100,1049.1),
(3,2,'BBK',100,1071.3),
(3,1,'AAG',100,1051.4),
(2,2,'AAF',100,568.1),
(3,2,'AAF',100,573.4),
(2,2,'AAM',100,242.5),
(3,2,'AAD',100,152.8),
(3,1,'BBI',100,818.1),
(2,2,'AAI',100,817.2),
(3,1,'BBI',100,788.9),
(3,1,'AAD',100,117.5),
(3,1,'BBJ',100,334.1),
(1,2,'BBH',100,600.6),
(2,2,'AAJ',100,180.1),
(3,1,'AAL',100,331.7),
(3,2,'BBC',100,1038),
(1,1,'BBJ',100,309.4),
(3,2,'AAI',100,761.4),
(3,1,'BBL',100,502.6),
(2,1,'BBH',100,609.9),
(3,1,'BBC',100,1021.6),
(2,1,'AAM',100,268.5),
(2,1,'AAE',100,801.1),
(3,2,'BBG',100,870),
(2,1,'BBE',100,383.1),
(3,2,'AAD',100,113.7),
(2,1,'BBC',100,1006.1),
(2,1,'BBJ',100,297.4),
(2,1,'BBJ',100,298.7),
(2,2,'BBI',100,782.4),
(2,2,'AAE',100,739.6),
(2,1,'BBH',100,651.2),
(3,1,'BBF',100,1077.9),
(1,1,'AAB',100,368.8),
(2,2,'AAH',100,228.1),
(1,2,'BBG',100,866.3),
(3,1,'AAL',100,316.9),
(2,2,'AAH',100,249),
(3,1,'AAD',100,189.2),
(2,1,'BBF',100,1023.8),
(1,2,'BBC',100,997.1),
(1,2,'AAI',100,775.8),
(2,2,'BBH',100,666.1),
(3,1,'BBJ',100,278.8),
(2,2,'AAJ',100,159.2),
(1,2,'BBG',100,872.5),
(2,2,'AAI',100,785),
(1,2,'AAJ',100,136.3),
(1,2,'AAF',100,572.4),
(3,1,'BBI',100,801.2),
(1,1,'AAA',100,333.9),
(3,2,'BBL',100,478.5),
(2,2,'BBE',100,320.2),
(2,1,'BBG',100,819),
(3,2,'BBI',100,776.5),
(1,1,'BBG',100,870.1),
(1,2,'AAH',100,309.4),
(1,2,'AAH',100,301.7),
(1,1,'BBJ',100,339.4),
(3,2,'AAL',100,323.1),
(3,1,'AAC',100,810),
(2,1,'AAL',100,294.1),
(1,2,'AAJ',100,199.2),
(1,2,'BBG',100,811.5),
(2,1,'BBI',100,781.7),
(1,1,'BBI',100,751.5),
(3,2,'AAK',100,205.2),
(3,1,'BBF',100,1071.7),
(1,2,'AAD',100,154.5),
(3,2,'AAH',100,288.6),
(3,2,'BBB',100,788.3),
(3,1,'BBJ',100,284.2),
(1,1,'BBE',100,347.4),
(1,2,'BBD',100,639.4),
(1,2,'BBB',100,766.1),
(3,1,'AAL',100,285.5),
(3,1,'BBH',100,662),
(3,2,'AAA',100,312.3),
(1,2,'AAJ',100,192.6),
(1,2,'AAC',100,864.5),
(2,2,'AAG',100,1018.9),
(1,2,'AAM',100,218.8),
(1,1,'BBK',100,1067),
(3,1,'AAD',100,150.8),
(1,2,'AAJ',100,188.4),
(2,1,'BBK',100,1076.4),
(3,1,'AAM',100,247.9),
(1,2,'BBH',100,614),
(2,2,'AAJ',100,220.9),
(2,1,'AAD',100,117.7),
(3,2,'AAE',100,820.4),
(3,2,'AAA',100,302),
(2,1,'AAL',100,325.4),
(3,2,'AAC',100,819),
(2,1,'BBB',100,851.2),
(3,1,'AAG',100,984.2),
(3,2,'BBG',100,846.8),
(2,1,'AAM',100,245.1),
(2,2,'AAA',100,285.9),
(1,2,'BBE',100,313.2),
(1,2,'AAA',100,261),
(2,2,'BBH',100,651.1),
(1,2,'AAJ',100,182.7),
(2,1,'BBJ',100,352.8),
(3,2,'BBD',100,652.6),
(2,2,'AAI',100,814.2),
(3,1,'BBL',100,530.2),
(2,1,'BBF',100,1072.9),
(1,2,'AAE',100,764.8),
(1,1,'AAK',100,165.9),
(1,1,'BBG',100,785.5),
(2,2,'AAE',100,752),
(3,2,'AAJ',100,149.8),
(3,2,'AAG',100,1055.4),
(2,2,'AAD',100,118),
(3,1,'BBB',100,838.4),
(1,2,'AAF',100,571.8),
(1,2,'AAE',100,759.4),
(3,2,'BBF',100,1067.7),
(3,1,'AAD',100,184.7),
(1,2,'BBC',100,1018.8),
(3,1,'AAI',100,773.4),
(1,1,'BBD',100,674),
(2,2,'AAB',100,359),
(3,2,'BBE',100,375.9),
(1,2,'AAE',100,792.8),
(2,2,'BBB',100,808.4),
(1,2,'AAB',100,331.8),
(3,2,'BBF',100,1039.6),
(2,1,'AAM',100,271.3),
(3,2,'AAD',100,148.8),
(1,1,'BBK',100,1031.8),
(2,2,'BBJ',100,302.5),
(1,1,'AAH',100,280.1),
(1,2,'BBF',100,1064.7),
(3,1,'BBL',100,509.4),
(1,2,'BBI',100,801.7),
(3,1,'BBI',100,801.8),
(1,2,'BBH',100,660.9),
(3,1,'BBH',100,644.2),
(3,2,'BBH',100,657),
(2,1,'AAJ',100,217.2),
(1,1,'AAG',100,980.5),
(2,2,'BBH',100,630.3),
(1,1,'AAF',100,525.1),
(2,1,'AAM',100,253.7),
(3,1,'BBD',100,644.2),
(2,2,'BBK',100,1070),
(2,2,'AAB',100,343.8),
(3,2,'AAF',100,533.8),
(2,2,'AAK',100,177.1),
(3,1,'AAD',100,148),
(3,1,'BBH',100,664.8),
(2,2,'AAE',100,788.2),
(3,2,'AAJ',100,176.1),
(2,2,'AAB',100,388.3),
(2,1,'BBB',100,768),
(2,1,'BBD',100,619.7),
(2,1,'AAI',100,744.7),
(2,2,'BBJ',100,352.3),
(3,2,'AAD',100,144.2),
(2,2,'AAJ',100,202.3),
(3,2,'BBJ',100,309.4),
(2,2,'AAJ',100,168.6),
(1,2,'AAK',100,217.9),
(3,2,'BBF',100,1073.3),
(3,2,'BBH',100,660.6),
(3,1,'AAE',100,783.7),
(3,2,'AAG',100,995.9),
(2,2,'AAM',100,223.5),
(3,1,'AAI',100,737.8),
(2,2,'BBC',100,1036.2),
(1,1,'AAG',100,1009.9),
(3,1,'BBG',100,868.5),
(1,2,'AAA',100,330.6),
(3,2,'AAL',100,328.7),
(2,1,'AAG',100,1010.8),
(2,2,'AAM',100,200.4),
(2,1,'AAA',100,279),
(1,2,'AAI',100,823.1),
(1,2,'BBG',100,805),
(3,1,'BBF',100,1078.4),
(3,2,'BBB',100,840.2),
(3,2,'BBJ',100,297.8),
(3,2,'BBE',100,348.1),
(1,2,'AAK',100,130.9),
(2,2,'AAC',100,817.7),
(3,1,'BBL',100,511.2),
(1,2,'BBE',100,321.5),
(3,1,'AAD',100,194.4),
(2,1,'BBI',100,781.8),
(2,2,'AAD',100,170.3),
(1,2,'BBB',100,825.5),
(2,2,'AAK',100,137.3),
(3,2,'BBD',100,639.5),
(1,1,'AAJ',100,141.7),
(3,2,'BBH',100,629.1),
(2,2,'AAI',100,792.6),
(2,1,'BBJ',100,331.1),
(2,1,'AAJ',100,158.2),
(2,1,'BBL',100,525.8),
(1,1,'AAA',100,323.2),
(3,2,'BBG',100,826.1),
(3,1,'AAI',100,808.1),
(1,2,'BBK',100,1016.9),
(1,2,'AAL',100,342.8),
(2,2,'BBC',100,972.1),
(1,1,'AAH',100,225.4),
(1,1,'BBI',100,790.1),
(3,2,'BBH',100,656),
(3,2,'BBK',100,1038.7),
(3,1,'BBG',100,863.3),
(1,2,'BBI',100,799.7),
(1,2,'AAJ',100,143.9),
(2,1,'AAK',100,183.9),
(1,1,'BBC',100,1018.6),
(3,2,'AAA',100,328.4),
(3,2,'AAC',100,802),
(3,2,'BBC',100,1055.9),
(3,2,'AAL',100,360.4),
(2,2,'BBK',100,1048),
(3,2,'BBL',100,473.1),
(2,1,'BBB',100,801.2),
(3,2,'BBK',100,1033.2),
(1,1,'BBL',100,525.6),
(1,2,'AAM',100,218.9),
(1,2,'AAK',100,134.5),
(2,1,'BBK',100,1033.5),
(3,2,'BBB',100,814.6),
(3,1,'BBL',100,525.4),
(1,1,'BBJ',100,279.7),
(1,1,'AAB',100,388.8),
(1,2,'AAI',100,785),
(1,1,'BBB',100,768.9),
(2,1,'AAD',100,123.9),
(1,2,'AAL',100,308.9),
(1,2,'AAA',100,276.2),
(1,2,'AAH',100,228.5),
(3,1,'AAA',100,258.2),
(3,2,'AAC',100,850),
(2,2,'BBL',100,537.9),
(2,2,'BBG',100,808.5),
(2,1,'AAB',100,411.5),
(1,2,'BBL',100,474.3),
(3,2,'AAH',100,275.8),
(3,2,'BBK',100,1069.7),
(2,2,'AAI',100,813.1),
(3,1,'AAI',100,786.4),
(1,2,'BBI',100,772),
(3,1,'AAG',100,1049.7),
(2,1,'AAF',100,538.4),
(3,2,'AAJ',100,172.3),
(3,2,'BBF',100,1065.7),
(2,1,'AAJ',100,190.2),
(3,1,'BBI',100,803.8),
(3,2,'BBL',100,503.3),
(3,2,'AAG',100,1014.3),
(3,1,'BBI',100,745.9),
(2,2,'BBH',100,665.7),
(3,2,'AAH',100,290),
(1,1,'AAB',100,387.2),
(1,1,'BBL',100,517.8),
(1,1,'BBJ',100,315.3),
(2,2,'AAE',100,809.1),
(3,2,'AAE',100,807.5),
(2,1,'AAD',100,165.9),
(2,1,'BBI',100,759),
(1,2,'BBH',100,641.8),
(3,1,'AAK',100,218.6),
(2,1,'AAK',100,148.5),
(1,2,'BBG',100,802.9),
(2,1,'AAA',100,323.8),
(3,2,'AAC',100,880.2),
(3,1,'BBL',100,478.3),
(1,1,'BBH',100,667.1),
(1,1,'BBB',100,777.3),
(1,1,'AAH',100,247.2),
(1,2,'AAL',100,288.6),
(3,2,'BBC',100,1032.7),
(1,2,'AAG',100,988.9),
(3,1,'BBH',100,622.7),
(1,1,'BBF',100,1048),
(2,2,'BBH',100,650.4),
(3,1,'BBC',100,1037.5),
(2,1,'AAC',100,847.7),
(1,1,'BBD',100,659.6),
(2,2,'AAL',100,279.5),
(3,2,'AAJ',100,214.5),
(1,2,'BBE',100,330.7),
(3,2,'BBK',100,1057.5),
(1,1,'AAG',100,1063.9),
(1,2,'BBB',100,774.8),
(2,2,'AAJ',100,138.6),
(1,1,'AAB',100,357.9),
(3,1,'AAC',100,875.1),
(2,1,'BBG',100,802.9),
(1,2,'AAH',100,300.4),
(1,2,'AAF',100,533.5),
(2,2,'AAI',100,773.5),
(2,2,'BBC',100,1055.5),
(2,1,'BBI',100,760.9),
(1,2,'AAH',100,237.8),
(1,2,'BBD',100,659.1),
(2,1,'AAM',100,210.2),
(3,2,'BBF',100,1058.3),
(3,1,'BBJ',100,306.1),
(3,2,'AAK',100,170.3),
(3,2,'AAF',100,553.8),
(1,2,'AAL',100,331.8),
(3,1,'AAL',100,333.6),
(2,1,'AAM',100,214.7),
(2,2,'BBD',100,628.8),
(3,1,'BBE',100,352.3),
(2,1,'BBJ',100,351.9),
(1,2,'AAA',100,267.3)
GO
USE [FabrikamInvestmentsDb]
GO
INSERT INTO [dbo].[ManagedAccountCompletedTransactions]
([OpenOrderID],[CompanySymbol],[Shares],[Price],[CreatedDate])
VALUES
(742,'AAH',100,'309.4',getdate()),
(743,'AAH',100,'301.7',getdate()),
(744,'BBJ',100,'339.4',getdate()),
(745,'AAL',100,'323.1',getdate()),
(746,'AAC',100,'810',getdate()),
(814,'BBF',100,'1039.6',getdate()),
(815,'AAM',100,'271.3',getdate()),
(816,'AAD',100,'148.8',getdate()),
(817,'BBK',100,'1031.8',getdate()),
(818,'BBJ',100,'302.5',getdate()),
(819,'AAH',100,'280.1',getdate()),
(820,'BBF',100,'1064.7',getdate()),
(821,'BBL',100,'509.4',getdate()),
(822,'BBI',100,'801.7',getdate()),
(823,'BBI',100,'801.8',getdate()),
(824,'BBH',100,'660.9',getdate()),
(825,'BBH',100,'644.2',getdate()),
(826,'BBH',100,'657',getdate()),
(827,'AAJ',100,'217.2',getdate()),
(828,'AAG',100,'980.5',getdate()),
(829,'BBH',100,'630.3',getdate()),
(830,'AAF',100,'525.1',getdate()),
(831,'AAM',100,'253.7',getdate()),
(832,'BBD',100,'644.2',getdate()),
(833,'BBK',100,'1070',getdate()),
(834,'AAB',100,'343.8',getdate()),
(835,'AAF',100,'533.8',getdate()),
(836,'AAK',100,'177.1',getdate()),
(837,'AAD',100,'148',getdate()),
(838,'BBH',100,'664.8',getdate()),
(839,'AAE',100,'788.2',getdate()),
(840,'AAJ',100,'176.1',getdate()),
(841,'AAB',100,'388.3',getdate()),
(842,'BBB',100,'768',getdate()),
(843,'BBD',100,'619.7',getdate()),
(844,'AAI',100,'744.7',getdate()),
(845,'BBJ',100,'352.3',getdate()),
(846,'AAD',100,'144.2',getdate()),
(847,'AAJ',100,'202.3',getdate()),
(848,'BBJ',100,'309.4',getdate()),
(849,'AAJ',100,'168.6',getdate()),
(850,'AAK',100,'217.9',getdate()),
(851,'BBF',100,'1073.3',getdate()),
(852,'BBH',100,'660.6',getdate()),
(853,'AAE',100,'783.7',getdate()),
(854,'AAG',100,'995.9',getdate()),
(855,'AAM',100,'223.5',getdate()),
(856,'AAI',100,'737.8',getdate()),
(857,'BBC',100,'1036.2',getdate()),
(858,'AAG',100,'1009.9',getdate()),
(859,'BBG',100,'868.5',getdate()),
(860,'AAA',100,'330.6',getdate()),
(861,'AAL',100,'328.7',getdate()),
(862,'AAG',100,'1010.8',getdate()),
(863,'AAM',100,'200.4',getdate()),
(864,'AAA',100,'279',getdate()),
(865,'AAI',100,'823.1',getdate()),
(999,'BBJ',100,'351.9',getdate()),
(1000,'AAA',100,'267.3',getdate()),
(1,'BBF',100,'1005.3',getdate()),
(2,'AAH',100,'230.4',getdate()),
(3,'AAK',100,'195.9',getdate()),
(4,'BBI',100,'808.5',getdate()),
(5,'AAB',100,'381.9',getdate()),
(6,'AAC',100,'813.1',getdate()),
(7,'BBJ',100,'283',getdate()),
(8,'BBG',100,'827.3',getdate()),
(9,'AAM',100,'196.7',getdate()),
(10,'AAK',100,'177.5',getdate()),
(11,'AAK',100,'173.6',getdate()),
(12,'AAC',100,'845.3',getdate()),
(13,'AAJ',100,'221',getdate()),
(14,'AAK',100,'151.2',getdate()),
(15,'AAF',100,'601.7',getdate()),
(16,'BBL',100,'503.9',getdate()),
(17,'AAJ',100,'216.8',getdate()),
(18,'BBL',100,'539.9',getdate()),
(19,'BBE',100,'389.1',getdate()),
(20,'AAG',100,'1060',getdate()),
(21,'AAD',100,'149.4',getdate()),
(22,'AAA',100,'323.6',getdate()),
(23,'BBI',100,'729.3',getdate()),
(24,'AAI',100,'741.1',getdate()),
(25,'BBL',100,'515.1',getdate()),
(26,'AAD',100,'117.6',getdate()),
(27,'BBB',100,'782.9',getdate()),
(28,'AAG',100,'1022.4',getdate()),
(29,'BBB',100,'779',getdate()),
(30,'AAI',100,'749.1',getdate()),
(31,'BBK',100,'1072',getdate()),
(32,'AAE',100,'793.1',getdate()),
(33,'BBI',100,'788.9',getdate()),
(34,'AAA',100,'264.4',getdate()),
(35,'BBC',100,'984.6',getdate()),
(36,'AAM',100,'255.2',getdate()),
(37,'AAA',100,'253.4',getdate()),
(38,'BBL',100,'529.5',getdate()),
(39,'AAM',100,'194.1',getdate()),
(40,'BBL',100,'483.6',getdate()),
(41,'AAF',100,'574.3',getdate()),
(42,'AAJ',100,'149.1',getdate()),
(43,'AAG',100,'1026.7',getdate()),
(44,'BBE',100,'378',getdate()),
(45,'AAA',100,'301.7',getdate()),
(46,'AAE',100,'796.8',getdate()),
(47,'BBL',100,'450.7',getdate()),
(48,'BBF',100,'1046.1',getdate()),
(49,'BBB',100,'782.8',getdate()),
(50,'AAB',100,'387.9',getdate()),
(51,'AAE',100,'817.8',getdate()),
(52,'BBG',100,'856.6',getdate()),
(53,'BBG',100,'863.1',getdate()),
(54,'BBJ',100,'336.3',getdate()),
(55,'AAG',100,'1055.9',getdate()),
(56,'BBC',100,'981.5',getdate()),
(57,'AAL',100,'279.4',getdate()),
(58,'BBC',100,'1028.1',getdate()),
(59,'AAC',100,'805.6',getdate()),
(60,'AAI',100,'787.7',getdate()),
(61,'AAE',100,'734.6',getdate()),
(62,'BBB',100,'813.1',getdate()),
(63,'BBH',100,'605.3',getdate()),
(64,'AAE',100,'819.5',getdate()),
(65,'AAB',100,'394.8',getdate()),
(133,'AAK',100,'155.5',getdate()),
(134,'BBJ',100,'334.6',getdate()),
(135,'BBH',100,'598.1',getdate()),
(136,'BBJ',100,'353.4',getdate()),
(137,'AAG',100,'1010.2',getdate()),
(138,'BBG',100,'798.6',getdate()),
(139,'BBJ',100,'297.9',getdate()),
(140,'BBK',100,'1036.5',getdate()),
(141,'BBE',100,'396.9',getdate()),
(142,'BBG',100,'819.7',getdate()),
(143,'AAL',100,'333.6',getdate()),
(144,'AAI',100,'795.4',getdate()),
(145,'AAK',100,'147.1',getdate()),
(146,'AAH',100,'303.6',getdate()),
(147,'AAG',100,'995.3',getdate()),
(148,'AAI',100,'783.4',getdate()),
(149,'BBF',100,'1094.9',getdate()),
(150,'AAF',100,'529.4',getdate()),
(151,'AAI',100,'802',getdate()),
(152,'AAG',100,'978.6',getdate()),
(153,'AAD',100,'186',getdate()),
(154,'AAL',100,'278.2',getdate()),
(155,'BBB',100,'832.2',getdate()),
(156,'AAL',100,'348.3',getdate()),
(157,'AAK',100,'197.3',getdate()),
(158,'BBK',100,'1041',getdate()),
(159,'BBF',100,'1067.9',getdate()),
(160,'AAG',100,'986.8',getdate()),
(161,'BBK',100,'1055',getdate()),
(162,'AAE',100,'781.2',getdate()),
(163,'AAG',100,'993.7',getdate()),
(164,'AAC',100,'792.6',getdate()),
(165,'AAB',100,'347.4',getdate()),
(166,'AAM',100,'197.6',getdate()),
(167,'AAI',100,'783.5',getdate()),
(168,'AAF',100,'571',getdate()),
(169,'AAC',100,'874.8',getdate()),
(170,'AAJ',100,'191.2',getdate()),
(171,'BBJ',100,'354.8',getdate()),
(172,'AAG',100,'985.8',getdate()),
(173,'AAL',100,'328.5',getdate()),
(174,'BBG',100,'821.5',getdate()),
(175,'BBC',100,'1052.4',getdate()),
(176,'BBC',100,'1024.1',getdate()),
(177,'AAH',100,'248.4',getdate()),
(178,'AAD',100,'182.4',getdate()),
(179,'BBL',100,'525.5',getdate()),
(180,'BBJ',100,'323.3',getdate()),
(181,'BBJ',100,'356.1',getdate()),
(182,'AAE',100,'805.7',getdate()),
(183,'BBD',100,'640.8',getdate()),
(184,'BBL',100,'458.1',getdate()),
(185,'AAA',100,'296.8',getdate()),
(186,'BBL',100,'481',getdate()),
(187,'AAM',100,'263.8',getdate()),
(188,'AAD',100,'131.6',getdate()),
(189,'BBG',100,'861.3',getdate()),
(190,'AAI',100,'770.5',getdate()),
(191,'BBK',100,'1017.2',getdate()),
(192,'AAC',100,'880.3',getdate()),
(193,'BBH',100,'620.6',getdate()),
(194,'BBL',100,'461.8',getdate()),
(195,'BBE',100,'380.6',getdate()),
(196,'AAM',100,'224.2',getdate()),
(197,'BBE',100,'328.8',getdate()),
(198,'BBB',100,'820.2',getdate()),
(199,'BBI',100,'764.1',getdate()),
(200,'BBD',100,'610.8',getdate()),
(201,'BBK',100,'1038.1',getdate()),
(202,'BBK',100,'1066',getdate()),
(203,'AAG',100,'1030.6',getdate()),
(204,'AAG',100,'1007.1',getdate()),
(205,'AAB',100,'342.1',getdate()),
(206,'AAM',100,'243.1',getdate()),
(207,'AAB',100,'359.4',getdate()),
(208,'AAM',100,'221.7',getdate()),
(209,'BBD',100,'616.9',getdate()),
(210,'AAH',100,'238.2',getdate()),
(211,'AAE',100,'782.7',getdate()),
(212,'BBJ',100,'361.8',getdate()),
(213,'BBK',100,'1056',getdate()),
(214,'AAK',100,'145.6',getdate()),
(215,'AAF',100,'541.5',getdate()),
(216,'AAA',100,'310.9',getdate()),
(217,'AAF',100,'535',getdate()),
(218,'AAJ',100,'142',getdate()),
(219,'AAD',100,'152.6',getdate()),
(220,'AAM',100,'270',getdate()),
(221,'AAK',100,'153.9',getdate()),
(222,'AAD',100,'138.9',getdate()),
(223,'AAK',100,'191.6',getdate()),
(292,'AAC',100,'803.8',getdate()),
(293,'BBB',100,'784.4',getdate()),
(294,'BBL',100,'497.1',getdate()),
(295,'AAK',100,'211.2',getdate()),
(296,'BBF',100,'1095',getdate()),
(297,'AAB',100,'385.2',getdate()),
(298,'AAC',100,'834.3',getdate()),
(299,'BBJ',100,'362',getdate()),
(300,'AAI',100,'816.7',getdate()),
(301,'AAB',100,'377.1',getdate()),
(302,'AAF',100,'583.4',getdate()),
(303,'AAE',100,'760.6',getdate()),
(304,'BBJ',100,'357.4',getdate()),
(305,'BBD',100,'657.5',getdate()),
(306,'AAA',100,'261.3',getdate()),
(307,'BBG',100,'860.7',getdate()),
(308,'BBC',100,'1025.6',getdate()),
(309,'AAD',100,'150.1',getdate()),
(310,'BBJ',100,'365.3',getdate()),
(311,'BBI',100,'803.3',getdate()),
(312,'BBH',100,'667.4',getdate()),
(313,'AAJ',100,'164.8',getdate()),
(314,'AAA',100,'283.6',getdate()),
(315,'BBC',100,'1052.3',getdate()),
(316,'AAD',100,'173.6',getdate()),
(317,'AAJ',100,'162.6',getdate()),
(318,'BBC',100,'987.4',getdate()),
(319,'BBI',100,'764.6',getdate()),
(320,'AAA',100,'288.7',getdate()),
(321,'AAB',100,'365.3',getdate()),
(322,'AAF',100,'575.1',getdate()),
(323,'BBH',100,'646.8',getdate()),
(324,'BBG',100,'858.8',getdate()),
(325,'BBH',100,'643.1',getdate()),
(326,'BBB',100,'794.3',getdate()),
(327,'BBJ',100,'354',getdate()),
(328,'AAD',100,'176.3',getdate()),
(329,'AAA',100,'304.4',getdate()),
(330,'BBE',100,'310.7',getdate()),
(331,'AAC',100,'799.1',getdate()),
(332,'AAH',100,'288.1',getdate()),
(333,'AAL',100,'274.9',getdate()),
(334,'AAF',100,'592.1',getdate()),
(335,'AAD',100,'180.5',getdate()),
(336,'BBF',100,'1070.2',getdate()),
(337,'BBI',100,'729.3',getdate()),
(338,'AAA',100,'308.2',getdate()),
(339,'AAL',100,'362.3',getdate()),
(340,'AAA',100,'251.5',getdate()),
(341,'AAA',100,'284.4',getdate()),
(342,'AAJ',100,'205.7',getdate()),
(343,'BBB',100,'808.3',getdate()),
(344,'AAJ',100,'132.4',getdate()),
(345,'AAB',100,'350.9',getdate()),
(346,'AAK',100,'153.9',getdate()),
(347,'AAI',100,'805.7',getdate()),
(348,'AAH',100,'298.8',getdate()),
(349,'BBG',100,'835.6',getdate()),
(350,'AAI',100,'753.1',getdate()),
(351,'BBI',100,'788.2',getdate()),
(352,'AAE',100,'776.5',getdate()),
(353,'BBG',100,'790.8',getdate()),
(354,'AAD',100,'154.3',getdate()),
(355,'AAG',100,'1015.2',getdate()),
(356,'BBK',100,'1007.9',getdate()),
(357,'AAI',100,'817.8',getdate()),
(358,'AAK',100,'172.1',getdate()),
(359,'AAI',100,'750.4',getdate()),
(360,'BBL',100,'470',getdate()),
(361,'AAI',100,'761.9',getdate()),
(362,'AAM',100,'197.8',getdate()),
(363,'BBE',100,'398.2',getdate()),
(364,'BBG',100,'820.5',getdate()),
(365,'AAD',100,'191.3',getdate()),
(366,'BBL',100,'461',getdate()),
(367,'BBC',100,'1057',getdate()),
(368,'AAK',100,'181.4',getdate()),
(369,'AAM',100,'242.9',getdate()),
(370,'AAI',100,'753.4',getdate()),
(371,'BBL',100,'512.6',getdate()),
(372,'BBI',100,'787.2',getdate()),
(373,'AAG',100,'996.3',getdate()),
(374,'AAC',100,'821.2',getdate()),
(375,'AAE',100,'736.7',getdate()),
(376,'BBL',100,'510.1',getdate()),
(377,'BBE',100,'317.2',getdate()),
(378,'BBF',100,'1074.4',getdate()),
(379,'BBD',100,'653.5',getdate()),
(380,'BBL',100,'492.3',getdate()),
(381,'BBE',100,'360.2',getdate()),
(382,'AAG',100,'979.2',getdate()),
(383,'AAJ',100,'144.8',getdate()),
(384,'AAK',100,'169.7',getdate()),
(385,'BBK',100,'1039.2',getdate()),
(386,'BBK',100,'1061.8',getdate()),
(387,'BBI',100,'784.9',getdate()),
(388,'AAI',100,'782.7',getdate()),
(389,'AAF',100,'553.2',getdate()),
(390,'BBE',100,'378.1',getdate()),
(391,'AAE',100,'804.5',getdate()),
(392,'AAA',100,'295',getdate()),
(393,'BBE',100,'355.3',getdate()),
(394,'AAC',100,'859.8',getdate()),
(395,'AAF',100,'588.9',getdate()),
(396,'AAE',100,'758',getdate()),
(397,'BBF',100,'1079.6',getdate()),
(398,'AAJ',100,'140.6',getdate()),
(399,'BBE',100,'318.9',getdate()),
(400,'AAH',100,'242.1',getdate()),
(401,'BBK',100,'1032.5',getdate()),
(402,'AAA',100,'265.9',getdate()),
(403,'BBK',100,'1024.5',getdate()),
(404,'BBE',100,'343.4',getdate()),
(405,'BBE',100,'355.5',getdate()),
(406,'AAM',100,'186.2',getdate()),
(407,'BBB',100,'786.4',getdate()),
(408,'AAH',100,'291.1',getdate()),
(409,'BBJ',100,'315.4',getdate()),
(410,'BBJ',100,'345.5',getdate()),
(411,'AAB',100,'405',getdate()),
(412,'AAE',100,'746.3',getdate()),
(413,'AAJ',100,'216.9',getdate()),
(414,'BBK',100,'1071.1',getdate()),
(415,'AAL',100,'331.8',getdate()),
(416,'BBE',100,'333.4',getdate()),
(417,'BBB',100,'852.2',getdate()),
(418,'AAI',100,'784.2',getdate()),
(419,'BBG',100,'788.4',getdate()),
(420,'AAI',100,'760.5',getdate()),
(421,'BBG',100,'826.9',getdate()),
(422,'BBJ',100,'316.7',getdate()),
(423,'AAD',100,'130',getdate()),
(424,'BBF',100,'1083.9',getdate()),
(425,'BBB',100,'798.8',getdate()),
(426,'AAC',100,'803.8',getdate()),
(427,'AAK',100,'142.4',getdate()),
(428,'AAB',100,'362.7',getdate()),
(429,'AAM',100,'238.1',getdate()),
(430,'AAE',100,'801.2',getdate()),
(431,'AAK',100,'205.2',getdate()),
(432,'AAH',100,'313.5',getdate()),
(599,'BBF',100,'1033.1',getdate()),
(600,'AAE',100,'761.3',getdate()),
(601,'AAK',100,'180.3',getdate()),
(602,'BBJ',100,'280.2',getdate()),
(603,'BBI',100,'815.2',getdate()),
(604,'BBB',100,'806.8',getdate()),
(605,'AAK',100,'171',getdate()),
(606,'BBK',100,'1010.9',getdate()),
(607,'BBL',100,'508.4',getdate()),
(608,'AAE',100,'775.5',getdate()),
(609,'AAI',100,'806.8',getdate()),
(610,'BBC',100,'1028.2',getdate()),
(611,'BBJ',100,'349.4',getdate()),
(612,'BBH',100,'621.9',getdate()),
(613,'AAE',100,'803.6',getdate()),
(614,'AAG',100,'1055.8',getdate()),
(615,'AAD',100,'153.5',getdate()),
(616,'AAC',100,'853.8',getdate()),
(617,'BBB',100,'786.8',getdate()),
(618,'AAK',100,'198.2',getdate()),
(619,'AAH',100,'228.7',getdate()),
(620,'AAB',100,'409.3',getdate()),
(621,'BBE',100,'386.3',getdate()),
(622,'BBC',100,'1032.6',getdate()),
(623,'BBK',100,'1011.7',getdate()),
(624,'BBG',100,'828.4',getdate()),
(625,'BBF',100,'1065.2',getdate()),
(626,'AAL',100,'319.8',getdate()),
(627,'AAE',100,'764.5',getdate()),
(628,'BBL',100,'461.5',getdate()),
(629,'BBI',100,'810.3',getdate()),
(630,'AAC',100,'804.9',getdate()),
(631,'BBL',100,'515.4',getdate()),
(632,'AAI',100,'750.4',getdate()),
(633,'AAD',100,'126.8',getdate()),
(634,'AAE',100,'821.2',getdate()),
(635,'BBI',100,'767.3',getdate()),
(636,'AAB',100,'379',getdate()),
(637,'AAE',100,'815.4',getdate()),
(638,'AAH',100,'297.9',getdate()),
(639,'BBB',100,'839.3',getdate()),
(640,'AAE',100,'764.4',getdate()),
(641,'BBG',100,'836.2',getdate()),
(642,'AAC',100,'809.8',getdate()),
(643,'BBF',100,'1085.8',getdate()),
(644,'BBJ',100,'318.8',getdate()),
(645,'AAM',100,'211.2',getdate()),
(646,'BBK',100,'1015.1',getdate()),
(647,'BBE',100,'324.7',getdate()),
(648,'BBL',100,'518',getdate()),
(649,'AAK',100,'167.9',getdate()),
(650,'BBD',100,'619',getdate()),
(651,'BBD',100,'609.6',getdate()),
(652,'AAL',100,'347.8',getdate()),
(653,'AAF',100,'562.4',getdate()),
(654,'AAH',100,'254.7',getdate()),
(655,'BBI',100,'770.3',getdate()),
(656,'BBE',100,'385.3',getdate()),
(657,'BBB',100,'851.8',getdate()),
(658,'AAL',100,'314.4',getdate()),
(659,'BBB',100,'836.8',getdate()),
(660,'AAL',100,'358',getdate()),
(661,'BBK',100,'1068.9',getdate()),
(662,'BBL',100,'497.4',getdate()),
(663,'AAK',100,'181.5',getdate()),
(664,'BBJ',100,'333.7',getdate()),
(665,'BBD',100,'690.8',getdate()),
(666,'BBD',100,'663.7',getdate()),
(667,'AAI',100,'763.3',getdate()),
(668,'BBF',100,'1077.5',getdate()),
(669,'AAK',100,'170.9',getdate()),
(670,'BBC',100,'1044.4',getdate()),
(671,'AAG',100,'1046.8',getdate()),
(672,'BBB',100,'802.8',getdate()),
(673,'AAL',100,'354.6',getdate()),
(674,'BBL',100,'513.4',getdate()),
(675,'AAG',100,'1009.9',getdate()),
(676,'AAK',100,'187.8',getdate()),
(677,'BBI',100,'802',getdate()),
(678,'AAE',100,'784.4',getdate()),
(679,'AAI',100,'754.4',getdate()),
(680,'BBJ',100,'328.2',getdate()),
(681,'AAA',100,'277.2',getdate()),
(682,'BBK',100,'1061.7',getdate()),
(683,'AAE',100,'795.5',getdate()),
(684,'AAM',100,'253.9',getdate()),
(685,'AAA',100,'257.3',getdate()),
(686,'BBC',100,'1049.1',getdate()),
(687,'BBK',100,'1071.3',getdate()),
(688,'AAG',100,'1051.4',getdate()),
(689,'AAF',100,'568.1',getdate()),
(690,'AAF',100,'573.4',getdate()),
(701,'BBC',100,'1038',getdate()),
(702,'BBJ',100,'309.4',getdate()),
(703,'AAI',100,'761.4',getdate()),
(704,'BBL',100,'502.6',getdate()),
(705,'BBH',100,'609.9',getdate()),
(706,'BBC',100,'1021.6',getdate()),
(707,'AAM',100,'268.5',getdate()),
(708,'AAE',100,'801.1',getdate()),
(709,'BBG',100,'870',getdate()),
(710,'BBE',100,'383.1',getdate()),
(711,'AAD',100,'113.7',getdate()),
(712,'BBC',100,'1006.1',getdate()),
(713,'BBJ',100,'297.4',getdate()),
(714,'BBJ',100,'298.7',getdate()),
(715,'BBI',100,'782.4',getdate()),
(716,'AAE',100,'739.6',getdate()),
(717,'BBH',100,'651.2',getdate()),
(718,'BBF',100,'1077.9',getdate()),
(719,'AAB',100,'368.8',getdate()),
(720,'AAH',100,'228.1',getdate()),
(721,'BBG',100,'866.3',getdate()),
(722,'AAL',100,'316.9',getdate()),
(723,'AAH',100,'249',getdate()),
(724,'AAD',100,'189.2',getdate()),
(725,'BBF',100,'1023.8',getdate()),
(726,'BBC',100,'997.1',getdate()),
(727,'AAI',100,'775.8',getdate()),
(728,'BBH',100,'666.1',getdate()),
(729,'BBJ',100,'278.8',getdate()),
(730,'AAJ',100,'159.2',getdate()),
(740,'BBI',100,'776.5',getdate()),
(741,'BBG',100,'870.1',getdate())
GO
Populate the Order Type Table
INSERT INTO [dbo].[OrderType] ([Name])
VALUES ('BUY'), ('SELL')
GO
Populate the Risk Type Table
INSERT INTO [dbo].[RiskType] ([Name])
VALUES ('LOW'), ('MEDIUM'), ('HIGH')
GO
Populate the Account Manager Table
INSERT INTO [dbo].[AccountManager] ([FirstName],[LastName],[RiskSpecialty])
VALUES ('John', 'Doe', 1), ('Steve', 'Smith', 2), ('Jane', 'Doe', 3)
GO
Populate the Customer Account Table
INSERT INTO [dbo].[CustomerAccount] ([FirstName],[LastName],[Email],[Address],[City],[State],[Zip],[Phone])
VALUES
('Peter',
'James',
'pjames@hotmail.com',
'111 Newport Way',
'Nowhere',
'NV',
'99999',
'555-555-5555'),
('David',
'Smith',
'davidsmith@hotmail.com',
'101 Bellevue Way',
'Bellevue',
'WA',
'99999',
'444-444-4444'),
('Daniel',
'Peterson',
'danp@hotmail.com',
'2751 North 1250 East',
'Denver',
'CO',
'99999',
'333-333-3333')
GO
Populate the Managed Account Table
INSERT INTO [dbo].[ManagedAccount] ([Name],[RiskLevel],[CashAvailable])
VALUES
('401K', 3, '1050000.00'),
('Personal', 2, '1100000.00'),
('IRA', 1, '2030000.00')
GO
Populate the Cuatomer Account Managed Account Relation Table
INSERT INTO [dbo].[CustomerAccountManagedAccount]
([CustomerAccountID],[ManagedAccountID],[AccountManagerID])
VALUES (1,1,3), (2,2,2), (3,3,1)
GO
Populate the Managed Account Positions Table (example)
INSERT INTO [dbo].[ManagedAccountPositions] ([ManagedAccountID],[CompanySymbol],[Shares],[Date])
VALUES
(1,'AAA',1000,getdate()),
(1,'AAB',1000,getdate()),
(1,'AAC',1000,getdate())
Populate the Managed Account Open Orders Table (example)
INSERT INTO [dbo].[ManagedAccountOpenOrders]
([ManagedAccountID],[OrderType],[CompanySymbol],[Shares],[Price])
VALUES
(3,2,'BBF',100,1005.3),
(3,2,'AAH',100,230.4),
(3,2,'AAK',100,195.9)
Populate the ManagedAccountCompletedTransactions Table (example)
INSERT INTO [dbo].[ManagedAccountCompletedTransactions] ([OpenOrderID],[CompanySymbol],[Shares],[Price],[CreatedDate])
VALUES
(742,'AAH',100,'309.4',getdate()),
(743,'AAH',100,'301.7',getdate()),
(744,'BBJ',100,'339.4',getdate())
Execute the Read Only Queries against the Listener (load balanced)
Setup a connection to the listener
Add MultiSubnetFailover=true;ApplicationIntent=readonly; to the connection string and connect
Additional Connection Parameters (Copy/Paste)
MultiSubnetFailover=true;ApplicationIntent=readonly;
Query for all Customer Account Information
USE [FabrikamInvestmentsDb]
GO
-- Select all information relating to a Customer Account
SELECT DISTINCT 'Customer Account Information', ma.Name, ca.*, rt1.Name AS RiskLevel, am.FirstName, am.LastName, rt2.Name AS RiskSpecialty, map.CompanySymbol, map.Shares
FROM CustomerAccount ca
JOIN CustomerAccountManagedAccount cama ON cama.CustomerAccountID = ca.ID
JOIN ManagedAccount ma ON ma.ID = cama.ManagedAccountID
JOIN RiskType rt1 ON rt1.ID = ma.RiskLevel
JOIN AccountManager am ON am.ID = cama.AccountManagerID
JOIN RiskType rt2 ON rt2.ID = am.RiskSpecialty
JOIN ManagedAccountPositions map ON map.ManagedAccountID = ma.ID
JOIN ManagedAccountOpenOrders cmaoo ON cmaoo.ManagedAccountID = ma.ID
ORDER BY ca.ID
GO
Query for Open Orders by Company
-- Query Open Orders by Company Symbol
SELECT DISTINCT 'Open Orders by Company', CompanySymbol, OrderType, Count(*) AS Count
FROM ManagedAccountOpenOrders
WHERE Completed = 0
GROUP BY CompanySymbol, OrderType
ORDER BY CompanySymbol ASC
GO
Query for Customer's Stock Positions
-- Query Customer Positions
SELECT DISTINCT 'Customer Positions', ma.Name, ca.*, am.FirstName, am.LastName, map.CompanySymbol, map.Shares
FROM CustomerAccount ca
JOIN CustomerAccountManagedAccount cama ON cama.CustomerAccountID = ca.ID
JOIN ManagedAccount ma ON ma.ID = cama.ManagedAccountID
JOIN RiskType rt1 ON rt1.ID = ma.RiskLevel
JOIN AccountManager am ON am.ID = cama.AccountManagerID
JOIN RiskType rt2 ON rt2.ID = am.RiskSpecialty
JOIN ManagedAccountPositions map ON map.ManagedAccountID = ma.ID
ORDER BY ca.ID
GO
Query for Customer's Open Orders
-- Query Customer Open Orders
SELECT DISTINCT 'Customer Open Orders', ma.Name, ca.*, am.FirstName, am.LastName, ot.Name, maoo.CompanySymbol, maoo.Shares
FROM CustomerAccount ca
JOIN CustomerAccountManagedAccount cama ON cama.CustomerAccountID = ca.ID
JOIN ManagedAccount ma ON ma.ID = cama.ManagedAccountID
JOIN RiskType rt1 ON rt1.ID = ma.RiskLevel
JOIN AccountManager am ON am.ID = cama.AccountManagerID
JOIN RiskType rt2 ON rt2.ID = am.RiskSpecialty
JOIN ManagedAccountOpenOrders maoo ON maoo.ManagedAccountID = ma.ID AND maoo.Completed = 0
JOIN OrderType ot ON ot.ID = maoo.OrderType
ORDER BY ca.ID
GO
Query for Customer's Completed Orders
-- Query Customer Completed Orders
SELECT DISTINCT 'Customer Completed Orders', ma.Name, ca.*, am.FirstName, am.LastName, ot.Name, maoo.CompanySymbol, maoo.Shares
FROM CustomerAccount ca
JOIN CustomerAccountManagedAccount cama ON cama.CustomerAccountID = ca.ID
JOIN ManagedAccount ma ON ma.ID = cama.ManagedAccountID
JOIN RiskType rt1 ON rt1.ID = ma.RiskLevel
JOIN AccountManager am ON am.ID = cama.AccountManagerID
JOIN RiskType rt2 ON rt2.ID = am.RiskSpecialty
JOIN ManagedAccountOpenOrders maoo ON maoo.ManagedAccountID = ma.ID AND maoo.Completed = 1
JOIN OrderType ot ON ot.ID = maoo.OrderType
ORDER BY ca.ID
GO
Query for the Most Active Stocks (Fabrikam Transactions)
-- Query for the Most Active stocks
SELECT TOP 3 'Most Active Stocks', [CompanySymbol], SUM(Shares) AS 'Shares Traded'
FROM [ManagedAccountOpenOrders]
WHERE [Completed] = 1
GROUP BY [CompanySymbol]
ORDER BY SUM(Shares) DESC
GO
Query for the Top and Bottom Movers
-- Top/Bottom Movers
-- Create a temp table with the first and last transactions for each company
SELECT * INTO #TempTable FROM (
SELECT maoo1.ID, maoo1.[CompanySymbol], maoo1.[Price]
FROM [ManagedAccountOpenOrders] maoo1
JOIN (
SELECT [CompanySymbol], MAX(ID) AS ID
FROM [ManagedAccountOpenOrders]
WHERE [Completed] = 1 AND OrderType = 2
GROUP BY [CompanySymbol]
) maoo2 ON ( maoo1.ID = maoo2.ID)
UNION
SELECT maoo1.ID, maoo1.[CompanySymbol], maoo1.[Price]
FROM [ManagedAccountOpenOrders] maoo1
JOIN (
SELECT [CompanySymbol], MIN(ID) AS ID
FROM [ManagedAccountOpenOrders]
WHERE [Completed] = 1 AND OrderType = 2
GROUP BY [CompanySymbol]
)
maoo2 ON ( maoo1.ID = maoo2.ID )
) AS Temp;
-- Query the temp table for the Biggest Gainers
SELECT TOP 5 t1.CompanySymbol, t2.Price - t1.Price AS 'Gain'
FROM #TempTable t1
JOIN #TempTable t2 ON t2.Price > t1.Price AND t1.CompanySymbol = t2.CompanySymbol AND t2.ID > t1.ID
ORDER BY t2.Price - t1.Price DESC
-- Query the temp table for the Biggest Losers
SELECT TOP 5 t1.CompanySymbol, t2.Price - t1.Price AS 'Loss'
FROM #TempTable t1
JOIN #TempTable t2 ON t2.Price < t1.Price AND t1.CompanySymbol = t2.CompanySymbol AND t2.ID > t1.ID
ORDER BY t2.Price - t1.Price ASC
DROP TABLE #TempTable
GO
Connect to the Primary SQL Server
Clear the Additional Connection Parameters and click Connect
Execute the Process Open Orders SQL script
USE [FabrikamInvestmentsDb]
GO
-------------------------------------------------------------------------------------------------
-- NOTE: This script assumes all orders transact 100 shares of stock.
-------------------------------------------------------------------------------------------------
DECLARE @ID [bigint]
DECLARE @OpenOrderID [bigint]
DECLARE @Shares [int]
DECLARE @Price [money]
DECLARE @ManagedAccountID [bigint]
DECLARE @OrderType [int]
DECLARE @CashAvailable [money]
-- Display the current number of pending transactions
SELECT 'Before Processing Completed Transactions', count(*)
FROM [ManagedAccountCompletedTransactions]
DECLARE CompletedOrderCursor CURSOR
FOR
SELECT [ID],[OpenOrderID],[Shares],[Price]
FROM [ManagedAccountCompletedTransactions]
OPEN CompletedOrderCursor
WHILE (1=1)
BEGIN
FETCH NEXT FROM CompletedOrderCursor INTO @ID, @OpenOrderID, @Shares, @Price
IF @@FETCH_STATUS <> 0
BREAK
-- Start the Settlement Transaction
BEGIN TRAN StockSettlement
-- Update the open orders to completed
UPDATE [ManagedAccountOpenOrders] WITH (Snapshot)
SET [Completed] = 1
WHERE [ID] = @OpenOrderID
-- Get the Open Order Transaction Information
SELECT @ManagedAccountID = maoo.[ID],
@OrderType = maoo.[OrderType],
@CashAvailable = ma.[CashAvailable]
FROM [ManagedAccountOpenOrders] maoo
JOIN [ManagedAccount] ma ON ma.ID = maoo.[ManagedAccountID]
WHERE maoo.[ID] = @OpenOrderID
-- Buy Order
IF @OrderType = 1
BEGIN
UPDATE [ManagedAccount]
SET [CashAvailable] = [CashAvailable] -
(@Price * CAST(@Shares AS DECIMAL))
WHERE [ID] = @ManagedAccountID
END
ELSE
-- Sell Order
BEGIN
UPDATE [ManagedAccount]
SET [CashAvailable] = [CashAvailable] +
(@Price * AST(@Shares AS ECIMAL))
WHERE [ID] = @ManagedAccountID
END
-- Complete the Open Order Transaction
UPDATE [ManagedAccountOpenOrders]
SET [Completed] = 1
WHERE [ID] = @OpenOrderID
-- Delete the transaction from the ManagedAccountCompletedTransactions table
DELETE FROM [ManagedAccountCompletedTransactions]
WHERE ID = @ID
-- Commit the Settlement Transaction
COMMIT TRAN StockSettlement
END
-- Clean up
CLOSE CompletedOrderCursor
DEALLOCATE CompletedOrderCursor
-- Make sure all the transactions were processed (should be 0)
SELECT 'After Processing Completed Transactions', count(*)
FROM [ManagedAccountCompletedTransactions]
GO
Enhanced Always On:
In Memory Column Store Indexes:
Query Store: