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