Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Allocations Check
--Sql Server 2014 Express Edition --Batches are separated by 'go' create table CrTran( Refr int not null primary key, Source varchar(2) not null, TrxType varchar(2) not null, YearNo int, Period int, FNett money, FVat money ) go create table Allocations( Id int identity(1,1) not null primary key, BankRefr int, BankType varchar(2), BankAlloc money, YearNo int, Period int, TrxRefr int, TrxType varchar(2), NomCode varchar(10), Allocated money ) go Create procedure ReconcileCreditorPayments( @YearNo int, @Period int ) As Begin Create table #RecTable( BankRefr int, BankType varchar(2), PaymentAmount money, Invoices int default 0, AllocatedAmount money ) /* Add payments */ insert into #RecTable(BankRefr, BankType, PaymentAmount, AllocatedAmount) select Refr, TrxType, ABS(FNett+FVat), 0 from CrTran where Source = 'BN' and YearNo = @YearNo and Period = @Period; /* Add lines */ update a set a.AllocatedAmount = b.AllocatedAmount, a.Invoices = b.Invoices from #RecTable a inner join (select a.BankRefr, a.BankType, count(distinct(a.TrxRefr)) [Invoices], abs(sum(a.Allocated)) [AllocatedAmount] from Allocations a where exists(select b.BankRefr from #RecTable b where a.BankRefr = b.BankRefr and a.BankType collate DATABASE_DEFAULT = b.BankType collate DATABASE_DEFAULT) group by a.BankRefr, a.BankType) b on(a.BankRefr = b.BankRefr and a.BankType collate DATABASE_DEFAULT = b.BankType collate DATABASE_DEFAULT); select *, PaymentAmount-AllocatedAmount [Variance] from #RecTable order by BankType, BankRefr; drop table #RecTable; End go insert into CrTran(Refr, Source, TrxType, YearNo, Period, FNett, FVat) select 1, 'BN', 'PY', 1, 1, -100, 0 insert into CrTran(Refr, Source, TrxType, YearNo, Period, FNett, FVat) select 2, 'BN', 'PY', 1, 1, -200, 0 insert into CrTran(Refr, Source, TrxType, YearNo, Period, FNett, FVat) select 3, 'BN', 'PY', 1, 1, -300, 0 go insert into Allocations(BankRefr, BankType, BankAlloc, YearNo, Period, TrxRefr, TrxType, Nomcode, Allocated) select 1, 'PY', 100, 1, 1, 1, 'IN', '1000', -50 UNION select 1, 'PY', 100, 1, 2, 2, 'IN', '2000', -40 union select 2, 'PY', 100, 1, 1, 3, 'IN', '1000', 110 UNION select 2, 'PY', 100, 1, 1, 3, 'IN', '2000', 90; go Exec ReconcileCreditorPayments 1, 1
run
|
edit
|
history
|
help
0
Conditional Operator (IIF)
base de datos dbfloreria
Comercio
repert
split a string into pairs of words
SCHOOL-BUILD
Miercoles
20181cse0012dbmsca2
range and pattern matching
SaLLa