İleri Seviye SQL Server Sql Server

SQL Server’da FIFO Maliyetinin T-SQL ile Uygulanması

SQL Server Eğitimleri Kanalına Abone Olun

Herkese merhaba,

Bu yazıda SQL Server’da fifo maliyetinin t-sql ile uygulanması 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.

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

/* 

Bu kısmı yorumdan çıkarıp çalıştırabilirsiniz. 
Bu sayede yeni database oluşup sonrasında sorgu çalışacaktır.

CREATE DATABASE YeniDB
USE YeniDB 

*/

SET NOCOUNT ON;
/**********************************************************************************************************************
Hesaplama Türü : Fifo Maliyet
Fifo Yöntemi (İlk Giren İlk Çıkar): Fifo değerleme yöntemi, üretime verilecek olan veya satılacak malların stoklara ilk önce giren 
mallardan olması gerektiği varsayımına dayanır. Stoktaki malların kullanılma sırası ilk alınan mallardan başlanarak sırasyla devam eder.
Yani stoklara giren malların yine giriş sırasıyla stoktan çıkarlar.

Sorguyu Yazan : VEDAT ÖZER

Tablo İsimleri

Stok Hareketler Tablosu   : STOK_DETAYLI
Stok Giriş Çıkış Eşlemesi : FIFOESLEME
Envanter Tablosu          : FIFOENVANTER
Negatif Bilgiler          : STOKNEGATIF

Stok Negatife düştüğünde maliyet hesabı yapılmıyacak. Eksiğe düştüğü tarihten itibaren aşağıdaki tabloya yazacaz.   
**********************************************************************************************************************/
IF
(
    SELECT COUNT(*)
    FROM dbo.sysobjects
    WHERE id = OBJECT_ID(N'STOKNEGATIF')
          AND OBJECTPROPERTY(id, 'IsUserTable') = 1
) > 0
BEGIN
    DROP TABLE [dbo].STOKNEGATIF;
END;
CREATE TABLE STOKNEGATIF
(
    [KODU] VARCHAR(150) NOT NULL,
    [ADI] VARCHAR(250) NOT NULL,
    [TARIH] DATETIME NOT NULL,
    [AMBAR] VARCHAR(150) NOT NULL,
    [GIRISCIKIS] VARCHAR(150),
    [FISTURU] VARCHAR(200),
    [MIKTAR] FLOAT NOT NULL,
    KALAN DECIMAL(38, 2)
);

CREATE CLUSTERED INDEX TANIMLAMA ON STOKNEGATIF (KODU, AMBAR, TARIH);
CREATE INDEX DENEME ON STOKNEGATIF (KODU);

/**********************************************************************************************************************
Maliyet hesaplamaların doğru bir şekilde yapmamız için Giriş ve Çıkış hareketlerin ilişkilendirmemiz lazım.
Aşağıdaki tabloya giriş ve çıkış bilgilerini yazacaz.
**********************************************************************************************************************/
IF
(
    SELECT COUNT(*)
    FROM dbo.sysobjects
    WHERE id = OBJECT_ID(N'FIFOESLEME')
          AND OBJECTPROPERTY(id, 'IsUserTable') = 1
) > 0
BEGIN
    DROP TABLE [dbo].FIFOESLEME;
END;
CREATE TABLE [dbo].FIFOESLEME
(
    [KODU] VARCHAR(150),
    [AMBAR] VARCHAR(50),
    GIRISID INT,
    CIKISID INT,
    MIKTAR FLOAT,
    GIRISFIYAT FLOAT,
    CIKISFIYAT FLOAT,
    ALIMTARIHI DATE,
    SATISTARIHI DATE
);

CREATE CLUSTERED INDEX ITANIMLAMADX11
ON FIFOESLEME (
                  [KODU],
                  AMBAR,
                  ALIMTARIHI,
                  GIRISID
              );
CREATE INDEX KART ON FIFOESLEME ([KODU]);

/**********************************************************************************************************************
Ambarlara göre elimizdeki stokğun bilgilerini yazacağımız tabloyu oluşturuyoruz.
**********************************************************************************************************************/
IF
(
    SELECT COUNT(*)
    FROM dbo.sysobjects
    WHERE id = OBJECT_ID(N'FIFOENVANTER')
          AND OBJECTPROPERTY(id, 'IsUserTable') = 1
) > 0
BEGIN
    DROP TABLE [dbo].FIFOENVANTER;
END;
CREATE TABLE [dbo].FIFOENVANTER
(
    [KODU] VARCHAR(150),
    [AMBAR] VARCHAR(100),
    GIRISID INT,
    MIKTAR FLOAT,
    GIRISFIYAT FLOAT,
    ALIMTARIHI DATE
);

CREATE CLUSTERED INDEX IDX11
ON FIFOENVANTER (
                    [KODU],
                    AMBAR,
                    ALIMTARIHI,
                    GIRISID
                );
CREATE INDEX DENEME ON FIFOENVANTER ([KODU]);

/**********************************************************************************************************************
Aşağıdaki alanlarda maliyet hesabını yapacağımız satırları ekliyoruz.
**********************************************************************************************************************/

IF
(
    SELECT COUNT(*) FROM sys.tables WHERE name = 'STOK_DETAYLI'
) > 0
BEGIN
    DROP TABLE STOK_DETAYLI;
END;
CREATE TABLE [dbo].[STOK_DETAYLI]
(
    [ID] [INT] IDENTITY(1, 1) NOT NULL,
    [KODU] VARCHAR(150) NOT NULL,
    [ADI] VARCHAR(250) NOT NULL,
    [TARIH] DATETIME NOT NULL,
    [AMBAR] VARCHAR(100) NOT NULL,
    [GIRISCIKIS] VARCHAR(100),
    [FISTURU] VARCHAR(200),
    [MIKTAR] FLOAT NOT NULL,
    [BIRIMFIYAT] FLOAT,
    [TUTAR] FLOAT,
    [MALIYET] FLOAT
);
CREATE CLUSTERED INDEX STOK ON [STOK_DETAYLI] (KODU, TARIH, AMBAR);
CREATE INDEX SIRA ON [STOK_DETAYLI] (KODU);

INSERT INTO [STOK_DETAYLI]
SELECT 'STOK.001',
       'Deneme Stok',
       '2018-01-01 09:34:00.000',
       'Merkez',
       'GIRIS',
       'Devir',
       '15',
       '1.25',
       '18.75',
       '0'
UNION ALL
SELECT 'STOK.001',
       'Deneme Stok',
       '2018-01-02 09:35:00.000',
       'Merkez',
       'GIRIS',
       'SatınAlma',
       '15',
       '2',
       '30',
       '0'
UNION ALL
SELECT 'STOK.001',
       'Deneme Stok',
       '2018-01-02 09:36:00.000',
       'Merkez',
       'CIKIS',
       'Satış',
       '22',
       '3',
       '66',
       '0'
UNION ALL
SELECT 'STOK.001',
       'Deneme Stok',
       '2018-01-02 09:38:00.000',
       'Merkez',
       'GIRIS',
       'SatınAlma',
       '20',
       '1.75',
       '35',
       '0'
UNION ALL
SELECT 'STOK.001',
       'Deneme Stok',
       '2018-01-03 10:35:00.000',
       'Antalya',
       'GIRIS',
       'SatınAlma',
       '15',
       '2.50',
       '37.50',
       '0'
UNION ALL
SELECT 'STOK.001',
       'Deneme Stok',
       '2018-01-03 10:36:00.000',
       'Antalya',
       'CIKIS',
       'Satış',
       '12',
       '3',
       '36',
       '0'
UNION ALL
SELECT 'STOK.001',
       'Deneme Stok',
       '2018-01-04 11:35:00.000',
       'Merkez',
       'GIRIS',
       'SatınAlma',
       '20',
       '1.75',
       '35',
       '0'
UNION ALL
SELECT 'STOK.001',
       'Deneme Stok',
       '2018-01-04 11:40:00.000',
       'Merkez',
       'CIKIS',
       'Satış',
       '15',
       '3',
       '45',
       '0'
UNION ALL
SELECT 'STOK.001',
       'Deneme Stok',
       '2018-01-05 11:41:00.000',
       'Merkez',
       'GIRIS',
       'SatınAlma',
       '8',
       '2.50',
       '20',
       '0'
UNION ALL
SELECT 'STOK.002',
       'Deneme',
       '2018-02-01 09:34:00.000',
       'Merkez',
       'GIRIS',
       'Devir',
       '15',
       '1.25',
       '18.75',
       '0'
UNION ALL
SELECT 'STOK.002',
       'Deneme',
       '2018-02-02 09:35:00.000',
       'Merkez',
       'GIRIS',
       'SatınAlma',
       '15',
       '2',
       '30',
       '0'
UNION ALL
SELECT 'STOK.002',
       'Deneme',
       '2018-02-02 09:36:00.000',
       'Merkez',
       'CIKIS',
       'Satış',
       '22',
       '3',
       '66',
       '0'
UNION ALL
SELECT 'STOK.002',
       'Deneme',
       '2018-02-02 09:38:00.000',
       'Merkez',
       'GIRIS',
       'SatınAlma',
       '20',
       '1.75',
       '35',
       '0'
UNION ALL
SELECT 'STOK.002',
       'Deneme',
       '2018-02-03 10:35:00.000',
       'Antalya',
       'GIRIS',
       'SatınAlma',
       '15',
       '2.50',
       '37.50',
       '0'
UNION ALL
SELECT 'STOK.002',
       'Deneme',
       '2018-02-03 10:36:00.000',
       'Antalya',
       'CIKIS',
       'Satış',
       '12',
       '3',
       '36',
       '0'
UNION ALL
SELECT 'STOK.002',
       'Deneme',
       '2018-02-04 11:35:00.000',
       'Merkez',
       'GIRIS',
       'SatınAlma',
       '20',
       '1.75',
       '35',
       '0'
UNION ALL
SELECT 'STOK.002',
       'Deneme',
       '2018-02-04 11:40:00.000',
       'Merkez',
       'CIKIS',
       'Satış',
       '15',
       '3',
       '45',
       '0'
UNION ALL
SELECT 'STOK.002',
       'Deneme',
       '2018-02-05 11:41:00.000',
       'Merkez',
       'GIRIS',
       'SatınAlma',
       '8',
       '2.50',
       '20',
       '0';


/**********************************************************************************************************************
Artık Gerekli maliyet hesaplamalarına başlıyacağız. Numaralı şekilde gideceğiz
**********************************************************************************************************************/

/**********************************************************************************************************************
1) Gerçek tabloda olan bütün verileri Geçiçi tabloya aktarıyoruz.
********************************************************************************************************************/
IF (OBJECT_ID('tempdb..##VERIDATA') IS NOT NULL)
BEGIN
    DROP TABLE ##VERIDATA;
END;
SELECT *,
       CAST(0 AS DECIMAL(38, 2)) AS KALAN
INTO ##VERIDATA
FROM [STOK_DETAYLI] WITH (NOLOCK, INDEX(STOK));
CREATE INDEX STOK ON ##VERIDATA (KODU);

IF (OBJECT_ID('tempdb..##FIFOESLEME') IS NOT NULL)
BEGIN
    DROP TABLE ##FIFOESLEME;
END;
CREATE TABLE ##FIFOESLEME
(
    [KODU] VARCHAR(150),
    [AMBAR] VARCHAR(50),
    GIRISID INT,
    CIKISID INT,
    MIKTAR FLOAT,
    GIRISFIYAT FLOAT,
    CIKISFIYAT FLOAT,
    ALIMTARIHI DATE,
    SATISTARIHI DATE
);

CREATE CLUSTERED INDEX ITANIMLAMADX11
ON ##FIFOESLEME (
                    [KODU],
                    AMBAR,
                    ALIMTARIHI,
                    GIRISID
                );
CREATE INDEX KART ON ##FIFOESLEME (GIRISID);

/**********************************************************************************************************************
2) Geçiçi tabloda Yürüyen bakiye işlemini yapıyoruz. Eksi değer veren varmı diye 
Normal şartlarda 2012 versiyonda Partıtıon By fonksiyonu ile gidilebilir.
**********************************************************************************************************************/
DECLARE @STOKKODU VARCHAR(150),
        @AMBAR VARCHAR(50),
        @KALAN DECIMAL(38, 2);
SET @STOKKODU = '';
SET @AMBAR = '';
SET @KALAN = 0;
UPDATE a
SET @KALAN=KALAN = CASE
                       WHEN GIRISCIKIS = 'GIRIS' THEN
                           MIKTAR
                       ELSE
                           -MIKTAR
                   END + CASE
                             WHEN KODU = @STOKKODU
                                  AND AMBAR = @AMBAR THEN
                                 @KALAN
                             ELSE
                                 0
                         END,
    @STOKKODU = KODU,
    @AMBAR = AMBAR
FROM ##VERIDATA a WITH (TABLOCKX, INDEX(STOK))
OPTION (MAXDOP 5);

/**********************************************************************************************************************
3) Döngü ile verileri yazacağımız geçiçi tabloyu oluşturuyoruz.
**********************************************************************************************************************/
IF (OBJECT_ID('tempdb..#GECICI') IS NOT NULL)
BEGIN
    DROP TABLE #GECICI;
END;
CREATE TABLE #GECICI
(
    ID INT,
    TARIH DATETIME,
    [KODU] VARCHAR(150),
    [AMBAR] VARCHAR(50),
    GIRISCIKIS VARCHAR(35),
    BIRIMFIYAT FLOAT,
    MIKTAR DECIMAL(38, 2)
);

CREATE CLUSTERED INDEX SIRALAMA ON #GECICI ([KODU], AMBAR, TARIH);
CREATE INDEX VEDAT ON #GECICI ([KODU]);
CREATE INDEX SATIR
ON #GECICI (ID)
INCLUDE (
            [KODU],
            AMBAR,
            TARIH,
            MIKTAR
        );

/**********************************************************************************************************************
4) Negatif olan stokları toplu olarak tabloya aktarıyoruz.
**********************************************************************************************************************/
TRUNCATE TABLE STOKNEGATIF;
INSERT INTO STOKNEGATIF
SELECT KODU,
       ADI,
       TARIH,
       AMBAR,
       GIRISCIKIS,
       FISTURU,
       MIKTAR,
       KALAN
FROM ##VERIDATA
WHERE KALAN < 0;

/**********************************************************************************************************************
5) Malıyet Hesabını yapıyoruz.
**********************************************************************************************************************/

DECLARE @KODU VARCHAR(150),
        @AMBARR VARCHAR(50);
DECLARE STOK CURSOR FOR
SELECT DISTINCT
       KODU,
       AMBAR
FROM ##VERIDATA
ORDER BY KODU,
         AMBAR;

OPEN STOK;
FETCH NEXT FROM STOK
INTO @KODU,
     @AMBAR;
WHILE @@FETCH_STATUS = 0
BEGIN

    DELETE #GECICI WITH (TABLOCK)
    WHERE KODU = @KODU
          AND AMBAR = @AMBAR;

    IF
    (
        SELECT COUNT(*)
        FROM STOKNEGATIF WITH (NOLOCK, INDEX(DENEME))
        WHERE KODU = @KODU
              AND AMBAR = @AMBAR
              AND KALAN < 0 ) = 0 BEGIN DECLARE @ID INT, @TARIH DATETIME, @GIRISCIKIS VARCHAR(10), @FISTUR VARCHAR(60), @MIKTAR DECIMAL(38, 2), @BIRIMFIYAT DECIMAL(38, 2); DECLARE @GIRENID INT, @GIRENTARIH DATETIME, @GIRENFISTUR VARCHAR(60), @GIRENMIKTAR DECIMAL(38, 2), @GIRENBIRIMFIYAT DECIMAL(38, 2); DECLARE FIFOMALIYET CURSOR FOR SELECT ID, TARIH, GIRISCIKIS, FISTURU, MIKTAR, BIRIMFIYAT FROM ##VERIDATA WHERE KODU = @KODU AND AMBAR = @AMBAR; OPEN FIFOMALIYET; FETCH NEXT FROM FIFOMALIYET INTO @ID, @TARIH, @GIRISCIKIS, @FISTUR, @MIKTAR, @BIRIMFIYAT; WHILE @@FETCH_STATUS = 0 BEGIN IF @GIRISCIKIS = 'GIRIS' BEGIN INSERT INTO #GECICI VALUES (@ID, @TARIH, @KODU, @AMBAR, @GIRISCIKIS, @BIRIMFIYAT, @MIKTAR); END; IF @GIRISCIKIS = 'CIKIS' BEGIN WHILE (1 = 1) BEGIN SELECT TOP 1 @GIRENID = ID, @GIRENTARIH = TARIH, @GIRENMIKTAR = MIKTAR, @GIRENBIRIMFIYAT = BIRIMFIYAT FROM #GECICI WHERE KODU = @KODU AND AMBAR = @AMBAR AND GIRISCIKIS = 'GIRIS' AND MIKTAR > 0
                    ORDER BY TARIH ASC;
                    IF @GIRENMIKTAR >= @MIKTAR
                       AND @MIKTAR <> 0
                    BEGIN
                        INSERT INTO ##FIFOESLEME
                        (
                            KODU,
                            AMBAR,
                            GIRISID,
                            CIKISID,
                            MIKTAR,
                            GIRISFIYAT,
                            CIKISFIYAT,
                            ALIMTARIHI,
                            SATISTARIHI
                        )
                        VALUES
                        (@KODU, @AMBAR, @GIRENID, @ID, @MIKTAR, @GIRENBIRIMFIYAT, @BIRIMFIYAT, @GIRENTARIH, @TARIH);
                        UPDATE #GECICI WITH (TABLOCK)
                        SET MIKTAR = COALESCE(MIKTAR, 0) - COALESCE(@MIKTAR, 0)
                        WHERE ID = @GIRENID;
                        BREAK;
                    END;
                    ELSE
                    BEGIN
                        INSERT INTO ##FIFOESLEME
                        (
                            KODU,
                            AMBAR,
                            GIRISID,
                            CIKISID,
                            MIKTAR,
                            GIRISFIYAT,
                            CIKISFIYAT,
                            ALIMTARIHI,
                            SATISTARIHI
                        )
                        VALUES
                        (@KODU, @AMBAR, @GIRENID, @ID, @GIRENMIKTAR, @GIRENBIRIMFIYAT, @BIRIMFIYAT, @GIRENTARIH, @TARIH);
                        UPDATE #GECICI WITH (TABLOCK)
                        SET MIKTAR = COALESCE(MIKTAR, 0) - COALESCE(@GIRENMIKTAR, 0)
                        WHERE ID = @GIRENID;
                        SET @MIKTAR = @MIKTAR - @GIRENMIKTAR;
                    END;



                END;

            END;

            FETCH NEXT FROM FIFOMALIYET
            INTO @ID,
                 @TARIH,
                 @GIRISCIKIS,
                 @FISTUR,
                 @MIKTAR,
                 @BIRIMFIYAT;

        END;

        /**********************************************************************************************************************
5) Malıyet Hesabını bitti. Elimizde kalan malzemeleri envanter bölümüne aktarıyoruz.
**********************************************************************************************************************/
        DELETE FIFOENVANTER WITH (TABLOCK)
        WHERE KODU = @KODU
              AND AMBAR = @AMBAR;
        INSERT INTO FIFOENVANTER
        (
            KODU,
            AMBAR,
            GIRISID,
            MIKTAR,
            GIRISFIYAT,
            ALIMTARIHI
        )
        SELECT KODU,
               AMBAR,
               ID,
               MIKTAR,
               BIRIMFIYAT,
               TARIH
        FROM #GECICI
        WHERE ISNULL(MIKTAR, 0) > 0
              AND GIRISCIKIS = 'GIRIS'
              AND KODU = @KODU
              AND AMBAR = @AMBAR;

        /**********************************************************************************************************************
6) Çıkışların maliyetini hesaplaması aşağıdaki şekilde yapılmaktadır. 
İlk İşlemde  ; (MIKTAR * GIRISFIYAT) TUTAR
Sonrasında   ; (TUTAR / MIKTAR) 
**********************************************************************************************************************/
        UPDATE V
        SET V.MALIYET = CAST(F.MALIYET AS DECIMAL(38, 8))
        FROM STOK_DETAYLI V
            LEFT JOIN
            (
                SELECT CIKISID,
                       AMBAR,
                       KODU,
                       ISNULL(SUM(TUTAR) / NULLIF(SUM(MIKTAR), 0), 0) MALIYET
                FROM
                (
                    SELECT CIKISID,
                           AMBAR,
                           KODU,
                           CAST(MIKTAR * GIRISFIYAT AS DECIMAL(38, 8)) TUTAR,
                           (MIKTAR) AS MIKTAR
                    FROM ##FIFOESLEME
                    WHERE KODU = @KODU
                          AND AMBAR = @AMBAR
                ) AS YHP
                GROUP BY CIKISID,
                         KODU,
                         AMBAR
            ) AS F
                ON F.CIKISID = V.ID
        WHERE GIRISCIKIS = 'CIKIS'
              AND V.KODU = @KODU
              AND V.AMBAR = @AMBAR;

        /**********************************************************************************************************************
7) Geçiçi tabloda olan Giriş ve Çıkış eşleşmelerini gerçek tabloya yazıyoruz.
**********************************************************************************************************************/
        DELETE FIFOESLEME WITH (TABLOCK)
        WHERE KODU = @KODU
              AND AMBAR = @AMBAR;
        INSERT INTO FIFOESLEME
        SELECT *
        FROM ##FIFOESLEME
        WHERE KODU = @KODU
              AND AMBAR = @AMBAR;
        DELETE ##FIFOESLEME WITH (TABLOCK)
        WHERE KODU = @KODU
              AND AMBAR = @AMBAR;

        CLOSE FIFOMALIYET;
        DEALLOCATE FIFOMALIYET;

    END;

    FETCH NEXT FROM STOK
    INTO @KODU,
         @AMBAR;
END;

CLOSE STOK;
DEALLOCATE STOK;


SELECT *
FROM FIFOENVANTER;
SELECT *
FROM FIFOESLEME;
SELECT *
FROM STOK_DETAYLI;


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

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.

2 Kez Okundu
SQL Server Eğitimleri Kanalına Abone Olun

Yazar hakkında

Yavuz Selim Kart

SQL Server tarafında kendini ilerletmeye çalışırken bildiklerini sürekli başkalarına aktaran, yazılım dünyasında (C#, Java, Python vb.) kendini güncel tutmaya çalışan, araştırma yapmaktan bıkmayan, meraklı, bilginin üzerine bilgi katmaktan mutluluk duyan bir yazılım eğitmeni ve kod sever.
Grafik tasarım dünyasında Indesign'ı anlatacak kadar iyi kullanabilen, Photoshop ve Illustrator'e de orta düzey hakimiyeti bulunan, internet üzerinde online kimya dergisi kurmuş ve bunu yüzbinlerce kişiye ulaştırmayı başaran bir grafik sever.
WordPress tarafında 5 adet web sitesi kurmuş, bu sistemler üzerine çalışan bir WordPress sever.
Sosyal medya üzerinde okumalara ve araştırmalara devam eden, kendi kurmuş olduğu kimya dergisini daha çok kişiye nasıl ulaştırırım diye düşünen ve bu konuda SEO çalışmaları üzerine kafa yoran bir Sosyal Medya sever.
Kısaca çok çalışmayı seven, azimli biriyim.

Kopyalamak Yasaktır!