SQL Server’da Yürüyen Bakiye

Herkese merhaba,

Bu yazıda SQL Server’da yürüyen bakiye hakkında bilgi vereceğim.

Aslında bu bilgiyi veren ve kodları hazırlayan kişi Vedat Özer Bey. Sorguyu hazırlamış. Gerekli açıklamaları eklemiş. Bana da paylaşması kaldı.

Kendisine bu çalışması için ayrıca teşekkürler.

İlk olarak yürüyen bakiye nedir kısmına değinelim.

Yürüyen bakiye kısaca müşteriden tahsilat yapıldıktan sonra müşteri bakiyesinin yeniden hesaplanmasıdır.

Aşağıdaki kod bloğunu yeni oluşturduğunuz veritabanı üzerinde deneyebilirsiniz.

SET NOCOUNT ON;
IF (OBJECT_ID('tempdb..#SONUC') IS NOT NULL)
BEGIN
    DROP TABLE #SONUC;
END;
CREATE TABLE #SONUC
(
    TARIH DATETIME,
    KOD VARCHAR(15),
    MIKTAR DECIMAL(38, 2),
    DEPOKODU VARCHAR(15),
    ONCEKIBAKIYE DECIMAL(38, 2),
    GUNCELBAKIYE DECIMAL(38, 2)
);
IF (OBJECT_ID('tempdb..#TABLOM') IS NOT NULL)
BEGIN
    DROP TABLE #TABLOM;
END;
CREATE TABLE #TABLOM
(
    TARIH DATETIME,
    KOD VARCHAR(15),
    MIKTAR DECIMAL(38, 2),
    DEPOKODU VARCHAR(15)
);
INSERT INTO #TABLOM
(
    TARIH,
    KOD,
    MIKTAR,
    DEPOKODU
)
SELECT '2019-12-10',
       'A001',
       10,
       '100'
UNION ALL
SELECT '2019-12-10',
       'A001',
       -10,
       '100'
UNION ALL
SELECT '2019-12-10',
       'A001',
       10,
       '102'
UNION ALL
SELECT '2019-12-10',
       'B001',
       10,
       '100'
UNION ALL
SELECT '2019-12-20',
       'B001',
       -20,
       '100';
CREATE CLUSTERED INDEX COZUMPARK ON #TABLOM (KOD, DEPOKODU, TARIH);
DECLARE @GUNCELBAKIYE DECIMAL(38, 2);
SET @GUNCELBAKIYE = 0;
DECLARE @ONCEKIBAKIYE DECIMAL(38, 2);
DECLARE @ALTBAKIYE DECIMAL(38, 2);
SET @ONCEKIBAKIYE = 0;
DECLARE @KOD VARCHAR(15),
        @DEPO VARCHAR(15);
DECLARE STOKKART CURSOR FOR
SELECT KOD,
       DEPOKODU
FROM #TABLOM
GROUP BY KOD,
         DEPOKODU;
OPEN STOKKART;
FETCH NEXT FROM STOKKART
INTO @KOD,
     @DEPO;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @@ERROR = 0
    BEGIN
        SET @GUNCELBAKIYE = 0;
        SET @ONCEKIBAKIYE = 0;
        DECLARE @TARIH DATETIME,
                @MIKTAR DECIMAL(38, 2);
        DECLARE HAREKET CURSOR FOR
        SELECT TARIH,
               MIKTAR
        FROM #TABLOM
        WHERE KOD = @KOD
              AND DEPOKODU = @DEPO
        ORDER BY TARIH ASC;
        OPEN HAREKET;
        FETCH NEXT FROM HAREKET
        INTO @TARIH,
             @MIKTAR;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF @@ERROR = 0
            BEGIN
                SET @GUNCELBAKIYE = @GUNCELBAKIYE + @MIKTAR;
                SET @ONCEKIBAKIYE = @ONCEKIBAKIYE + @GUNCELBAKIYE - @MIKTAR;
                INSERT INTO #SONUC
                (
                    TARIH,
                    KOD,
                    MIKTAR,
                    DEPOKODU,
                    ONCEKIBAKIYE,
                    GUNCELBAKIYE
                )
                VALUES
                (@TARIH, @KOD, @MIKTAR, @DEPO, @ONCEKIBAKIYE, @GUNCELBAKIYE);
            END;
            FETCH NEXT FROM HAREKET
            INTO @TARIH,
                 @MIKTAR;
        END;
        CLOSE HAREKET;
        DEALLOCATE HAREKET;
    END;
    FETCH NEXT FROM STOKKART
    INTO @KOD,
         @DEPO;
END;
CLOSE STOKKART;
DEALLOCATE STOKKART;
SELECT *
FROM #SONUC;

Sorguyu çalıştırdığınızda aşağıdaki gibi bir sonuç alacaksınız.

Aşağıda bu işlemin farklı çözümleri de mevcut. Kodları deneyebilirsiniz.

