ForGuymon.sql

use Broker_Recon_Data
go

begin transaction 

select
	*
from 
      Broker_Recon_Data.dbo.t_Bond_Reference
WHERE 
	   SEC_ID in (
			SELECT DISTINCT tc.PSEC
            FROM 
				CATS_Data.dbo.T_TRADING_CONTRACTS tc
				INNER JOIN (
					SELECT * 
					FROM BBH_Accounting.dbo.BBHSnapShot 
					WHERE EffectiveDate = 
						(SELECT max(EffectiveDate) FROM BBH_Accounting.dbo.BBHSnapShot)) ss
					ON tc.MarketID = ss.ISIN OR tc.MarketID = ss.CUSIP)
 

UPDATE 
      Broker_Recon_Data.dbo.t_Bond_Reference
SET 
      ISSUER = x.Issuer,
      EXP_DATE = x.Maturity
from
	(SELECT DISTINCT 
			PSEC,
			Coalesce(ss.Issuer, ss.SecurityDesc) as Issuer,
			Coalesce(ss.Maturity, '1900-01-01') as Maturity
		 FROM 
			CATS_Data.dbo.T_TRADING_CONTRACTS tc
			INNER JOIN (
				SELECT * 
				FROM BBH_Accounting.dbo.BBHSnapShot 
				WHERE EffectiveDate = 
					(SELECT max(EffectiveDate) FROM BBH_Accounting.dbo.BBHSnapShot)) ss
				ON tc.MarketID = ss.ISIN OR tc.MarketID = ss.CUSIP) x
WHERE
	SEC_ID = PSEC

select
	*
from 
      Broker_Recon_Data.dbo.t_Bond_Reference
WHERE 
	   SEC_ID in (
			SELECT DISTINCT tc.PSEC
            FROM 
				CATS_Data.dbo.T_TRADING_CONTRACTS tc
				INNER JOIN (
					SELECT * 
					FROM BBH_Accounting.dbo.BBHSnapShot 
					WHERE EffectiveDate = 
						(SELECT max(EffectiveDate) FROM BBH_Accounting.dbo.BBHSnapShot)) ss
					ON tc.MarketID = ss.ISIN OR tc.MarketID = ss.CUSIP)

 
rollback transaction