SQLize Online / PHPize Online  /  SQLtest Online

CREATE view sol_finist_risk as select distinct -- dt.DealTransactID, convert(varchar(210),dt.Comment) as Description ,convert(decimal(16,2),dt.RealQty) as DtSum ,convert(decimal(16,2),dt.RealQty) as CtSum ,convert(varchar(20),rd.Brief) as DtAccount ,convert(varchar(20),rc.Brief) as CtAccount ,convert(datetime,op.OperDate) as EntryDate ,convert(datetime,dt.Date) as BalanceDate ,convert(decimal(16,2),op.Qty) as EntrySum ,op.Number as EntryNumber ,case i.Brief when '**ПлатПор' then p_pi.InnClient else id.INN end as SenderINN ,k16.StrValue as SenderKPP ,case i.Brief when '**ПлатПор' then iBank_p.BIC else ib.BIC end as SenderBIK ,case i.Brief when '**ПлатПор' then p_pi.NameClient else id.Name end as Sender --плательщик ,case i.Brief when '**ПлатПор' then p_pi.AccBank else ib.AccRcv end as SenderKorrAcc ,case i.Brief when '**ПлатПор' then r_pi.InnClient else ic.INN end as RecipientINN ,k17.StrValue as RecipientKPP ,case i.Brief when '**ПлатПор' then iBank_r.BIC else ib.BIC end as RecipientBIK ,case i.Brief when '**ПлатПор' then r_pi.NameClient else ic.Name end as Recipient --получатель ,case i.Brief when '**ПлатПор' then r_pi.AccBank else ib.AccRcv end as RecipientKorrAcc ,convert(varchar(50),op.OperationID) as EntryID ,substring(rd.Brief,6,3) as SenderAccCurrency ,substring(rc.Brief,6,3) as RecipientAccCurrency ,convert(varchar(255),u.Name) as ResponsibleExecutor ,convert(varchar(255),u.Position) as Position ,convert(decimal(10,4),dt.RealCourse) as DtCurrencyRate ,convert(decimal(16,2),op.QtyBs) as DocumentSumInRoubles ,convert(varchar(50),op.QtyBs) as DocumentSumInRoubles ,k10.StrValue as OKTMO ,case isnull(k8.StrValue, 0) when 0 then '' else k9.StrValue end as KBK ,case ct.CharType when 1 then dp.Brief else NULL end as DtCashSymbol ,case ct.CharType when 2 then dp.Brief else NULL end as CtCashSymbol ,i.Brief as DocumentType from tDealTransact dt JOIN tResource rd ON(dt.ResourceID = rd.ResourceID) JOIN tInstitution id ON(id.InstitutionID = rd.InstOwnerID) JOIN tResource rc ON(dt.ResourcePsvID = rc.ResourceID) JOIN tInstitution ic ON(ic.InstitutionID = rc.InstOwnerID) JOIN tInstitution ib ON(dt.InstitutionID = ib.InstitutionID) JOIN tOperPart op ON(dt.DealTransactID = op.DealTransactID and op.CharType = 1) --по дебету JOIN tUser u ON(dt.UserID = u.UserID) JOIN tInstrument i ON(dt.InstrumentID = i.InstrumentID) LEFT JOIN tPayInstruct p_pi ON(p_pi.DealTransactID = dt.DealTRansactID and p_pi.Belong = dt.Direction) LEFT JOIN tInstitution ibank_p ON(iBank_p.InstitutionID = p_pi.BankID) LEFT JOIN tKey k16 ON(k16.ObjectID = dt.DealTransactID and k16.InterfaceFieldID = 16) LEFT JOIN tPayInstruct r_pi ON(r_pi.DealTransactID = dt.DealTRansactID and r_pi.Belong = 1 - dt.Direction) LEFT JOIN tInstitution ibank_r ON(iBank_r.InstitutionID = r_pi.BankID) LEFT JOIN tKey k17 ON(k17.ObjectID = dt.DealTransactID and k17.InterfaceFieldID = 17) LEFT JOIN tKey k10 ON(k10.ObjectID = dt.DealTransactID and k10.InterfaceFieldID = 10) LEFT JOIN tKey k8 ON(k8.ObjectID = dt.DealTransactID and k8.InterfaceFieldID = 8) LEFT JOIN tKey k9 ON(k9.ObjectID = dt.DealTransactID and k8.InterfaceFieldID = 9) LEFT JOIN tDocRelation dr ON (dt.DealTransactID = dr.DealTransactID and dr.RelType = 2) LEFT JOIN tDepartment dp ON (dr.ChildID = dp.DepartmentID) LEFT JOIN tCashType ct ON (ct.CashTypeID = dp.DepartmentID /*and ct.CharType = 1*/ and ct.ViewInReport = 1) --ct.CharType: 1- приход 2 - расход ct.ViewInReport = 1 - отображать в отчете where 1 = 1 --and dt.Date between '20220601' and '20220615'