Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --exec [XBRL].[Import.AccountDepoByPeriod]
- Declare
- @adtStart date = '2017-07-01'
- ,@adtEnd date = '2017-07-05'
- Declare @now datetime = getdate()
- Set @adtStart = isnull(@adtStart,DateAdd(month,-1,getdate()))
- Set @adtEnd = isnull(@adtEnd,getdate())
- ---------------------------------------------------------------
- If Object_id('tempdb..#tbl_AccountDepoByPeriod_pre_Operation') is not Null
- Drop table #tbl_AccountDepoByPeriod_pre_Operation
- Create Table #tbl_AccountDepoByPeriod_pre_Operation (ID bigint not Null Primary key)
- Insert into #tbl_AccountDepoByPeriod_pre_Operation (ID)
- Select ID
- From Depo.Operation
- Where DepoDate >= @adtStart
- and DepoDate <= @adtEnd
- -- Operations
- If Object_id('tempdb..#tbl_AccountDepoByPeriod_Operation') is not Null
- Drop table #tbl_AccountDepoByPeriod_Operation
- Create Table #tbl_AccountDepoByPeriod_Operation
- (
- [ID] bigint
- ,[Account_ID] int
- ,[SubAccount_ID] int not null
- ,[Instrument_ID] int not null
- ,[Date] date
- ,[Time] datetime
- ,[Name] varchar(255)
- ,[OperationNo] bigint
- ,[DocName] varchar(512)
- ,[DocNumber] varchar(512)
- ,[DocDate] datetime
- ,[QuantityBefore] decimal(35,10)
- ,[Quantity] decimal(35,10)
- ,[QuantityAfter] decimal(35,10)
- ,[isDebet] bit
- ,[Account] varchar(13)
- ,[Corr_Account_ID] int
- ,[Corr_Account] varchar(13)
- ,[C_Person_ID] int
- ,[RemindQuantity] decimal(35,10)
- ,Primary Key ([ID],[Account_ID])
- )
- -- Credit side
- Insert into #tbl_AccountDepoByPeriod_Operation
- (
- [ID]
- ,[Date]
- ,[Time]
- ,[Name]
- ,[OperationNo]
- ,[DocName]
- ,[DocNumber]
- ,[DocDate]
- ,[Instrument_ID]
- ,[Quantity]
- ,[isDebet]
- ,[SubAccount_ID]
- ,[Account_ID]
- ,[Account]
- ,[Corr_Account_ID]
- ,[Corr_Account]
- ,[C_Person_ID]
- )
- Select d_o.ID [ID]
- ,d_o.DepoDate [Date]
- ,d_o.OperationDateTime [Time]
- ,d_ot.TypeName [Name]
- ,Cast(Stuff( d_o.OperationNo
- ,1,2,''
- ) as bigint
- ) [OperationNo]
- ,d_o.DocName [DocName]
- ,d_o.DocNumber [DocNumber]
- ,d_o.DocDate [DocDate]
- ,d_o.Instrument_ID [Instrument_ID]
- ,d_o.Quantity [Quantity]
- ,0 [isDebet]
- ,d_sa_c.ID [SubAccount_ID]
- ,d_sa_c.DepoAccount_ID [Account_ID]
- ,NULL [Account]
- ,isNull( d_a_d.ID
- ,c_st_d.ID
- ) [Corr_Account_ID]
- ,isNull( d_a_d.Account
- ,c_st_d.Account
- ) [Corr_Account]
- ,isNull( c_c_d.Person_ID
- ,c_st_d.Person_ID
- ) [C_Person_ID]
- From #tbl_AccountDepoByPeriod_pre_Operation c
- Join [Depo].[Operation] d_o on c.ID = d_o.ID
- -- CreditAccount
- Join [Depo].[SubAccount] d_sa_c on d_sa_c.ID = d_o.CreditSubAccount_ID
- Left join [Depo].[OperationType] d_ot on d_ot.ID = d_o.OperationType_ID
- -- Debet DepoAccount_ID
- Left join [Depo].[SubAccount] d_sa_d on d_sa_d.ID = d_o.DebetSubAccount_ID
- Left join [Depo].[Account] d_a_d on d_a_d.ID = d_sa_d.DepoAccount_ID
- Left join [Core].[Client.Agreement] c_ca_d on c_ca_d.ID = d_a_d.[Client.Agreement_ID]
- Left join [Core].[Client] c_c_d on c_c_d.ID = c_ca_d.Client_ID
- -- Debet Storage
- Left join [Core].[StorageSubAccount] c_ssa_d on c_ssa_d.ID = d_o.DebetStorageSubAccount_ID
- Left join [Core].[Storage] c_st_d on c_st_d.ID = c_ssa_d.Storage_ID
- -- Debet side
- Insert into #tbl_AccountDepoByPeriod_Operation
- (
- [ID]
- ,[Date]
- ,[Time]
- ,[Name]
- ,[OperationNo]
- ,[DocName]
- ,[DocNumber]
- ,[DocDate]
- ,[Instrument_ID]
- ,[Quantity]
- ,[isDebet]
- ,[SubAccount_ID]
- ,[Account_ID]
- ,[Account]
- ,[Corr_Account_ID]
- ,[Corr_Account]
- ,[C_Person_ID]
- )
- Select d_o.ID
- ,d_o.DepoDate [Date]
- ,d_o.OperationDateTime [Time]
- ,d_ot.TypeName [Name]
- ,Cast(Stuff( d_o.OperationNo
- ,1,2,''
- ) as bigint
- ) [OperationNo]
- ,d_o.DocName [DocName]
- ,d_o.DocNumber [DocNumber]
- ,d_o.DocDate [DocDate]
- ,d_o.Instrument_ID [Instrument_ID]
- ,d_o.Quantity [Quantity]
- ,1 [isDebet]
- ,d_sa_d.ID [SubAccount_ID]
- ,d_sa_d.DepoAccount_ID [Account_ID]
- ,NULL [Account]
- ,isNull( d_a_c.ID
- ,c_st_c.ID
- ) [Corr_Account_ID]
- ,isNull( d_a_c.Account
- ,c_st_c.Account
- ) [Corr_Account]
- ,isNull( c_c_c.Person_ID
- ,c_st_c.Person_ID
- ) [C_Person_ID]
- From #tbl_AccountDepoByPeriod_pre_Operation c
- Join [Depo].[Operation] d_o on c.ID = d_o.ID
- -- Debet DepoAccount_ID
- Join [Depo].[SubAccount] d_sa_d on d_sa_d.ID = d_o.DebetSubAccount_ID
- Left join [Depo].[OperationType] d_ot on d_ot.ID = d_o.OperationType_ID
- -- CreditAccount
- Left Join [Depo].[SubAccount] d_sa_c on d_sa_c.ID = d_o.CreditSubAccount_ID
- Left Join [Depo].[Account] d_a_c on d_a_c.ID = d_sa_c.DepoAccount_ID
- Left join [Core].[Client.Agreement] c_ca_c on c_ca_c.ID = d_a_c.[Client.Agreement_ID]
- Left join [Core].[Client] c_c_c on c_c_c.ID = c_ca_c.Client_ID
- -- Debet Storage
- Left join [Core].[StorageSubAccount] c_ssa_c on c_ssa_c.ID = d_o.CreditStorageSubAccount_ID
- Left join [Core].[Storage] c_st_c on c_st_c.ID = c_ssa_c.Storage_ID
- Where Not Exists (select 1
- from #tbl_AccountDepoByPeriod_Operation sub
- where sub.[ID] = d_o.[ID]
- and sub.[Account_ID] = d_sa_d.[DepoAccount_ID])
- Create Index IDX_tbl_AccountDepoByPeriod_Operation_Complex
- on #tbl_AccountDepoByPeriod_Operation ([Account_ID], [Instrument_ID], [Time], [OperationNo])
- Create Index IDX_tbl_AccountDepoByPeriod_Operation_SubAccount_ID_Instrument_ID
- on #tbl_AccountDepoByPeriod_Operation ([SubAccount_ID], [Instrument_ID])
- Select t.[Account_ID]
- ,t.[Instrument_ID]
- ,sum(Case when sub_t.[isDebet] = 1
- then -1
- else 1
- End * isNull(sub_t.[Quantity],0)
- ) [OperastionBefore]
- FROM #tbl_AccountDepoByPeriod_Operation t
- LEFT JOIN #tbl_AccountDepoByPeriod_Operation sub_t ON sub_t.[Account_ID] = t.[Account_ID]
- and sub_t.[ID] != t.[ID]
- and sub_t.[Instrument_ID] = t.[Instrument_ID]
- and sub_t.[time] <= t.[time]
- and sub_t.[OperationNo] < t.[OperationNo]
- Group by t.[Account_ID]
- ,t.[Instrument_ID]
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement