SELECT 

deals.Period
,Contracts.ContractID
,deals.Dog
,deals.ComisType
,deals.ComisTypeText
,SUM(deals.Qty) SumQty

--deals.* 
--,Contracts.ContractID
FROM (

--Form class: nil Query name: 
--Base: workdb
select distinct/*1 as RecurrentRight,
       (case
          when substring(dt.NumberExt,5,1) = '1' then 'Карт2'
          when substring(dt.NumberExt,5,1) = '3' then 'ПереносТр'
          when substring(dt.NumberExt,5,1) = '5' then 'НевСумм'
          when substring(dt.NumberExt,5,1) = '7' then 'КАкцепт'
          when substring(dt.NumberExt,5,1) = '8' then 'Транзит'
          when substring(dt.NumberExt,5,1) = '9' then 'M_Pay'
          when substring(dt.NumberExt,5,1) = 'A' then 'НезРасч'
          when substring(dt.NumberExt,5,1) = 'B' then 'КартРезерв'
          when substring(dt.NumberExt,5,1) = 'C' then 'Внеб'
          when substring(dt.NumberExt,5,1) = 'D' then 'СпецСчета'
          when substring(dt.NumberExt,5,1) = 'E' then 'Приостанов'
          when substring(dt.NumberExt,5,1) = 'F' then 'КАкцепт2'
          when substring(dt.NumberExt,5,1) = 'Y' then 'КартИнПФЛ'
          else '        '
        end
       ) as Kartoteka,
       (case
          when substring(dt.NumberExt,9,1) = '1' then 'Карт2'
          when substring(dt.NumberExt,9,1) = '3' then 'ПереносТр'
          when substring(dt.NumberExt,9,1) = '5' then 'НевСумм'
          when substring(dt.NumberExt,9,1) = '7' then 'КАкцепт'
          when substring(dt.NumberExt,9,1) = '8' then 'Транзит'
          when substring(dt.NumberExt,9,1) = '9' then 'M_Pay'
          when substring(dt.NumberExt,9,1) = 'A' then 'НезРасч'
          when substring(dt.NumberExt,9,1) = 'B' then 'КартРезерв'
          when substring(dt.NumberExt,9,1) = 'C' then 'Внеб'
          when substring(dt.NumberExt,9,1) = 'D' then 'СпецСчета'
          when substring(dt.NumberExt,9,1) = 'E' then 'Приостанов'
          when substring(dt.NumberExt,9,1) = 'F' then 'КАкцепт2'
          when substring(dt.NumberExt,9,1) = 'Y' then 'КартИнПФЛ'
          when substring(dt.NumberExt,9,1) = 'V' then 'КартИнПФЛ'
          else '        '
        end
       ) as KartDel,
       '      ' as ByTrip,
       dt.Type,
       dt.BalanceQty,*/
	   FORMAT(dt.Date,'yyyy.MM') Period,
       dt.DealProtocolID as DealProtocolID,
       dt.Date as OperDate,
       dt.ResourceID as ResDebID,
       dt.ResourcePsvID as ResCreID,
       dt.DocNumber as Number,
       dt.DealTransactID as DealTransactID,
       /*dt.DealID,
       dt.Direction,
       case
         when it.InterfaceObjectID = 66 then case
                                               when isnull(piout.BankID,0) = isnull(piin.BankID,0) then '      '
                                               else substring('РасходПриход', convert(integer,1+dt.Direction*6),6)
                                             end
         when it.InterfaceObjectID = 30 then substring('РасходПриход', convert(integer,1+dt.Direction*6),6)
         else '      '
       end as Direct,
       piin.ResIntID0   as AccInID,
       piin.AccClient   as AccIn,
       iin.BIC          as BicIn,
       piout.ResIntID0  as AccOutID,
       piout.AccClient  as AccOut,
       iout.BIC         as BicOut,
       dt.ParentID,*/
       dt.InstrumentID,
       dt.Confirmed as Confirmed,
       cast(dt.Qty as float) as Qty,
       cast(dt.FixQty as float) as QtyDeb,
       convert(char(25),r1.AccNumber) as Brief1,
       convert(char(25),r2.AccNumber)  as Brief2,
       /*'' as Brief3,*/
       dt.NumberExt,
       dt.OpCode,
       dt.Priority,
       dt.FixCourse as Course,
       dt.TermDate,
       dt.DocDate,
       dt.ValueDate,
       dt.Nominal,
       coalesce(o.Brief,oc.Brief) as ObjClsBrief,
       b.Brief as Batch,
       /*(case
          when dt.DealID > 0 then 'из сделки'
          else '        '
        end
        ) as Deal,
        (case
            when exists (select 1
                          from tDocRelation dr WITH (NOLOCK index=XPKtDocRelation)
                         where dr.DealTransactID = dt.DealTransactID
                           and dr.RelType = 3
                           and dr.Type    = 0
                         )
                 then 'реестр'
           else '     '
         end
        ) as Package,
        case when (dt.NumberLow &8) /8 = 1 then 'РЦ' else '     ' end as RCField,*/
        dt.FundID      as FundDebID,
		tR.InstOwnerID,
        r1.Name        as NameDeb,
        r2.Name        as NameCre,
		dt.Comment     as Comment,
		--REPLACE(dt.Comment, '№ ', '') Comment2,
		--left(dt.Comment, 10) as Comment2,
		
		
		CASE

		-- когда есть символ '№ '
		WHEN dt.Comment like 'Комиссия за обслуживание по договору №%' THEN LEFT(SUBSTRING (dt.Comment, 38, 100), CHARINDEX(' ', SUBSTRING (dt.Comment, 38+2, 100)) - 1 + 2)
		WHEN dt.Comment like 'Требование по уплате комиссии за обслуживание по договору №%' THEN LEFT(SUBSTRING (dt.Comment, 59, 100), CHARINDEX(' ', SUBSTRING (dt.Comment, 59+2, 100)) - 1 + 2)
		WHEN dt.Comment like 'Комиссия за несоблюдение условий по договору №%' THEN LEFT(SUBSTRING (dt.Comment, 46, 100), CHARINDEX(' ', SUBSTRING (dt.Comment, 46+2, 100)) - 1 + 2)
		WHEN dt.Comment like 'Требование по уплате комиссии за несоблюдение условий по договору №%' THEN LEFT(SUBSTRING (dt.Comment, 67, 100), CHARINDEX(' ', SUBSTRING (dt.Comment, 67+2, 100)) - 1 + 2)
		WHEN dt.Comment like 'Комиссия по договору эквайринга №%' THEN LEFT(SUBSTRING (dt.Comment, 33+2, 100), CHARINDEX(' ', SUBSTRING (dt.Comment, 33+2, 100)) - 1 + 0)

		WHEN dt.Comment like 'Комиссия за обслуживание по договору%' THEN LEFT(SUBSTRING (dt.Comment, 38, 100), CHARINDEX(' ', SUBSTRING (dt.Comment, 38, 100)) - 1)
		WHEN dt.Comment like 'Требование по уплате комиссии за обслуживание по договору%' THEN LEFT(SUBSTRING (dt.Comment, 59, 100), CHARINDEX(' ', SUBSTRING (dt.Comment, 59, 100)) - 1)
		WHEN dt.Comment like 'Комиссия за несоблюдение условий по договору%' THEN LEFT(SUBSTRING (dt.Comment, 46, 100), CHARINDEX(' ', SUBSTRING (dt.Comment, 46, 100)) - 1)
		WHEN dt.Comment like 'Требование по уплате комиссии за несоблюдение условий по договору%' THEN LEFT(SUBSTRING (dt.Comment, 67, 100), CHARINDEX(' ', SUBSTRING (dt.Comment, 67, 100)) - 1)
		WHEN dt.Comment like 'Комиссия по договору эквайринга%' THEN LEFT(SUBSTRING (dt.Comment, 33, 100), CHARINDEX(' ', SUBSTRING (dt.Comment, 33, 100)) - 1)

		ELSE 'Прочие проводки'
		END as Dog,

		CASE
		WHEN dt.Comment like 'Комиссия за обслуживание по договору%' THEN 1 --фиксированный абонентский платеж
		WHEN dt.Comment like 'Требование по уплате комиссии за обслуживание по договору%' THEN 1 --фиксированный абонентский платеж
		WHEN dt.Comment like 'Комиссия за несоблюдение условий по договору%' THEN 2 --платеж за несоблюдение условий
		WHEN dt.Comment like 'Требование по уплате комиссии за несоблюдение условий по договору%' THEN 2 --платеж за несоблюдение условий
		WHEN dt.Comment like 'Комиссия по договору эквайринга%' THEN 3 --платеж за комиссию с оборота
		ELSE 0
		END as ComisType,

		CASE
		WHEN dt.Comment like 'Комиссия за обслуживание по договору%' THEN 'За обслуживание' --фиксированный абонентский платеж
		WHEN dt.Comment like 'Требование по уплате комиссии за обслуживание по договору%' THEN 'За обслуживание' --фиксированный абонентский платеж
		WHEN dt.Comment like 'Комиссия за несоблюдение условий по договору%' THEN 'За несоблюдение условий' --платеж за несоблюдение условий
		WHEN dt.Comment like 'Требование по уплате комиссии за несоблюдение условий по договору%' THEN 'За несоблюдение условий' --платеж за несоблюдение условий
		WHEN dt.Comment like 'Комиссия по договору эквайринга%' THEN 'За оборот' --платеж за комиссию с оборота
		
		ELSE 'Прочие проводки'
		END as ComisTypeText
		
        /*dt.InitiatorID as InitiatorID,
        dt.FundPsvID   as FundCreID,
        it.Brief       as InstrumentBrief,
        substring((r1.AccNumber + '00000000'),6,3) as DebFundShortName,
        substring((r2.AccNumber + '00000000'),6,3) as CreFundShortName,
        case
          when exists(select 1
                        from tFoldingDocument fd WITH (NOLOCK index=XPKtFoldingDocument)
                       where fd.DealTransactID = dt.DealTransactID) then 'Свод'
          when exists(select 1
                        from tFoldingDocumentRelation fdr WITH (NOLOCK index=XPKtFoldingDocumentRelation)
                       where fdr.DocumentSourceID = dt.DealTransactID) then 'ВклСв'
          else ' '
        end as FoldingDoc,
        0 as RelationType,
        case
          when (isnull(de.Flags, 0) & 1) = 1 then 'ИЗМ'
          else ''
        end as Properties ,
        isnull(de.flags,0) as UserFlag,
        isnull(de.OnlineControlFlags,0) as OnlineControlFlags,
       isnull(sd.EDDocSignStatusID,0) as StatusID,
        case
         when sd.EDDocSignStatusID = 10 then 'С'
         when sd.EDDocSignStatusID = 20 then 'П'
         when sd.EDDocSignStatusID = 30 then 'Г'
         when sd.EDDocSignStatusID = 40 then 'В'
         when sd.EDDocSignStatusID = 50 then 'ОП'
         when sd.EDDocSignStatusID = 60 then 'ОВ'
         when sd.EDDocSignStatusID = 70 then 'Б'
         else ''
        end as StatusType,
  case when isnull(tdfpfst.OPKC,'')='' then '' else 'СБП' end as DPF,
        case when Well_grnded.DocValue = 1 then 'Да' else '' end as Well_grounded,
        isnull(pdpu.Flag,0) as PUFlag,
        isnull(pdSDO.Flag,0) as SDOFlag,
        _tTransition.TargetStateID as StateID,
        dt.NumberLow as NumberLow*/
  from
    tDealTransact dt WITH (NOLOCK index=XIE3tDealTransact)
    inner join tRKO_FinOper_Sync it WITH (NOLOCK index=XPKtRKO_FinOper_Sync)
            on it.FinOperID = dt.InstrumentID
    inner join tRKO_ExpProperty_Sync b WITH (NOLOCK index=XPKtRKO_ExpProperty_Sync)
            on b.ExpPropertyID = dt.BatchID
           and b.ExpPropertyID between 5000024 and 10000004671
 inner join tRKO_UserRight_Sync us WITH (NOLOCK index=XPKtRKO_UserRight_Sync)
         on us.UserID         = 1
        and us.RightType      = 2
        and us.ObjectID       = b.ExpPropertyID
 inner join tRKO_UserRight_Sync ufo WITH (NOLOCK index=XPKtRKO_UserRight_Sync)
         on ufo.UserID    = 1
        and ufo.RightType = 4
        and ufo.ObjectID  = dt.InstrumentID
 inner join tRKO_Account_Sync r1 WITH (NOLOCK index=XPKtRKO_Account_Sync)
         on r1.AccountID = dt.ResourceID
        and r1.BalanceID = 2140
--and r1.AccNumber LIKE  '40__________________'
 inner join tRKO_Account_Sync r2 WITH (NOLOCK index=XPKtRKO_Account_Sync)
         on r2.AccountID = dt.ResourcePsvID
and r2.AccNumber LIKE  '70601_______________'
--and (dt.InstitutionID =            2000   or  r1.DepartmentID  =            2000   or  r2.DepartmentID  =            2000)
 inner join workdb.dbo.tObject _tObject WITH (NOLOCK index=XAK2tObject)             
        on _tObject.InstrumentID = dt.InstrumentID
       and _tObject.ID           = dt.DealtransactID
inner join workdb.dbo.tInstrument _tInstrument WITH (NOLOCK index=XPKtInstrument)  
        on _tInstrument.InstrumentID = _tObject.InstrumentID                                    
inner join workdb.dbo.tProtocol _tProtocol WITH (NOLOCK index=XPKtProtocol)        
        on _tProtocol.ProtocolID = _tObject.CurrProtocolID                                      
inner join workdb.dbo.tTransition _tTransition WITH (NOLOCK index=XPKtTransition)  
        on _tTransition.TransitionID = _tProtocol.TransitionID                                  
 left join workdb.dbo.tNode _tNode WITH (NOLOCK index=XPKtNode)                    
        on _tNode.NodeID = _tTransition.TargetStateID                                           
inner join tRKO_UserRight_Sync ub WITH (NOLOCK index=XPKtRKO_UserRight_Sync)
        on ub.UserID    =               1
       and ub.RightType = 14
       and ub.ObjectID  = _tTransition.TargetStateID
  left join tRKO_ExpProperty_Sync o WITH (NOLOCK index=XPKtRKO_ExpProperty_Sync)
         on o.ExpPropertyID   = dt.PaymentType
        and o.ExpPropertyType = 25
  left join tObjClassifier       oc WITH (NOLOCK index=XPKtDepClassifier)
         on oc.ObjClassifierID = dt.PaymentType
  left join tPrimaryDocumentAttr de WITH (NOLOCK index=XPKtPrimaryDocumentAttr)
         on de.DealTransactID = dt.DealTransactID
  left join tSignDoc sd WITH (NOLOCK index=XPKtSignDoc)
         on sd.SignDocID = dt.DealTransactID
  left join tDocumentPlatFast tdfpfst WITH (NOLOCK index=XPKtDocumentPlatFast)
         on tdfpfst.DealTransactID = dt.DealTransactID
  left join tDocRelation Well_grnded WITH (NOLOCK index=XPKtDocRelation)
         on Well_grnded.DealTransactID = dt.DealTransactID
        and Well_grnded.RelType   = 51 --DOC_REL_REVAL_BASIS
  
  /*left join tPayInstruct piin          WITH (NOLOCK index=XIE2tPayInstruct)
         on piin.DealTransactID           = dt.DealTransactID
        and piin.Type                     = 2
        and piin.Direct                   = dt.Direction
        and piin.Belong                   = dt.Direction*/

  /*left join tRKO_Participant_Sync iin  WITH (NOLOCK index=XPKtRKO_Participant_Sync)
         on iin.ParticipantID             = piin.BankID*/

  /*left join tPayInstruct piout         WITH (NOLOCK index=XIE2tPayInstruct)
         on piout.DealTransactID          = dt.DealTransactID
        and piout.Type                    = 2
        and piout.Direct                  = dt.Direction
        and piout.Belong                  = 1 - dt.Direction*/


  /*left join tRKO_Participant_Sync iout WITH (NOLOCK index=XPKtRKO_Participant_Sync)
         on iout.ParticipantID            = piout.BankID*/

  left join tPrimaryDocPublicUtility pdpu WITH (NOLOCK index=XPKtPrimaryDocPublicUtility)
         on pdpu.DealTransactID = dt.DealTransactID
  left join tPrmDStateDefenseOrder pdSDO WITH (NOLOCK index=XPKtPrmDStateDefenseOrder)
         on pdSDO.PrimaryDocumentID = dt.DealTransactID

  left join tResource tR on tR.Brief = convert(char(25),r1.AccNumber)

 where 1=1
   and it.DsModuleID    = 21
   and dt.TransactType  = 5
   --and dt.Date = '20200430'
     -- если необходим отчет за интервал между датами
   and dt.Date >= DATEADD(d, 1, (EOMONTH (GETDATE(),-2))) --'20200501'
   and dt.Date < DATEADD(d, 1, (EOMONTH (GETDATE(),-1))) --'20200601' -- +1 день
   
   and (exists (select 1
                  from tRKO_UserRight_Sync ubr1 WITH (NOLOCK index=XPKtRKO_UserRight_Sync)
                 where ubr1.UserID    = 1
                   and ubr1.RightType = 19 --USER_RES_RESOURCE_PROFILE
                   and ubr1.ObjectID  = r1.AccProfileID)
        or
        exists (select 1
                  from tRKO_UserRight_Sync ubr2 WITH (NOLOCK index=XPKtRKO_UserRight_Sync)
                 where ubr2.UserID    = 1
                   and ubr2.RightType = 19 --USER_RES_RESOURCE_PROFILE
                   and ubr2.ObjectID  = r2.AccProfileID))
   --and r1.AccNumber LIKE  '40__________________' 
   and r2.AccNumber LIKE  '70601_______________' 
 --and (dt.InstitutionID =            2000   or  r1.DepartmentID  =            2000   or  r2.DepartmentID  =            2000)
   and dt.BatchID =     10000003353
   --and tR.InstOwnerID in (10001544513, 10000426129)



UNION ALL


--Form class: nil Query name: 
--Base: workdb
select distinct/*1 as RecurrentRight,
       (case
          when substring(dt.NumberExt,5,1) = '1' then 'Карт2'
          when substring(dt.NumberExt,5,1) = '3' then 'ПереносТр'
          when substring(dt.NumberExt,5,1) = '5' then 'НевСумм'
          when substring(dt.NumberExt,5,1) = '7' then 'КАкцепт'
          when substring(dt.NumberExt,5,1) = '8' then 'Транзит'
          when substring(dt.NumberExt,5,1) = '9' then 'M_Pay'
          when substring(dt.NumberExt,5,1) = 'A' then 'НезРасч'
          when substring(dt.NumberExt,5,1) = 'B' then 'КартРезерв'
          when substring(dt.NumberExt,5,1) = 'C' then 'Внеб'
          when substring(dt.NumberExt,5,1) = 'D' then 'СпецСчета'
          when substring(dt.NumberExt,5,1) = 'E' then 'Приостанов'
          when substring(dt.NumberExt,5,1) = 'F' then 'КАкцепт2'
          when substring(dt.NumberExt,5,1) = 'Y' then 'КартИнПФЛ'
          else '        '
        end
       ) as Kartoteka,
       (case
          when substring(dt.NumberExt,9,1) = '1' then 'Карт2'
          when substring(dt.NumberExt,9,1) = '3' then 'ПереносТр'
          when substring(dt.NumberExt,9,1) = '5' then 'НевСумм'
          when substring(dt.NumberExt,9,1) = '7' then 'КАкцепт'
          when substring(dt.NumberExt,9,1) = '8' then 'Транзит'
          when substring(dt.NumberExt,9,1) = '9' then 'M_Pay'
          when substring(dt.NumberExt,9,1) = 'A' then 'НезРасч'
          when substring(dt.NumberExt,9,1) = 'B' then 'КартРезерв'
          when substring(dt.NumberExt,9,1) = 'C' then 'Внеб'
          when substring(dt.NumberExt,9,1) = 'D' then 'СпецСчета'
          when substring(dt.NumberExt,9,1) = 'E' then 'Приостанов'
          when substring(dt.NumberExt,9,1) = 'F' then 'КАкцепт2'
          when substring(dt.NumberExt,9,1) = 'Y' then 'КартИнПФЛ'
          when substring(dt.NumberExt,9,1) = 'V' then 'КартИнПФЛ'
          else '        '
        end
       ) as KartDel,
       '      ' as ByTrip,
       dt.Type,
       dt.BalanceQty,*/
	   FORMAT(dt.Date,'yyyy.MM') Period,
       dt.DealProtocolID as DealProtocolID,
       dt.Date as OperDate,
       dt.ResourceID as ResDebID,
       dt.ResourcePsvID as ResCreID,
       dt.DocNumber as Number,
       dt.DealTransactID as DealTransactID,
       /*dt.DealID,
       dt.Direction,
       case
         when it.InterfaceObjectID = 66 then case
                                               when isnull(piout.BankID,0) = isnull(piin.BankID,0) then '      '
                                               else substring('РасходПриход', convert(integer,1+dt.Direction*6),6)
                                             end
         when it.InterfaceObjectID = 30 then substring('РасходПриход', convert(integer,1+dt.Direction*6),6)
         else '      '
       end as Direct,
       piin.ResIntID0   as AccInID,
       piin.AccClient   as AccIn,
       iin.BIC          as BicIn,
       piout.ResIntID0  as AccOutID,
       piout.AccClient  as AccOut,
       iout.BIC         as BicOut,
       dt.ParentID,*/
       dt.InstrumentID,
       dt.Confirmed as Confirmed,
       cast(dt.Qty as float)*(-1) as Qty,
       cast(dt.FixQty as float)*(-1) as QtyDeb,
       convert(char(25),r1.AccNumber) as Brief1,
       convert(char(25),r2.AccNumber)  as Brief2,
       /*'' as Brief3,*/
       dt.NumberExt,
       dt.OpCode,
       dt.Priority,
       dt.FixCourse as Course,
       dt.TermDate,
       dt.DocDate,
       dt.ValueDate,
       dt.Nominal,
       coalesce(o.Brief,oc.Brief) as ObjClsBrief,
       b.Brief as Batch,
       /*(case
          when dt.DealID > 0 then 'из сделки'
          else '        '
        end
        ) as Deal,
        (case
            when exists (select 1
                          from tDocRelation dr WITH (NOLOCK index=XPKtDocRelation)
                         where dr.DealTransactID = dt.DealTransactID
                           and dr.RelType = 3
                           and dr.Type    = 0
                         )
                 then 'реестр'
           else '     '
         end
        ) as Package,
        case when (dt.NumberLow &8) /8 = 1 then 'РЦ' else '     ' end as RCField,*/
        dt.FundID      as FundDebID,
		tR.InstOwnerID,
        r1.Name        as NameDeb,
        r2.Name        as NameCre,
		dt.Comment     as Comment,
		--REPLACE(dt.Comment, '№ ', '') Comment2,
		--left(dt.Comment, 10) as Comment2,
		
		
		CASE

		-- когда есть символ '№ '
		WHEN dt.Comment like 'Комиссия за обслуживание по договору №%' THEN LEFT(SUBSTRING (dt.Comment, 38, 100), CHARINDEX(' ', SUBSTRING (dt.Comment, 38+2, 100)) - 1 + 2)
		WHEN dt.Comment like 'Требование по уплате комиссии за обслуживание по договору №%' THEN LEFT(SUBSTRING (dt.Comment, 59, 100), CHARINDEX(' ', SUBSTRING (dt.Comment, 59+2, 100)) - 1 + 2)
		WHEN dt.Comment like 'Комиссия за несоблюдение условий по договору №%' THEN LEFT(SUBSTRING (dt.Comment, 46, 100), CHARINDEX(' ', SUBSTRING (dt.Comment, 46+2, 100)) - 1 + 2)
		WHEN dt.Comment like 'Требование по уплате комиссии за несоблюдение условий по договору №%' THEN LEFT(SUBSTRING (dt.Comment, 67, 100), CHARINDEX(' ', SUBSTRING (dt.Comment, 67+2, 100)) - 1 + 2)
		WHEN dt.Comment like 'Комиссия по договору эквайринга №%' THEN LEFT(SUBSTRING (dt.Comment, 33+2, 100), CHARINDEX(' ', SUBSTRING (dt.Comment, 33+2, 100)) - 1 + 0)

		WHEN dt.Comment like 'Комиссия за обслуживание по договору%' THEN LEFT(SUBSTRING (dt.Comment, 38, 100), CHARINDEX(' ', SUBSTRING (dt.Comment, 38, 100)) - 1)
		WHEN dt.Comment like 'Требование по уплате комиссии за обслуживание по договору%' THEN LEFT(SUBSTRING (dt.Comment, 59, 100), CHARINDEX(' ', SUBSTRING (dt.Comment, 59, 100)) - 1)
		WHEN dt.Comment like 'Комиссия за несоблюдение условий по договору%' THEN LEFT(SUBSTRING (dt.Comment, 46, 100), CHARINDEX(' ', SUBSTRING (dt.Comment, 46, 100)) - 1)
		WHEN dt.Comment like 'Требование по уплате комиссии за несоблюдение условий по договору%' THEN LEFT(SUBSTRING (dt.Comment, 67, 100), CHARINDEX(' ', SUBSTRING (dt.Comment, 67, 100)) - 1)
		WHEN dt.Comment like 'Комиссия по договору эквайринга%' THEN LEFT(SUBSTRING (dt.Comment, 33, 100), CHARINDEX(' ', SUBSTRING (dt.Comment, 33, 100)) - 1)

		WHEN dt.Comment like 'Возврат комиссии по договору эквайринга%' THEN LEFT(SUBSTRING (dt.Comment, 41, 100), CHARINDEX(' ', SUBSTRING (dt.Comment, 41, 100)) - 1)


		ELSE 'Прочие проводки'
		END as Dog,

		CASE
		WHEN dt.Comment like 'Комиссия за обслуживание по договору%' THEN 1 --фиксированный абонентский платеж
		WHEN dt.Comment like 'Требование по уплате комиссии за обслуживание по договору%' THEN 1 --фиксированный абонентский платеж
		WHEN dt.Comment like 'Комиссия за несоблюдение условий по договору%' THEN 2 --платеж за несоблюдение условий
		WHEN dt.Comment like 'Требование по уплате комиссии за несоблюдение условий по договору%' THEN 2 --платеж за несоблюдение условий
		WHEN dt.Comment like 'Комиссия по договору эквайринга%' THEN 3 --платеж за комиссию с оборота

		WHEN dt.Comment like 'Возврат комиссии по договору эквайринга%' THEN 3 --платеж за комиссию с оборота
		
		ELSE 0
		END as ComisType,

		CASE
		WHEN dt.Comment like 'Комиссия за обслуживание по договору%' THEN 'За обслуживание' --фиксированный абонентский платеж
		WHEN dt.Comment like 'Требование по уплате комиссии за обслуживание по договору%' THEN 'За обслуживание' --фиксированный абонентский платеж
		WHEN dt.Comment like 'Комиссия за несоблюдение условий по договору%' THEN 'За несоблюдение условий' --платеж за несоблюдение условий
		WHEN dt.Comment like 'Требование по уплате комиссии за несоблюдение условий по договору%' THEN 'За несоблюдение условий' --платеж за несоблюдение условий
		WHEN dt.Comment like 'Комиссия по договору эквайринга%' THEN 'За оборот' --платеж за комиссию с оборота
		
		WHEN dt.Comment like 'Возврат комиссии по договору эквайринга%' THEN 'За оборот' --платеж за комиссию с оборота

		ELSE 'Прочие проводки'
		END as ComisTypeText
		
        /*dt.InitiatorID as InitiatorID,
        dt.FundPsvID   as FundCreID,
        it.Brief       as InstrumentBrief,
        substring((r1.AccNumber + '00000000'),6,3) as DebFundShortName,
        substring((r2.AccNumber + '00000000'),6,3) as CreFundShortName,
        case
          when exists(select 1
                        from tFoldingDocument fd WITH (NOLOCK index=XPKtFoldingDocument)
                       where fd.DealTransactID = dt.DealTransactID) then 'Свод'
          when exists(select 1
                        from tFoldingDocumentRelation fdr WITH (NOLOCK index=XPKtFoldingDocumentRelation)
                       where fdr.DocumentSourceID = dt.DealTransactID) then 'ВклСв'
          else ' '
        end as FoldingDoc,
        0 as RelationType,
        case
          when (isnull(de.Flags, 0) & 1) = 1 then 'ИЗМ'
          else ''
        end as Properties ,
        isnull(de.flags,0) as UserFlag,
        isnull(de.OnlineControlFlags,0) as OnlineControlFlags,
       isnull(sd.EDDocSignStatusID,0) as StatusID,
        case
         when sd.EDDocSignStatusID = 10 then 'С'
         when sd.EDDocSignStatusID = 20 then 'П'
         when sd.EDDocSignStatusID = 30 then 'Г'
         when sd.EDDocSignStatusID = 40 then 'В'
         when sd.EDDocSignStatusID = 50 then 'ОП'
         when sd.EDDocSignStatusID = 60 then 'ОВ'
         when sd.EDDocSignStatusID = 70 then 'Б'
         else ''
        end as StatusType,
  case when isnull(tdfpfst.OPKC,'')='' then '' else 'СБП' end as DPF,
        case when Well_grnded.DocValue = 1 then 'Да' else '' end as Well_grounded,
        isnull(pdpu.Flag,0) as PUFlag,
        isnull(pdSDO.Flag,0) as SDOFlag,
        _tTransition.TargetStateID as StateID,
        dt.NumberLow as NumberLow*/
  from
    tDealTransact dt WITH (NOLOCK index=XIE3tDealTransact)
    inner join tRKO_FinOper_Sync it WITH (NOLOCK index=XPKtRKO_FinOper_Sync)
            on it.FinOperID = dt.InstrumentID
    inner join tRKO_ExpProperty_Sync b WITH (NOLOCK index=XPKtRKO_ExpProperty_Sync)
            on b.ExpPropertyID = dt.BatchID
           and b.ExpPropertyID between 5000024 and 10000004671
 inner join tRKO_UserRight_Sync us WITH (NOLOCK index=XPKtRKO_UserRight_Sync)
         on us.UserID         = 1
        and us.RightType      = 2
        and us.ObjectID       = b.ExpPropertyID
 inner join tRKO_UserRight_Sync ufo WITH (NOLOCK index=XPKtRKO_UserRight_Sync)
         on ufo.UserID    = 1
        and ufo.RightType = 4
        and ufo.ObjectID  = dt.InstrumentID
 inner join tRKO_Account_Sync r1 WITH (NOLOCK index=XPKtRKO_Account_Sync)
         on r1.AccountID = dt.ResourceID
        and r1.BalanceID = 2140
and r1.AccNumber LIKE  '70601_______________'
 inner join tRKO_Account_Sync r2 WITH (NOLOCK index=XPKtRKO_Account_Sync)
         on r2.AccountID = dt.ResourcePsvID
--and r2.AccNumber LIKE  '70601_______________'
--and (dt.InstitutionID =            2000   or  r1.DepartmentID  =            2000   or  r2.DepartmentID  =            2000)
 inner join workdb.dbo.tObject _tObject WITH (NOLOCK index=XAK2tObject)             
        on _tObject.InstrumentID = dt.InstrumentID
       and _tObject.ID           = dt.DealtransactID
inner join workdb.dbo.tInstrument _tInstrument WITH (NOLOCK index=XPKtInstrument)  
        on _tInstrument.InstrumentID = _tObject.InstrumentID                                    
inner join workdb.dbo.tProtocol _tProtocol WITH (NOLOCK index=XPKtProtocol)        
        on _tProtocol.ProtocolID = _tObject.CurrProtocolID                                      
inner join workdb.dbo.tTransition _tTransition WITH (NOLOCK index=XPKtTransition)  
        on _tTransition.TransitionID = _tProtocol.TransitionID                                  
 left join workdb.dbo.tNode _tNode WITH (NOLOCK index=XPKtNode)                    
        on _tNode.NodeID = _tTransition.TargetStateID                                           
inner join tRKO_UserRight_Sync ub WITH (NOLOCK index=XPKtRKO_UserRight_Sync)
        on ub.UserID    =               1
       and ub.RightType = 14
       and ub.ObjectID  = _tTransition.TargetStateID
  left join tRKO_ExpProperty_Sync o WITH (NOLOCK index=XPKtRKO_ExpProperty_Sync)
         on o.ExpPropertyID   = dt.PaymentType
        and o.ExpPropertyType = 25
  left join tObjClassifier       oc WITH (NOLOCK index=XPKtDepClassifier)
         on oc.ObjClassifierID = dt.PaymentType
  left join tPrimaryDocumentAttr de WITH (NOLOCK index=XPKtPrimaryDocumentAttr)
         on de.DealTransactID = dt.DealTransactID
  left join tSignDoc sd WITH (NOLOCK index=XPKtSignDoc)
         on sd.SignDocID = dt.DealTransactID
  left join tDocumentPlatFast tdfpfst WITH (NOLOCK index=XPKtDocumentPlatFast)
         on tdfpfst.DealTransactID = dt.DealTransactID
  left join tDocRelation Well_grnded WITH (NOLOCK index=XPKtDocRelation)
         on Well_grnded.DealTransactID = dt.DealTransactID
        and Well_grnded.RelType   = 51 --DOC_REL_REVAL_BASIS
  
  /*left join tPayInstruct piin          WITH (NOLOCK index=XIE2tPayInstruct)
         on piin.DealTransactID           = dt.DealTransactID
        and piin.Type                     = 2
        and piin.Direct                   = dt.Direction
        and piin.Belong                   = dt.Direction*/

  /*left join tRKO_Participant_Sync iin  WITH (NOLOCK index=XPKtRKO_Participant_Sync)
         on iin.ParticipantID             = piin.BankID*/

  /*left join tPayInstruct piout         WITH (NOLOCK index=XIE2tPayInstruct)
         on piout.DealTransactID          = dt.DealTransactID
        and piout.Type                    = 2
        and piout.Direct                  = dt.Direction
        and piout.Belong                  = 1 - dt.Direction*/


  /*left join tRKO_Participant_Sync iout WITH (NOLOCK index=XPKtRKO_Participant_Sync)
         on iout.ParticipantID            = piout.BankID*/

  left join tPrimaryDocPublicUtility pdpu WITH (NOLOCK index=XPKtPrimaryDocPublicUtility)
         on pdpu.DealTransactID = dt.DealTransactID
  left join tPrmDStateDefenseOrder pdSDO WITH (NOLOCK index=XPKtPrmDStateDefenseOrder)
         on pdSDO.PrimaryDocumentID = dt.DealTransactID

  left join tResource tR on tR.Brief = convert(char(25),r1.AccNumber)

 where 1=1
   and it.DsModuleID    = 21
   and dt.TransactType  = 5
   --and dt.Date = '20200430'
     -- если необходим отчет за интервал между датами
   and dt.Date >= DATEADD(d, 1, (EOMONTH (GETDATE(),-2))) --'20200501'
   and dt.Date < DATEADD(d, 1, (EOMONTH (GETDATE(),-1))) --'20200601' -- +1 день
   
   and (exists (select 1
                  from tRKO_UserRight_Sync ubr1 WITH (NOLOCK index=XPKtRKO_UserRight_Sync)
                 where ubr1.UserID    = 1
                   and ubr1.RightType = 19 --USER_RES_RESOURCE_PROFILE
                   and ubr1.ObjectID  = r1.AccProfileID)
        or
        exists (select 1
                  from tRKO_UserRight_Sync ubr2 WITH (NOLOCK index=XPKtRKO_UserRight_Sync)
                 where ubr2.UserID    = 1
                   and ubr2.RightType = 19 --USER_RES_RESOURCE_PROFILE
                   and ubr2.ObjectID  = r2.AccProfileID))
   and r1.AccNumber LIKE  '70601_______________' 
   --and r2.AccNumber LIKE  '70601_______________' 
 --and (dt.InstitutionID =            2000   or  r1.DepartmentID  =            2000   or  r2.DepartmentID  =            2000)
   and dt.BatchID =     10000003353
   --and tR.InstOwnerID in (10001544513, 10000426129)

) deals         


left join (

Select distinct

tInstitution.InstitutionID
,AgrP.ContractID
,AgrP.Number

from tContractCrdDev CCD

left join tcontract AgrP
on AgrP.contractID = CCD.ParentCCDID

left join tInstitution 
on tInstitution.InstitutionId = AgrP.InstitutionID

left join tcontract Agr1
on Agr1.contractID = CCD.ContractCrdDevID

left Join tInstrument
on tInstrument.InstrumentID = Agr1.InstrumentID

left Join tCrdAccountLink CAL
on CAL.ObjectID = CCD.ContractCrdDevID

left join tCRD_RuleAcc_Sync     ta 
         on CAL.RuleID      = ta.RuleID
        and CAL.Flags = 2   --Только действующие!
        and ta.ObjectID   in (10000002061,
10000002062,
10000002063,
10000002370,
10000002372) 

left join tResource Acc
on Acc.ResourceID = CAL.AccountID

) Contracts
on Contracts.Number = deals.Dog --and Contracts.InstitutionID = deals.InstOwnerID

GROUP BY 
deals.Period
,Contracts.ContractID
,deals.Dog
,deals.ComisType
,deals.ComisTypeText
                                                                        
option (FORCE ORDER, LOOP JOIN, KEEPFIXED PLAN, FAST 1)