SET NOCOUNT ON;
IF (OBJECT_ID('tempdb..#TABLOM') IS NOT NULL)
BEGIN
    DROP TABLE #TABLOM;
END;
CREATE TABLE #TABLOM
(
    SATIRNO INT IDENTITY(1, 1),
    TARIH DATETIME,
    KOD VARCHAR(15),
    MIKTAR DECIMAL(38, 2),
    DEPOKODU VARCHAR(15),
    ONCEKIBAKIYE DECIMAL(38, 2),
    GUNCELBAKIYE DECIMAL(38, 2)
);
CREATE CLUSTERED INDEX SIRALAMA ON #TABLOM (KOD, DEPOKODU, TARIH);
CREATE INDEX STOKKART ON #TABLOM (KOD);
INSERT INTO #TABLOM
(
    TARIH,
    KOD,
    MIKTAR,
    DEPOKODU,
    ONCEKIBAKIYE,
    GUNCELBAKIYE
)
SELECT '2019-12-10',
       'A001',
       10,
       '100',
       0,
       0
UNION ALL
SELECT '2019-12-10',
       'A001',
       -10,
       '100',
       0,
       0
UNION ALL
SELECT '2019-12-10',
       'A001',
       10,
       '102',
       0,
       0
UNION ALL
SELECT '2019-12-10',
       'B001',
       10,
       '100',
       0,
       0
UNION ALL
SELECT '2019-12-20',
       'B001',
       -20,
       '100',
       0,
       0; 


DECLARE @KOD VARCHAR(15);
DECLARE @DEPO VARCHAR(15),
        @GUNCELBAKIYE DECIMAL(38, 2),
        @ONCEKIBAKIYE DECIMAL(38, 2);
SET @KOD = '';
SET @DEPO = '';
SET @GUNCELBAKIYE = 0;
SET @ONCEKIBAKIYE = 0;
UPDATE T
SET @GUNCELBAKIYE=GUNCELBAKIYE = MIKTAR + CASE
                                              WHEN KOD = @KOD
                                                   AND DEPOKODU = @DEPO THEN
                                                  @GUNCELBAKIYE
                                              ELSE
                                                  0
                                          END,
    @ONCEKIBAKIYE=ONCEKIBAKIYE = CASE
                                     WHEN KOD = @KOD
                                          AND DEPOKODU = @DEPO THEN
                                         @GUNCELBAKIYE - MIKTAR
                                     ELSE
                                         0
                                 END,
    @KOD = KOD,
    @DEPO = DEPOKODU
FROM #TABLOM AS T WITH (TABLOCK)
OPTION (MAXDOP 1);
SELECT *
FROM #TABLOM;


SELECT SATIRNO,
       TARIH,
       KOD,
       MIKTAR,
       DEPOKODU,
       BAK.BAKIYE - MIKTAR [ONCEKIBAKIYE],
       BAK.BAKIYE [GUNCELBAKIYE]
FROM #TABLOM AS T
    OUTER APPLY
(
    SELECT SUM(MIKTAR) AS BAKIYE
    FROM #TABLOM AS BAKIYE
    WHERE BAKIYE.KOD = T.KOD
          AND BAKIYE.DEPOKODU = T.DEPOKODU
          AND BAKIYE.TARIH <= T.TARIH
          AND BAKIYE.SATIRNO <= T.SATIRNO
) BAK;



SELECT SATIRNO,
       TARIH,
       KOD,
       MIKTAR,
       DEPOKODU,
       BAKIYE - MIKTAR [ONCEKIBAKIYE],
       BAKIYE [GUNCELBAKIYE]
FROM
(
    SELECT SATIRNO,
           TARIH,
           KOD,
           MIKTAR,
           DEPOKODU,
           (
               SELECT SUM(MIKTAR)
               FROM #TABLOM BAKIYE
               WHERE BAKIYE.KOD = T.KOD
                     AND BAKIYE.DEPOKODU = T.DEPOKODU
                     AND BAKIYE.TARIH <= T.TARIH
                     AND BAKIYE.SATIRNO <= T.SATIRNO
           ) BAKIYE
    FROM #TABLOM AS T
) AS BAK;



SELECT SATIRNO,
       TARIH,
       KOD,
       MIKTAR,
       DEPOKODU,
       BAKIYE - MIKTAR [ONCEKIBAKIYE],
       BAKIYE [GUNCELBAKIYE]
FROM
(
    SELECT SATIRNO,
           TARIH,
           KOD,
           MIKTAR,
           DEPOKODU,
           SUM(MIKTAR) OVER (PARTITION BY KOD,
                                          DEPOKODU
                             ORDER BY TARIH
                             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                            ) BAKIYE
    FROM #TABLOM AS T
) AS BAK;

Yukarıdaki kodları çalıştırdığınızda aşağıdaki gibi bir sonuç göreceksiniz.

Sorgu ile ilgili detaylı bilgi almak isterseniz Vedat Özer Beye ulaşabilirsiniz.

Herkese çalışma hayatında ve yaşamında başarılar kolaylıklar.