Allocations Check, Sql Server

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

λ

                                      .NET NoSQL database for rapid development