SQL Server 2016 Highly Efficient and Performant Database

Overview

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:

  • An In-Memory OLTP with AlwaysOn Availability Groups
    • Synchronous Replica in the primary datacenter for High Availability and Asynchronous Replicas in the secondary datacenter for disaster recovery
    • Read only load balancing to the read only secondary asynchronous replica databases
    • Rebalancing of load during failure conditions
  • Query Datastore for performance tuning and troubleshooting
  • ColumnStore Index on the asynchronous replicas to support high performance querying for reports and dashboards

DBA Considerations

For a Highly Efficient and Performant Database scenario, it is important to have an in depth understanding of:

  • Customer's HADR strategy
  • Performance tuning and optimization of queries of the relational structure of the database tables and how the data is being accessed for both the OLTP and the real time report and dashboard workloads

Highly Efficient and Performant Database Features

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.

Always On Availability Groups

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).

In Memory Column Store Indexes

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.

Example Scenario

Fabrikam Investments provide security portfolio management services for millions of customers in the US.

  • High performance - low-latency trading application and OLTP is hosted by HSP.
  • They have a need to develop real-time dashboards and reports harvesting data during the trading day showing:
    • Most Active Stocks.
    • Top and Bottom Movers
  • They have already implemented a HADR solution with both synchronous and asynchronous replicas in their datacenters.
  • HSP analyzes the workload and implements the reporting and dashboarding needs using the highly efficient and performant database offer.

Developing the Fabrikam Investments Deployment

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.

  • The new database will be created to include a memory optimized file group and container.
  • The new database tables will be constructed with non-clustered in memory column store indexes.

For more information see: Monitoring Performance By Using the Query Store

Implementation Steps

Fabrikam Database Table Relations

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

Adding the Fabrikam Investment Database to the Availability Group

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

Configure the Read Only Routing for the Availability Group

The following statements will configure the load balancing as follows:

  • Primary replica will be DATA1-SQL1
  • Round robin the read only workload across DATA2-SQL1 and DATA2-SQL2
  • If Site2-DC is down then send the read only workload to DATA1-SQL2
  • If DATA1-SQL2 becomes the new primary replica, then send the read only workload to DATA1-SQL1
  • If a failover occurs to Site2-DC and DATA2-SQL1 becomes the new primary replica then the read only workload should be sent to DATA1-SQL1 and DATA1-SQL2
  • If Site1-DC is down then send the read only workload to DATA2-SQL2
  • If DATA2-SQL2 becomes the new primary replica then send the read only workload to DATA2-SQL1

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

Creating the Sample Investment Tables

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

Populating the Investment Tables with sample data

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

Example Insert Statements from embedded SQL script

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())

Testing the new Fabrikam Investments Database

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;

Execute the Reporting Queries

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

Execute the Process Open Orders Script against the Primary SQL Server

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

References

Enhanced Always On:

In Memory Column Store Indexes:

Query Store: