SQL View - Yaşlandırma için Borç Alacak - Bakiye

Question

Elimde aşağıdaki gibi bir tablo var.

CARI|	TARIH		|	BORC	|	ALACAK
------------------------------------------------------------
A	|	02.02.2020	|	10,00	|	0,00
A	|	03.02.2020	|	20,00	|	0,00
A	|	04.02.2020	|	0,00 	|	30,00
A	|	05.02.2020	|	20,00	|	0,00
A	|	06.02.2020	|	40,00	|	0,00
A	|	07.02.2020	|	50,00	|	0,00
A	|	08.02.2020	|	15,00	|	0,00
A	|	09.02.2020	|	20,00	|	0,00
A	|	10.02.2020	|	10,00	|	0,00
A	|	11.02.2020	|	20,00	|	0,00
A	|	12.02.2020	|	0,00 	|	50,00
A	|	13.02.2020	|	0,00 	|	60,00
A	|	14.02.2020	|	20,00	|	15,00

Bu tabloyla bir view oluşturmak istiyorum. İlk Olarak ALACAK'ın tamamı CARİ baz alınarak toplanacak. Sonra toplam ALACAK'tan tarihe göre ilk BORÇ düşülecek ve bunu ilk tarihteki BAKİYE kolonuna yazacak. Daha sonra bu tutardan sırayla diğer BORÇ kolonlarını tek tek düşecek ve mevcut tarihlerine ait kolonlara yazacak.

Elde etmek istediğim view aşağıdaki gibidir.

CARI	|	TARIH		|	BORC	|	ALACAK	|	BAKIYE	|	ALACAK TOPLAM
--------------------------------------------------------------------------------------------------------
A	|	02.02.2020	|	10,00	|	0,00 	|	145,00	|	155,00
A	|	03.02.2020	|	20,00	|	0,00 	|	125,00	|
A	|	04.02.2020	|	0,00 	|	30,00	|	125,00	|
A	|	05.02.2020	|	20,00	|	0,00 	|	105,00	|
A	|	06.02.2020	|	40,00	|	0,00 	|	65,00	|
A	|	07.02.2020	|	50,00	|	0,00 	|	15,00	|
A	|	08.02.2020	|	15,00	|	0,00 	|	0,00 	|
A	|	09.02.2020	|	20,00	|	0,00 	|	-20,00	|
A	|	10.02.2020	|	10,00	|	0,00 	|	-30,00	|
A	|	11.02.2020	|	20,00	|	0,00 	|	-50,00	|
A	|	12.02.2020	|	0,00 	|	50,00	|	-50,00	|
A	|	13.02.2020	|	0,00 	|	60,00	|	-50,00	|
A	|	14.02.2020	|	20,00	|	15,00	|	-70,00	|

Bu işlemi MVC de aşağıdaki gibi yapabiliyorum:

var alacakToplam = Model.Sum(x => x.ALACAK);

foreach (var item in Model.OrderBy(x => x.TARIH))
{
	<tr>
		<td>@item.CARI</td>
		<td>@item.TARIH</td>
		<td>@item.BORC</td>
		<td>@item.ALACAK</td>
		<td>@alacakToplam- item.BORC</td>
	</tr>
	alacakToplam= alacakToplam- item.BORC;
}

Ancak bunu View ile yapabilirsem çok daha iyi olacak.

Oluşturduğum view kodlarını ve oluşan view i aşağıya yazıyorum:

SELECT
CH.CARI,
CH.TARIH,
CH.BORC,
CH.ALACAK,

(sum(CH.ALACAK) over (partition by CH.CARI) -
        sum(debit) over (partition by CH.CARI)
       ) as BAKIYE,
	   
       (case when row_number() over (partition by CH.CARI order by date) = 1
             then sum(ALACAK) over (partition by CH.CARI)
        end) as ALACAK_TOPLAM

FROM
CARIHAREKETLER CH

Oluşan View:

CARI|   TARIH       |   BORC    |   ALACAK  |   BAKIYE  |   TOPLAM_ALACAK
-------------------------------------------------------------------------
A   |   02.02.2020  |   10,00   |   0,00    |   -70,00  |   155,00
A   |   03.02.2020  |   20,00   |   0,00    |   -70,00  |
A   |   04.02.2020  |   0,00    |   30,00   |   -70,00  |
A   |   05.02.2020  |   20,00   |   0,00    |   -70,00  |
A   |   06.02.2020  |   40,00   |   0,00    |   -70,00  |
A   |   07.02.2020  |   50,00   |   0,00    |   -70,00  |
A   |   08.02.2020  |   15,00   |   0,00    |   -70,00  |
A   |   09.02.2020  |   20,00   |   0,00    |   -70,00  |
A   |   10.02.2020  |   10,00   |   0,00    |   -70,00  |
A   |   11.02.2020  |   20,00   |   0,00    |   -70,00  |
A   |   12.02.2020  |   0,00    |   50,00   |   -70,00  |
A   |   13.02.2020  |   0,00    |   60,00   |   -70,00  |
A   |   14.02.2020  |   20,00   |   15,00   |   -70,00  |

Görüldüğü üzere Bakiye kısmında yapılan işlem toplam alacaktan toplam bakiyeyi çıkarınca elde edilen sonucu gösteriyor. Ben hepsinin ayrı ayrı yukarıda verdiğim örnekteki gibi hesaplatmak istiyorum.

Answer 1

Bunun View ile yapilmasinin dah iyi olduğu fikrine katılmıyorum acikcasi (view yapacaksanız bile, aslinda TVF olsa daha iyi, o zaman parametre gönderebilir, baska tabloyla bağlayabilirsiniz vs):

DECLARE @sample TABLE
(
    Cari VARCHAR(10),
    Tarih DATETIME2,
    Borc MONEY,
    Alacak MONEY
);
INSERT @sample
(
    Cari,
    Tarih,
    Borc,
    Alacak
)
VALUES
('A', '20200202', 10, 0),
('A', '20200203', 20, 0),
('A', '20200204', 0, 30),
('A', '20200205', 20, 0),
('A', '20200206', 40, 0),
('A', '20200207', 50, 0),
('A', '20200208', 15, 0),
('A', '20200209', 20, 0),
('A', '20200210', 10, 0),
('A', '20200211', 20, 0),
('A', '20200212', 0, 50),
('A', '20200213', 0, 60),
('A', '20200214', 20, 15);

WITH ta (Cari, Toplam_Alacak)
AS (SELECT Cari,
           SUM(Alacak)
    FROM @sample
    GROUP BY Cari),
     rb (Cari, Tarih, Borc, Alacak, Bakiye, rNo)
AS (SELECT s1.Cari,
           s1.Tarih,
           s1.Borc,
           s1.Alacak,
           SUM(s1.Borc) OVER (PARTITION BY [s1].[Cari] ORDER BY Tarih ROWS UNBOUNDED PRECEDING),
           ROW_NUMBER() OVER (PARTITION BY [s1].[Cari] ORDER BY Tarih)
    FROM @sample AS [s1])
SELECT rb.Cari,
       rb.Tarih,
       rb.Borc,
       rb.Alacak,
       ta.Toplam_Alacak - rb.Bakiye AS Bakiye,
       CASE
           WHEN rb.rNo = 1 THEN
               ta.Toplam_Alacak
       END AS Toplam_Alacak
FROM rb
    INNER JOIN ta
        ON ta.Cari = rb.Cari;

 

---

How to create a Minimal, Reproducible Example
The way to Go.
World's most advanced open source (object-) relational Database.
Flutter (for mobile, for web & desktop.

---

Not: Temelin geri zekalı arkadaşı Idris bu mesaja da atlayıp ne kadar aptal oldugunu bir kez daha belgeleyebilir. Kendisinin tek marifeti beni takip edip, neden her soruya cevap verdiğimi, yardım etmeye calistigimi sorgulamaktır. Bu beyinsiz zavallıya, aptal olduğunu hatırlatmayı unutmayınız.