gistfile1.sql

select
	position.BrokerAccount
	, position.Contract
	, contractDetails.Future_Index_Contracts as Name
	, contractDetails.Market_Sector_Code as MarketSectorCode
	, position.Currency
	, contract.BloombergTicker
	, Sum(position.Quantity) as Quantity
from
	( select
			brokerAccount.BrokerAcct as BrokerAccount
			, contracts.PSEC as Contract
			, contracts.P_CURRENCY as Currency	
			, currentPosition.TOTAL_PSTN as Quantity
		from
			Broker_Recon_Data.dbo.T_Accounts brokerAccount with (nolock)
			inner join CATS_Data.dbo.T_CURRENT_POSITION currentPosition with (nolock) on
				brokerAccount.BrokerAcct = currentPosition.BROKER_ACCOUNT
			inner join CATS_Data.dbo.T_TRADING_CONTRACTS contracts with (nolock) on
				currentPosition.SEC_ID = contracts.TR_SEC

		union all

		select
			brokerAccount.BrokerAcct as BrokerAccount
			, contracts.PSEC as Contract
			, contracts.P_CURRENCY as Currency
			, case 
				when 'S' = todaysTrades.SGL_TRANS then - todaysTrades.SGL_CONTRACTS
				else todaysTrades.SGL_CONTRACTS end as Quantity
		from
			Broker_Recon_Data.dbo.T_Accounts brokerAccount with (nolock)
			inner join CATS_Data.dbo.T_TRADING_SINGLE_DAILY todaysTrades with (nolock) on
				brokerAccount.BrokerAcct = todaysTrades.SGL_CL_BRKR_ACCT
			inner join CATS_Data.dbo.T_TRADING_CONTRACTS contracts with (nolock) on
				todaysTrades.SGL_SECID = contracts.TR_SEC
    	where
			  todaysTrades.SGL_DISCARDED = 0

	) as position
	inner join (
		select
			PSEC as Contract
			, Max(Bloomberg_TKR) as BloombergTicker
		from
			CATS_Data.dbo.T_TRADING_CONTRACTS with (nolock)
		group by
			PSEC
	) contract on 
		position.Contract = contract.Contract
	inner join CATS_Data.dbo.T_FUTURES_MAIN contractDetails with (nolock) on
		contract.Contract = contractDetails.PSEC
where
	position.BrokerAccount = Coalesce(:BrokerAccount, position.BrokerAccount)
group by
	position.BrokerAccount
	, position.Contract
	, contractDetails.Future_Index_Contracts
	, contractDetails.Market_Sector_Code
	, position.Currency
	, contract.BloombergTicker
order by
	position.BrokerAccount
	, position.Contract