set based solution, covering amount
-
I need an idea for set based solution. The solution has to be fast because of big amount of data. From data in #tmp1 table I would like to get result set like this:
1.1.200950050015.2.2009
1.1.200950050020.2.2009
1.3.200910010020.2.2009
1.3.20092002001.4.2009
create table #tmp1(
DateTrans datetime not null,
Debit money not null,
Credit money not null
)
go
insert into #tmp1(DateTrans, Debit, Credit)
values('20090101', 1000, 0)
insert into #tmp1(DateTrans, Debit, Credit)
values('20090301', 300, 0)
insert into #tmp1(DateTrans, Debit, Credit)
values('20090215', 0, 500)
insert into #tmp1(DateTrans, Debit, Credit)
values('20090220', 0, 600)
insert into #tmp1(DateTrans, Debit, Credit)
values('20090401', 0, 200)
-
Need more info than this. What are the 2 middle columns? What is the logic needed to calc the dates? etc...
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
The goal is to split and cover amount on debit side with amounts on credit side. From my example:
1000 € ( 1.1.2009) = 500€ ( 15.2.2009 ) + 500 € ( 20.2.2009; only 500 € from 600 €)
300 € ( 1.3. 2009 ) = 100€ ( 20.2.2009; rest 100€ from 600 € ) + 200 ( 1.4.2009 )
-
So this is a running total , yes?
See Jeff Modens article
-
Kind of.
But also includes split amount to cover debit side with credit side.
I think that runningsum is part of solution.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply