Stok Envanter Yaşlandırma Sorgusu

Sorgu, depoda bekleyen stokların hangi yaş aralıklarında olduğunu, yani depoda kaç gündür beklediklerini gösterir.

Stok envanter yaşlandırma raporunun SQL sorgusunu ve kullanım örneğini aşağıda bulabilirsiniz.

Sorgu:

CREATE PROCEDURE dbo.sp_Zukod_Stok_Envanter_Yaslandirma_Raporu
(
  @Bugun DATETIME=NULL,
  @StokKodu NVARCHAR (25)='',
  @HareketsizGunSayisi INT=0,
  @DepolarTekDepo BIT=0
)
AS
BEGIN

IF @Bugun IS NULL
  SET @Bugun=dbo.fn_DatePart(GETDATE())

SELECT TOP 100 PERCENT 
  ROW_NUMBER() OVER (ORDER BY sth_stok_kod,sth_depono,sth_tarih, sth_Guid) AS Id,
  sth_Guid,
  sth_stok_kod,
  CASE WHEN @DepolarTekDepo=0 THEN sth_depono ELSE 0 END AS sth_depono,
  sth_tarih,
  CASE WHEN sth_cins IN (9,15) OR sth_miktar=0 THEN 1 ELSE 0 END AS [IsDegerFarki],
  CAST(CASE WHEN sth_cins NOT IN(9,15) THEN sth_miktar ELSE 0.0 END AS FLOAT) AS sth_miktar,
  sth_giris_cikis AS sth_tip,
  sth_cins,
  sth_normal_iade
  INTO #hareketler
FROM dbo.fn_Stok_Giris_Cikis(N'',NULL,@Bugun,@DepolarTekDepo) H
WHERE (@StokKodu=N'' OR  sth_stok_kod=@StokKodu) AND 
  (@HareketsizGunSayisi=0 OR 
    NOT EXISTS (SELECT TOP 1 * 
          FROM dbo.fn_Stok_Giris_Cikis(N'',DATEADD(DAY,-1*@HareketsizGunSayisi,@Bugun),@Bugun,@DepolarTekDepo) T 
          WHERE T.sth_stok_kod=H.sth_stok_kod AND 
          T.sth_depono=H.sth_depono))
ORDER BY sth_stok_kod,sth_depono,sth_tarih, sth_Guid;


WITH #movements AS (
  SELECT TOP 100 PERCENT *
  FROM #hareketler
  WHERE IsDegerFarki=0
), 
net AS (
  SELECT
    sth_stok_kod,
    sth_depono,
    SUM( CASE WHEN sth_tip = 0 THEN sth_miktar ELSE -1 * sth_miktar END ) AS net
  FROM #movements
  GROUP BY sth_stok_kod,sth_depono
), 
inputs AS (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY sth_stok_kod,sth_depono ORDER BY sth_tarih, sth_Guid) AS AddId,
    ROW_NUMBER() OVER (PARTITION BY sth_stok_kod,sth_depono ORDER BY sth_tarih DESC, sth_Guid DESC) AS LastInp,
    *,
    SUM(sth_miktar) OVER (PARTITION BY sth_stok_kod,sth_depono ORDER BY sth_tarih ROWS UNBOUNDED PRECEDING) AS SUMIn,
    SUM(sth_miktar) OVER (PARTITION BY sth_stok_kod,sth_depono ORDER BY sth_tarih DESC ROWS UNBOUNDED PRECEDING) AS SUMInDesc
   FROM #movements
   WHERE
    (sth_tip = 0)
  ), 
  outputs AS (
   SELECT
    ROW_NUMBER() OVER (PARTITION BY sth_stok_kod,sth_depono ORDER BY sth_tarih, sth_Guid) AS AddId,
    *,
    SUM(sth_miktar) OVER (PARTITION BY sth_stok_kod,sth_depono ORDER BY sth_tarih ROWS UNBOUNDED PRECEDING) AS SUMOut
   FROM #movements
   WHERE
    (sth_tip = 1)
  ), 
  calculation AS (
   SELECT
    inputs.*,
    net.net,
    net.net - inputs.SUMInDesc AS diff
   FROM inputs
   left join net
    on inputs.sth_stok_kod = net.sth_stok_kod and inputs.sth_depono = net.sth_depono
)


