SELECT 	OCA0.ID_CURRENCY, 
	OCA0.CURRENCY_CB, 
	isnull(SALDO,0), 
	ISNULL(CNT_DB,0), 
	ISNULL(OBOROT_DB,0),
	ISNULL(CNT_CR,0), ISNULL(OBOROT_CR,0),  ISNULL(X,0), ISNULL(FULLNAME,'') 
FROM 	(	SELECT 	isnull(CR.ID_CURRENCY,DB.ID_CURRENCY) 	AS ID_CURRENCY, 
			isnull(CR.CURRENCY_CB,DB.CURRENCY_CB) 	   CURRENCY_CB, 
			0 					AS SALDO, 
			ISNULL(SUM(CNT_DB),0) 			AS CNT_DB, 
			ISNULL(SUM(OBOROT_DB),0) 		AS OBOROT_DB,  
			ISNULL(SUM(CNT_CR),0) 			AS CNT_CR, 
			ISNULL(SUM(OBOROT_CR),0) 		AS OBOROT_CR, 
			0 					AS X, 
			isnull(isnull(DB.FULLNAME,CR.FULLNAME),'') AS FULLNAME 
		FROM 	( 	select  OCA0_CR.ID_CURRENCY, 
					CC_CR.CURRENCY_CB,
					ISNULL(OD0_CR.ID_RU_TRN, OD0_CR.ID_USER_TRN) ID_USER_CR, 
 					sum(OD0_CR.OBOROT_CR) 	OBOROT_CR, 
					isnull(ODAS_CR.FIELD,U_CR.FULLNAME) as FULLNAME, 
 					sum(ISNULL((case when OD0_CR.ID_CURRENCY_DB <> 810 and 
         	     					OD0_CR.ID_CURRENCY_CR <> 810 and 
          	     					convert(varchar(4),CC_DB.CURRENCY_CB) < convert(varchar(4),CC_CR.CURRENCY_CB) and 
          	     					substring(OD0_CR.STRACCOUNT_DB, 1, 5) =  substring(OD0_CR.STRACCOUNT_CR, 1, 5) and 
          	     					substring(OD0_CR.STRACCOUNT_DB, 6, 3) <> substring(OD0_CR.STRACCOUNT_CR, 6, 3) 
 							then 0 
 	        					when OD0_CR.ID_CURRENCY_CR = 810 and 
				 	             	substring(OD0_CR.STRACCOUNT_DB, 1, 5) =  substring(OD0_CR.STRACCOUNT_CR, 1, 5) and 
			 	             		substring(OD0_CR.STRACCOUNT_DB, 6, 3) <> substring(OD0_CR.STRACCOUNT_CR, 6, 3) 
 	        					then 0     
 	        					else 1 end ),0)) cnt_CR, 
					case when U_CR.FULLNAME = 'Планировщик задач' then isnull(ODAS_CR.FIELD,'Не заполнен оператор внешнего ОД') else isnull(ODAS_CR.FIELD,'') end as FIELD_CR,
 					OD0_CR.ID_RU_DOC AS ID_RU_DOC_CR
 				from 	OD_DOC_0 OD0_CR
  					left join 	(	select 	OOL_CR.ID_OPERATION, 
									OOL_CR.STR_ID_OPERATION, 
									OBD_CR.ID_DOCUMENT 
								from 	OC_OPERATION_LOG OOL_CR 
             								inner join 	OD_BUSINESS_DOC OBD_CR 	
									on  OOL_CR.ID_OPERATION  = OBD_CR.ID_OPERATION and RAZDEL = 0 
             								inner join UBS_BUSINESS OB_CR     	
									on  OB_CR.ID_BUSINESS   = OBD_CR.ID_BUSINESS and OB_CR.NAME_BUSINESS = 'Операционная касса') 	O_CR 
					on O_CR.ID_DOCUMENT   = OD0_CR.ID_DOC
					left join 	OD_DOC_0_ADDFL_STRING ODAS_CR    
						inner join 	OD_DOC_0_ADDFL_DIC ODAD_CR 
						on ODAD_CR.ID_FIELD = ODAS_CR.ID_FIELD and ODAD_CR.NAME_FIELD = 'Оператор во внешнем ОД' 
					on 	ODAS_CR.ID_OBJECT = OD0_CR.ID_DOC 
					left  join 	OC_OPERATION_NAME OON_CR 
					on OON_CR.STR_ID_OPERATION  = O_CR.STR_ID_OPERATION
					inner join 	(	select distinct ID_CASH,ID_KIND_ACC,ID_ACCOUNT,ID_CURRENCY 
								from OC_CASH_ACC0 where id_value = 0) OCA0_CR    
					on OCA0_CR.ID_ACCOUNT   = OD0_CR.ID_ACCOUNT_CR and OCA0_CR.ID_CASH = 907 and OCA0_CR.ID_KIND_ACC = 1
 					inner join COM_CURRENCY CC_CR      
					on CC_CR.ID_CURRENCY    = OD0_CR.ID_CURRENCY_CR
					inner join COM_CURRENCY CC_DB      
					on CC_DB.ID_CURRENCY    = OD0_CR.ID_CURRENCY_DB
					left  join OC_OPERLOG_ADDFL OOA 
             					inner join OC_OPERLOG_ADDFL_DIC OOAD 
						on OOAD.ID_FIELD = OOA.ID_FIELD and OOAD.NAME_FIELD='Идентификатор создавшего кассира' 
					on OOA.ID_OBJECT = O_CR.ID_OPERATION 
 					left  join 	OC_OPERLOG_ADDFL OOAS 
             					inner join 	OC_OPERLOG_ADDFL_DIC OOADS 
						on OOADS.ID_FIELD = OOAS.ID_FIELD and OOADS.NAME_FIELD='Идентификатор кассира' 
					on OOAS.ID_OBJECT = O_CR.ID_OPERATION
 					left join USERS U_CR  
					on  U_CR.USERNUM        = OD0_CR.ID_RU_DOC
 				where 	OD0_CR.EXEC_TRN <> 0 
   				and 	OD0_CR.DATE_TRN = {d '2007-10-08'}
   				and 	isnull(OON_CR.NAME_OPERATION,'') not in ('Аванс на послеоперационное время','Прием остатков послеоперационного времени') 
 				group by OCA0_CR.ID_CURRENCY, CC_CR.CURRENCY_CB, ODAS_CR.FIELD, U_CR.FULLNAME,ISNULL(OD0_CR.ID_RU_TRN, OD0_CR.ID_USER_TRN),OD0_CR.ID_RU_DOC 
			) CR
 			full join 
 (
 select OCA0_DB.ID_CURRENCY, CC_DB.CURRENCY_CB,
  ISNULL(OD0_DB.ID_RU_TRN, OD0_DB.ID_USER_TRN) ID_USER_DB, 
 sum(OD0_DB.OBOROT_DB) OBOROT_DB, isnull(ODAS_DB.FIELD,U_DB.FULLNAME) as FULLNAME,
 --	В отчете по операциям в ин. валюте при подсчете количества документов  документы: 
 --      Дт  счет кассы(валюта1) - Кт счет кассы (валюта 2) учитывать 1 раз по валюте с наименьшим кодом.
 sum(ISNULL((case when OD0_DB.ID_CURRENCY_DB <> 810 and 
          	     OD0_DB.ID_CURRENCY_CR <> 810 and 
          	     convert(varchar(4),CC_CR.CURRENCY_CB) < convert(varchar(4),CC_DB.CURRENCY_CB) and 
          	     substring(OD0_DB.STRACCOUNT_DB, 1, 5) =  substring(OD0_DB.STRACCOUNT_CR, 1, 5) and 
          	     substring(OD0_DB.STRACCOUNT_DB, 6, 3) <> substring(OD0_DB.STRACCOUNT_CR, 6, 3) 
 		then 0 
 	        when OD0_DB.ID_CURRENCY_DB = 810 and 
 	             substring(OD0_DB.STRACCOUNT_DB, 1, 5) =  substring(OD0_DB.STRACCOUNT_CR, 1, 5) and 
 	             substring(OD0_DB.STRACCOUNT_DB, 6, 3) <> substring(OD0_DB.STRACCOUNT_CR, 6, 3) 
 	        then 0     
 	        else 1 end ),0)) cnt_DB,
 case when U_DB.FULLNAME = 'Планировщик задач' then isnull(ODAS_DB.FIELD,'Не заполнен оператор внешнего ОД') else isnull(ODAS_DB.FIELD,'') end as FIELD_DB,
 OD0_DB.ID_RU_DOC AS ID_RU_DOC_DB
 from OD_DOC_0 OD0_DB
  left join (select OOL_DB.ID_OPERATION, OOL_DB.STR_ID_OPERATION, OBD_DB.ID_DOCUMENT from OC_OPERATION_LOG OOL_DB 
             inner join OD_BUSINESS_DOC OBD_DB 	on  OOL_DB.ID_OPERATION  = OBD_DB.ID_OPERATION and RAZDEL = 0 
             inner join UBS_BUSINESS OB_DB     	on  OB_DB.ID_BUSINESS   = OBD_DB.ID_BUSINESS and OB_DB.NAME_BUSINESS = 'Операционная касса') O_DB on O_DB.ID_DOCUMENT   = OD0_DB.ID_DOC
	left join (OD_DOC_0_ADDFL_STRING ODAS_DB    inner join OD_DOC_0_ADDFL_DIC ODAD_DB on ODAD_DB.ID_FIELD = ODAS_DB.ID_FIELD and ODAD_DB.NAME_FIELD = 'Оператор во внешнем ОД') on ODAS_DB.ID_OBJECT = OD0_DB.ID_DOC left  join OC_OPERATION_NAME OON_DB on OON_DB.STR_ID_OPERATION  = O_DB.STR_ID_OPERATION
 inner join (select distinct ID_CASH,ID_KIND_ACC,ID_ACCOUNT,ID_CURRENCY from OC_CASH_ACC0 where id_value = 0) OCA0_DB    on OCA0_DB.ID_ACCOUNT   = OD0_DB.ID_ACCOUNT_DB and OCA0_DB.ID_CASH =  907 and OCA0_DB.ID_KIND_ACC = 1
 inner join COM_CURRENCY CC_DB      on CC_DB.ID_CURRENCY    = OD0_DB.ID_CURRENCY_DB
 inner join COM_CURRENCY CC_CR      on CC_CR.ID_CURRENCY    = OD0_DB.ID_CURRENCY_CR
 left  join (OC_OPERLOG_ADDFL OOA 
             inner join OC_OPERLOG_ADDFL_DIC OOAD on OOAD.ID_FIELD = OOA.ID_FIELD and OOAD.NAME_FIELD='Идентификатор создавшего кассира') on OOA.ID_OBJECT = O_DB.ID_OPERATION 
 left  join (OC_OPERLOG_ADDFL OOAS 
             inner join OC_OPERLOG_ADDFL_DIC OOADS on OOADS.ID_FIELD = OOAS.ID_FIELD and OOADS.NAME_FIELD='Идентификатор кассира') on OOAS.ID_OBJECT = O_DB.ID_OPERATION
 left  join USERS U_DB on  U_DB.USERNUM = OD0_DB.ID_RU_DOC
 
 where OD0_DB.EXEC_TRN <> 0 
   and OD0_DB.DATE_TRN = {d '2007-10-08'}
   and isnull(OON_DB.NAME_OPERATION,'') not in ('Аванс на послеоперационное время','Прием остатков послеоперационного времени') 
 group by OCA0_DB.ID_CURRENCY, CC_DB.CURRENCY_CB, ODAS_DB.FIELD, U_DB.FULLNAME, ISNULL(OD0_DB.ID_RU_TRN, OD0_DB.ID_USER_TRN), OD0_DB.ID_RU_DOC 
 ) DB on CR.ID_CURRENCY = DB.ID_CURRENCY AND CR.ID_USER_CR = DB.ID_USER_DB AND CR.ID_RU_DOC_CR = DB.ID_RU_DOC_DB and isnull(CR.FIELD_CR,'') = isnull(DB.FIELD_DB,'') 
 where isnull(CR.ID_USER_CR, DB.ID_USER_DB) = 889
 group by isnull(CR.ID_CURRENCY,DB.ID_CURRENCY), isnull(CR.CURRENCY_CB,DB.CURRENCY_CB), isnull(isnull(DB.FULLNAME,CR.FULLNAME),'')   
) OBOROT right join (select distinct A.ID_CURRENCY,CURRENCY_CB  FROM OC_CASH_ACC0 A, COM_CURRENCY CC WHERE  A.ID_CURRENCY  = CC.ID_CURRENCY AND ID_CASH = 907) OCA0 ON OBOROT.ID_CURRENCY = OCA0.ID_CURRENCY 
 where OCA0.ID_CURRENCY  = 810
 order by 1,8 asc,5 desc