select
	DealerID = dlr.Company_Id,
	DealerType = case par.Holding_Company_Type
				when 'Anonymous' then 'Regional'
					else par.Holding_Company_Type
				end,
	DumbSort = case par.Holding_Company_Type
				when 'Anonymous' then 20
				else 30
				end,
--TypeOfDealer = CASE WHEN @ThresholdDate < MIN(trd.TradeDate) THEN 'OLD' ELSE 'NEW' END
	TypeOfDealer = CASE WHEN '20000505' < MIN(trd.TradeDate) THEN 'OLD' ELSE 'NEW' END

--into #DealerList
from
pivotal..Company dlr
inner join
(
select Company_Id, Holding_Company_Type
from pivotal..Company
where Holding_Company_Type in ('Anonymous', 'Diversity')
and Client_Active_Status = 'Active'
) par
on

dlr.Parent_Company_Id = par.Company_Id
inner join
Trade trd
on trd.CPPivotalCompanyID = dlr.Company_Id
where
Client_Active_Status = 'Active'
group by pivotal..Company.Company_Id