SELECT
  CASE WHEN @DepolarTekDepo=0 THEN dbo.fn_DepoIsmi(H.sth_depono) ELSE 'Toplam' END AS [Depo], 
  H.sth_stok_kod AS [Kodu], 
  sto_isim AS [İsmi],
  sto_anagrup_kod AS [Ana Grup],
  sto_altgrup_kod AS [Alt Grup],
  [Son Hareket Tarihi],
  [Hareketsiz Gün Sayısı],
  CASE 
    WHEN [Hareketsiz Gün Sayısı]>360 THEN '1. > 360'
    WHEN [Hareketsiz Gün Sayısı]>180 THEN '2. > 180'
    WHEN [Hareketsiz Gün Sayısı]> 90 THEN '3. >  90'
    WHEN [Hareketsiz Gün Sayısı]<=90 THEN '4. <  90'
    ELSE ''
  END AS [Hareketsiz Gün Sayısı Durumu],
  [<-90],
  [91-180],
  [181-360],
  [361 ->],
  [Ortalama]
FROM
(
SELECT 
  sth_stok_kod,
  sth_depono,
  (SELECT TOP 1 T.sth_tarih FROM #hareketler T WHERE T.sth_stok_kod=I.sth_stok_kod AND T.sth_depono=I.sth_depono ORDER BY sth_tarih DESC)  AS [Son Hareket Tarihi],
  (SELECT TOP 1 DATEDIFF(DAY,T.sth_tarih,@Bugun) FROM #hareketler T WHERE T.sth_stok_kod=I.sth_stok_kod AND T.sth_depono=I.sth_depono ORDER BY sth_tarih DESC)  AS [Hareketsiz Gün Sayısı],
  SUM(CASE WHEN AgeingInDays<=90 THEN InStockQuantity ELSE 0.0 END) AS [<-90],
  SUM(CASE WHEN AgeingInDays BETWEEN 91 AND 180 THEN InStockQuantity ELSE 0.0 END) AS [91-180],
  SUM(CASE WHEN AgeingInDays BETWEEN 181 AND 360 THEN InStockQuantity ELSE 0.0 END) AS [181-360],
  SUM(CASE WHEN AgeingInDays>=361 THEN InStockQuantity ELSE 0.0 END) AS [361 ->],
  CASE WHEN SUM(AgeingInDays*InStockQuantity)<>0.0 THEN SUM(AgeingInDays*InStockQuantity)/SUM(InStockQuantity) ELSE 0.0 END AS [Ortalama],
  SUM(InStockQuantity) AS [Toplam Miktar]
FROM
(
SELECT
 sth_stok_kod, 
 sth_depono,
 CASE WHEN diff > 0 then sth_miktar else sth_miktar-abs(diff) end AS InStockQuantity,
 DATEDIFF(DAY,sth_tarih, @Bugun) AS AgeingInDays
FROM (
 SELECT *, 1 AS rn FROM calculation WHERE diff >= 0
 union all
 (
  SELECT * FROM 
  (
   SELECT
    *, ROW_NUMBER() OVER (PARTITION BY sth_stok_kod,sth_depono ORDER BY diff desc) AS rn
   FROM calculation
   WHERE diff < 0
  ) t WHERE rn = 1
 )
) AS temptable
) I
group by sth_stok_kod,sth_depono
) H
left outer join STOKLAR WITH (NOLOCK) ON (sto_kod=sth_stok_kod)
left outer join DEPOLAR WITH (NOLOCK) ON (dep_no=sth_depono)
ORDER BY dep_subeno, dep_no,H.sth_stok_kod

END

Örnek Rapor Görünümü: