USE VanDam

IF object_id('IsSystematic', 'u') IS NOT NULL
  DROP TABLE IsSystematic

SELECT
  trAVAm.ExchProfile                                                                                   AS [ECN],
  MIN(EntryDate)                                                                                       AS [StartDate],
  MAX(EntryDate)                                                                                       AS [EndDate],
  COUNT(
      *)                                                                                               AS [Total Numbers of Trades],
  SUM(
      trAVAm.Volume)                                                                                   AS [Total Number of Shares Traded],
  trAVAm.LiqFlag,
  trAVAm.LiqInd,
  stats.Volume                                                                                         AS [Matched Volume],
  [Route Scheduled]                                                                                    AS [Real Rate Per Share for Flag],
  NULL                                                                                                 AS [Lek Charges Per Share for Flag],
  SUM(trAVAm.Volume *
      [Route Scheduled])                                                                               AS [Real Charge $],
  SUM(
      [LEK Charged])                                                                                   AS [Lek Charge $],
  -1 * Round(SUM(TotalDifference),
             2)                                                                                        AS [Lek's Overcharge $ (Lek Charge - Real Charge)],
  NULL                                                                                                 AS [Link To Official Exchange Document Re Charging],
  FullName                                                                                             AS [Notes],
  AVG(
      AvalonVolume)                                                                                    AS [AVG AvalonVolume],
  AVG(CADV)                                                                                            AS [AVG CADV],
  (SUM(Indicators.[Added Displayed Volume]) + SUM(Indicators.[Added Non-Displayed Volume])) /
      SUM(TCV_data.CADV)                                                                                   AS [AVG ADAV as percent],
  --   AVG([ADRV as percent])              AS [AVG ADRV as percent],
  AVG(
      [AddedVolume])                                                                                   AS [AVG AddedVolume]
INTO IsSystematic
FROM (
       SELECT *
       FROM trAVAm_extended_20161025
       WHERE LiqFlag IS NOT NULL AND TotalDifference IS NOT NULL AND TotalDifference < 0) AS [trAVAm]
  LEFT JOIN RM_TierCodes AS [TierCodes]
    ON TierCodes.Route = trAVAm.Route
       AND TierCodes.ExchProfile = trAVAm.ExchProfile
       AND TierCodes.TierCode = trAVAm.Tier
  LEFT JOIN Indicators_new AS [Indicators]
    ON MONTH(trAVAm.EntryDate) = Month(Month)
       AND YEAR(EntryDate) = YEAR(Month)
       AND Indicators.Route = trAVAm.Route
       AND Indicators.ExchProfile = trAVAm.ExchProfile
  LEFT JOIN TCV AS [TCV_data]
    ON Month(Indicators.Month) = TCV_data.Month
       AND Year(Indicators.Month) = TCV_data.Year
  LEFT JOIN (
              SELECT DISTINCT
                [ExchProfile],
                [LiqInd],
                [LiqFlag],
                [Volume]
              FROM [VanDam].[dbo].[ProvedMathcing_with_stats]) AS stats
    ON trAVAm.ExchProfile = stats.ExchProfile
       --        AND LiqFlag = stats.LiqFlag
       AND trAVAm.LiqInd = stats.LiqInd


WHERE trAVAm.LiqFlag IS NOT NULL AND TotalDifference IS NOT NULL AND trAVAm.TotalDifference < 0
GROUP BY trAVAm.ExchProfile, trAVAm.LiqFlag, trAVAm.LiqInd, [Route Scheduled], FullName, stats.Volume


SELECT *
FROM IsSystematic