Selamlar,
SQLde çok amatörüm. 2 gündür bu konu hakkında dünyayı dolaştım. Ancak aşağıdaki aşamaya getirebildim. Bir yazılımın 2008Mssql datası mevcut. Özetle istediğim Borç ve Alacak sütunlarından Bakiyeyi bulmak.
TABLO OLUŞTURMA
create table Yevmiye (
Refno int,
Refno2 int,
Fisno NVARCHAR(9),
Tip NVARCHAR(1),
Borclu float,
Alacakli float,
Fistar smalldatetime,
Evraktarihi smalldatetime)
VERİLERİ GİRME
insert into Yevmiye values(1,1,0000000001,'B',1000,null,'02/13/2014 00:00:00','02/10/2014 00:00:00')
insert into Yevmiye values(1,2,0000000001,'C',null,500,'02/13/2014 00:00:00','02/11/2014 00:00:00')
insert into Yevmiye values(1,3,0000000001,'B',2000,null,'02/13/2014 00:00:00','02/13/2014 00:00:00')
insert into Yevmiye values(2,1,0000000008,'B',1000,null,'02/14/2014 00:00:00','02/10/2014 00:00:00')
insert into Yevmiye values(2,2,0000000008,'C',null,300,'02/14/2014 00:00:00','02/11/2014 00:00:00')
insert into Yevmiye values(2,3,0000000008,'C',null,10000,'02/14/2014 00:00:00','02/18/2014 00:00:00')
insert into Yevmiye values(3,1,0000000003,'C',null,5000,'02/15/2014 00:00:00','02/14/2014 00:00:00')
insert into Yevmiye values(3,2,0000000003,'B',3000,null,'02/15/2014 00:00:00','02/14/2014 00:00:00')
insert into Yevmiye values(3,3,0000000003,'B',2000,null,'02/15/2014 00:00:00','02/14/2014 00:00:00')
insert into Yevmiye values(3,4,0000000003,'C',null,500,'02/15/2014 00:00:00','02/11/2014 00:00:00')
SORGU
select
Refno,
Refno2,
Fisno,
Fistar,
Evraktarihi,
Fisno,
Tip,
Borclu = isnull(Borclu,0),
Alacakli = isnull(Alacakli,0),
Bakiye = (isnull(Borclu,0)-isnull(Alacakli,0)) +
isnull((select sum((isnull(Borclu,0)-isnull(Alacakli,0)))
From Yevmiye b where (b.Refno2) < (a.Refno2) ),0)
from Yevmiye a ;
BU SORGUDAN SONRA ÇIKAN RAPOR
Refno Refno2 Fistar Belgetarihi Fisno Tip Borclu Alacakli Bakiye
1 1 13.02.2014 00:00 10.02.2014 00:00 1 B 1000 0 1000
1 2 13.02.2014 00:00 11.02.2014 00:00 1 C 0 500 -3500
1 3 13.02.2014 00:00 13.02.2014 00:00 1 B 2000 0 1200
2 1 14.02.2014 00:00 10.02.2014 00:00 8 B 1000 0 1000
2 2 14.02.2014 00:00 11.02.2014 00:00 8 C 0 300 -3300
2 3 14.02.2014 00:00 18.02.2014 00:00 8 C 0 10000 -10800
3 1 15.02.2014 00:00 14.02.2014 00:00 3 C 0 5000 -5000
3 2 15.02.2014 00:00 14.02.2014 00:00 3 B 3000 0 0
3 3 15.02.2014 00:00 14.02.2014 00:00 3 B 2000 0 1200
3 4 15.02.2014 00:00 11.02.2014 00:00 3 C 0 500 -7300
OLMASI GEREKEN İSE
1 1 13.02.2014 00:00 10.02.2014 00:00 1 B 1000 0 1000
1 2 13.02.2014 00:00 11.02.2014 00:00 1 C 0 500 500
1 3 13.02.2014 00:00 13.02.2014 00:00 1 B 2000 0 2500
2 1 14.02.2014 00:00 10.02.2014 00:00 8 B 1000 0 3500
2 2 14.02.2014 00:00 11.02.2014 00:00 8 C 0 300 3200
2 3 14.02.2014 00:00 18.02.2014 00:00 8 C 0 10000 -6800
3 1 15.02.2014 00:00 14.02.2014 00:00 3 C 0 5000 -11800
3 2 15.02.2014 00:00 14.02.2014 00:00 3 B 3000 0 -8800
3 3 15.02.2014 00:00 14.02.2014 00:00 3 B 2000 0 -6800
3 4 15.02.2014 00:00 11.02.2014 00:00 3 C 0 500 -7300
SQLde çok amatörüm. 2 gündür bu konu hakkında dünyayı dolaştım. Ancak aşağıdaki aşamaya getirebildim. Bir yazılımın 2008Mssql datası mevcut. Özetle istediğim Borç ve Alacak sütunlarından Bakiyeyi bulmak.
TABLO OLUŞTURMA
create table Yevmiye (
Refno int,
Refno2 int,
Fisno NVARCHAR(9),
Tip NVARCHAR(1),
Borclu float,
Alacakli float,
Fistar smalldatetime,
Evraktarihi smalldatetime)
VERİLERİ GİRME
insert into Yevmiye values(1,1,0000000001,'B',1000,null,'02/13/2014 00:00:00','02/10/2014 00:00:00')
insert into Yevmiye values(1,2,0000000001,'C',null,500,'02/13/2014 00:00:00','02/11/2014 00:00:00')
insert into Yevmiye values(1,3,0000000001,'B',2000,null,'02/13/2014 00:00:00','02/13/2014 00:00:00')
insert into Yevmiye values(2,1,0000000008,'B',1000,null,'02/14/2014 00:00:00','02/10/2014 00:00:00')
insert into Yevmiye values(2,2,0000000008,'C',null,300,'02/14/2014 00:00:00','02/11/2014 00:00:00')
insert into Yevmiye values(2,3,0000000008,'C',null,10000,'02/14/2014 00:00:00','02/18/2014 00:00:00')
insert into Yevmiye values(3,1,0000000003,'C',null,5000,'02/15/2014 00:00:00','02/14/2014 00:00:00')
insert into Yevmiye values(3,2,0000000003,'B',3000,null,'02/15/2014 00:00:00','02/14/2014 00:00:00')
insert into Yevmiye values(3,3,0000000003,'B',2000,null,'02/15/2014 00:00:00','02/14/2014 00:00:00')
insert into Yevmiye values(3,4,0000000003,'C',null,500,'02/15/2014 00:00:00','02/11/2014 00:00:00')
SORGU
select
Refno,
Refno2,
Fisno,
Fistar,
Evraktarihi,
Fisno,
Tip,
Borclu = isnull(Borclu,0),
Alacakli = isnull(Alacakli,0),
Bakiye = (isnull(Borclu,0)-isnull(Alacakli,0)) +
isnull((select sum((isnull(Borclu,0)-isnull(Alacakli,0)))
From Yevmiye b where (b.Refno2) < (a.Refno2) ),0)
from Yevmiye a ;
BU SORGUDAN SONRA ÇIKAN RAPOR
Refno Refno2 Fistar Belgetarihi Fisno Tip Borclu Alacakli Bakiye
1 1 13.02.2014 00:00 10.02.2014 00:00 1 B 1000 0 1000
1 2 13.02.2014 00:00 11.02.2014 00:00 1 C 0 500 -3500
1 3 13.02.2014 00:00 13.02.2014 00:00 1 B 2000 0 1200
2 1 14.02.2014 00:00 10.02.2014 00:00 8 B 1000 0 1000
2 2 14.02.2014 00:00 11.02.2014 00:00 8 C 0 300 -3300
2 3 14.02.2014 00:00 18.02.2014 00:00 8 C 0 10000 -10800
3 1 15.02.2014 00:00 14.02.2014 00:00 3 C 0 5000 -5000
3 2 15.02.2014 00:00 14.02.2014 00:00 3 B 3000 0 0
3 3 15.02.2014 00:00 14.02.2014 00:00 3 B 2000 0 1200
3 4 15.02.2014 00:00 11.02.2014 00:00 3 C 0 500 -7300
OLMASI GEREKEN İSE
1 1 13.02.2014 00:00 10.02.2014 00:00 1 B 1000 0 1000
1 2 13.02.2014 00:00 11.02.2014 00:00 1 C 0 500 500
1 3 13.02.2014 00:00 13.02.2014 00:00 1 B 2000 0 2500
2 1 14.02.2014 00:00 10.02.2014 00:00 8 B 1000 0 3500
2 2 14.02.2014 00:00 11.02.2014 00:00 8 C 0 300 3200
2 3 14.02.2014 00:00 18.02.2014 00:00 8 C 0 10000 -6800
3 1 15.02.2014 00:00 14.02.2014 00:00 3 C 0 5000 -11800
3 2 15.02.2014 00:00 14.02.2014 00:00 3 B 3000 0 -8800
3 3 15.02.2014 00:00 14.02.2014 00:00 3 B 2000 0 -6800
3 4 15.02.2014 00:00 11.02.2014 00:00 3 C 0 500 -7300