declare @StartDate datetime, @Today datetime
select @StartDate = dbo.GetSimpleDate(DateAdd(dd,-7,GetDate())), @Today = GetDate()


select a.BrokerID
  , c.BrokerName
  , RetailPrice
--  , 25 / RetailPrice
  , NetSalesTotal
  , len(isNull(ContactStateOrProvinceList,0)) / 3 - 1 as StateCount
  , datediff(dd,c.CreatedDt,@Today) as BrokerDaysOld
  , NetSalesCount
from dbo.tb_Cached_AutocommitterBrokerRunningAccountBalances a (NOLOCK) inner join
	dbo.tb_BrokerMortgageLeadCriteria b on a.BrokerID = b.BrokerID inner join
  dbo.tb_Brokers c on a.BrokerID = c.BrokerID inner join
    (select a.BrokerID
        , sum(b.TransactionDetailAmount * c.TransactionMultiplyBy) as NetSalesTotal
        , count(1) as NetSalesCount
       from dbo.tb_Transactions a (NOLOCK) inner join
        dbo.tb_TransactionDetails b (NOLOCK) on a.TransactionID = b.TransactionID inner join
        dbo.tb_TransactionTypes c (NOLOCK) on a.TransactionTypeID = c.TransactionTypeID
      where TransactionCreatedDt > @StartDate
      group by a.BrokerID
      -- order by sum(b.TransactionDetailAmount * c.TransactionMultiplyBy) desc
    ) d on a.BrokerID = d.BrokerID
where (a.RunningAccountBalance + c.CreditLimit) - RetailPrice > 0
and b.RetailPrice != 0
-- and b.MaxLeadsPerDay > 0
and (a.BrokerID = 149244 or b.MaxLeadsPerDay > 0)
order by NetSalesTotal desc





-- @Y = modifier to set Log10(@X) = 1
declare @X float, @Y float, @index int, @increment int, @from int, @to int
set @Y = 1

select @from = 7, @to = 365, @increment = 7, @index = @from

while @index <= @to
begin
  set @X = @index

  print str(@X,9,0) + ' ' + str(Log(@X) / @Y,9,4)

  set @index = @index + @increment

end