/*
	Description: Binary Table Search, removes the need to have mutiple passes of the same data through
				 derived table

*/

SELECT 
	 Pool_id
	,Accnbr
	,CAST(SUBSTRING(mx, 9, 4) AS int) AS rec_nbr
	,CAST(SUBSTRING(mx, 13, 8) AS datetime) AS dt_eff
	,CAST(SUBSTRING(mx, 1, 8) AS datetime) AS dt_trn
	,CAST(SUBSTRING(mx, 21, 8) AS decimal(12,2)) AS dt_trn
FROM (
		SELECT  
			 Pool_id
			,Accnbr
        	,MAX( CAST(dt_trn AS binary(8))
	 	       + CAST(rec_nbr AS binary(4))
    	       + CAST(dt_eff AS binary(8))
 			   + CAST(tran_amt AS binary(8))
				) AS mx
      FROM 
			MOR97
      GROUP BY 
			 Pool_id
			,Accnbr
	) AS D