data

[Quote]:
 Load *, autonumber(REDUCTION&'_'&year(QuoteEffectiveDate)) as %keyNameYear;
SELECT D.[QuoteID]
      ,D.[NamedInsured]
      ,UPPER(D.[AcctExec]) as REDUCTION
      ,D.[Quoted]
      ,D.[OpenItem]
      ,D.[PolicyID]
      ,CONVERT(DATE,D.[Effective]) as QuoteEffectiveDate
      ,D.[InsuredID]
      ,D.[VersionBound]
      ,D.[Bound]
      ,D.[ProducerID]
      ,D.[CoverageID]
      ,D.[ActivePolicyFlag]
      ,D.[Renewal]
      ,D.[DivisionID]
      ,D.[SicID]
      ,D.[OrganizationID]
 FROM [CIS].[dbo].[dvQuoteView] D
 LEFT JOIN CIS..UserID U ON D.AcctExec = U.UserID
 WHERE D.DivisionID IN ('1','2','6');

[Version]:
SELECT [QuoteID]
      ,[Version]
      ,[VerOriginal]
      ,[VersionCompanyID]
      ,[ProductID]
      ,[MarketID]
      ,[CoverageName]
      ,[CompanyName]
      ,[MarketName]
FROM [CIS].[dbo].[dvVersionView];

[Invoices]:
SELECT
    I.QuoteID,
    CONVERT(DATE,I.AccountingEffectiveDate) as InvoiceEffectiveDate,
    SUM(ISNULL(I.TotalGrossComm, 0)) - SUM(ISNULL(I.TotalAgentComm, 0)) + SUM(ISNULL(I.TotalFeeRevenue, 0)) AS Revenue,
    SUM(ISNULL(I.Premium,0)) as Premium
FROM
    CIS..InvoiceHeader I
where I.InvoiceID not like '%-M' AND (I.STATUSID='P' OR I.STATUSID='E' ) AND I.DivisionID IN ('1','2','6') AND I.AccountingEffectiveDate >= '2016-01-01'
GROUP BY I.QuoteID, I.AccountingEffectiveDate;

[Users]:
select UPPER(U.UserID) as REDUCTION, U.Name as BrokerName  from Cis..UserID U;

[Division]:
  SELECT
  [DivisionID],
  [Name]
  FROM [CIS].[dbo].[Division]
  Where DivisionID IN ('1','2','6');

[BrokerFlow]:
 Load *, autonumber(REDUCTION&'_'&year(EffDate)) as %keyNameYear;
select
      P.BillToCd as AgentId,
      P.InsurerNAme as Insured,
      UPPER(P.ProducerCd) as REDUCTION,
      M.Market as marketName,
      P.Div as DivisionID,
      CONVERT(VARCHAR(10), P.LoeDate, 101)  AS EffDate,
      P.PolicyNumber as policy,
      CASE WHEN(TransType = N'NEW' OR
      TransType = N'CAN' OR TransType = N'END' OR TransType = N'CXL' OR TransType = N'REW' OR
      TransType = N'ADD' OR TransType = N'RET' OR TransType = N'NBS' OR
      TransType = N'RMID' OR TransType = N'REN') THEN P.InvoiceAmt ELSE 0 END as BFPremium,
      P.RevenueAmt as BFRevenue,
      CASE WHEN (TransType = N'NEW' OR TransType = N'NBS') THEN 'NEW' ELSE 'OTHER' END AS NEWFLAG,
      p.CoverageName as Coverage
      from BrokerFlow_NEW..tbl_premium_sagita P
      LEFT JOIN (
            SELECT N.PolicyNumber, MAX(N.PayeeName) as Market FROM BrokerFlow_NEW..tbl_premium_sagita N WHERE N.TransType IN ('NEW','CAN','END','CXL','REW','ADD','RET','NBS','RMID','REN') GROUP BY N.PolicyNumber
      ) M ON P.PolicyNumber = M.PolicyNumber
      where P.LoeDate >= '2011-01-01' AND P.LoeDate <= '2015-12-31' and P.Div in (1,2,6);