Şubeler Dahil Sayım Fark Raporu
asagida yazdigim sorguyu siz stored procedure veya table value function olarak yazarsaniz (View degil)
isinizi görür
WITH Stok AS (select STOK_KODU,SUBE_KODU,SUM(case when STHAR_GCKOD='G' THEN STHAR_GCMIK ELSE -STHAR_GCMIK END) STOK
FROM TBLSTHAR
WHERE STHAR_TARIH BETWEEN '2016-01-01' AND '2016-03-10' AND STHAR_HTUR<>'N'
GROUP BY STOK_KODU,SUBE_KODU),
Sayim AS (SELECT SUBE_KODU,STOK_KODU,MIKTAR FROM TBLSAYIM WHERE TARIH='2016-03-10'),
TblPivot AS (SELECT A.STOK_KODU,STOK_ADI,COALESCE(B.SUBE_KODU,C.SUBE_KODU) SUBE_KODU,ALIS_FIAT1 FIYAT,
MAX(C.STOK) STOK,MAX(B.MIKTAR) SAYIM
FROM Sayim B
FULL JOIN Stok C ON B.SUBE_KODU=C.SUBE_KODU AND B.STOK_KODU=C.STOK_KODU
JOIN TBLSTSABIT A (NOLOCK) ON A.STOK_KODU=COALESCE(B.STOK_KODU,C.STOK_KODU) --AND B.SUBE_KODU=B.DEPO_KODU
GROUP BY A.STOK_KODU,STOK_ADI,COALESCE(B.SUBE_KODU,C.SUBE_KODU),ALIS_FIAT1)
SELECT STOK_KODU,STOK_ADI,FIYAT
,MAX([STOK_0]) AS STOK_0
,MAX([SAYIM_0]) AS SAYIM_0
,MAX([STOK_1]) AS STOK_1
,MAX([SAYIM_1]) AS SAYIM_1
,MAX([STOK_2]) AS STOK_2
,MAX([SAYIM_2]) AS SAYIM_2
,MAX([STOK_3]) AS STOK_3
,MAX([SAYIM_3]) AS SAYIM_3
,MAX([STOK_4]) AS STOK_4
,MAX([SAYIM_4]) AS SAYIM_4
,MAX([STOK_5]) AS STOK_5
,MAX([SAYIM_5]) AS SAYIM_5
,MAX([STOK_6]) AS STOK_6
,MAX([SAYIM_6]) AS SAYIM_6
,MAX([STOK_7]) AS STOK_7
,MAX([SAYIM_7]) AS SAYIM_7
,MAX([STOK_8]) AS STOK_8
,MAX([SAYIM_8]) AS SAYIM_8
,MAX([STOK_9]) AS STOK_9
,MAX([SAYIM_9]) AS SAYIM_9
--,MAX([STOK_10]) AS STOK_10
--,MAX([SAYIM_10]) AS SAYIM_10
FROM (SELECT STOK_KODU,STOK_ADI,FIYAT,'STOK_'+CONVERT(VARCHAR,SUBE_KODU) SUBE_STOK,'SAYIM_'+CONVERT(VARCHAR,SUBE_KODU) SUBE_SAYIM,SUM(STOK) AS STOK,
SUM(SAYIM) As SAYIM
FROM TblPivot
GROUP BY STOK_KODU,STOK_ADI,FIYAT,SUBE_KODU) AS T
PIVOT (SUM(STOK) FOR SUBE_STOK IN ([STOK_0],[STOK_1],[STOK_2],[STOK_3],[STOK_4],[STOK_5],[STOK_6],[STOK_7],[STOK_8],[STOK_9]/*,[STOK_10]*/)) AS P1
PIVOT (SUM(SAYIM) FOR SUBE_SAYIM IN ([SAYIM_0],[SAYIM_1],[SAYIM_2],[SAYIM_3],[SAYIM_4],[SAYIM_5],[SAYIM_6],[SAYIM_7],[SAYIM_8],[SAYIM_9]/*,[SAYIM_10]*/)) AS P2
GROUP BY STOK_KODU,STOK_ADI,FIYAT;
Sube'lerin Lokal deposu 1'den fazla ise asagidaki sekilde
WITH Stok AS (select STOK_KODU,SUBE_KODU,DEPO_KODU,SUM(case when STHAR_GCKOD='G' THEN STHAR_GCMIK ELSE -STHAR_GCMIK END) STOK
FROM TBLSTHAR
WHERE STHAR_TARIH BETWEEN '2016-01-01' AND '2016-03-10' AND STHAR_HTUR<>'N'
GROUP BY STOK_KODU,SUBE_KODU,DEPO_KODU),
Sayim AS (SELECT SUBE_KODU,DEPO_KODU,STOK_KODU,MIKTAR FROM TBLSAYIM WHERE TARIH='2016-03-10'),
TblPivot AS (SELECT A.STOK_KODU,STOK_ADI,COALESCE(B.SUBE_KODU,C.SUBE_KODU) SUBE_KODU,COALESCE(B.DEPO_KODU,C.DEPO_KODU) DEPO_KODU,ALIS_FIAT1 FIYAT,
MAX(C.STOK) STOK,MAX(B.MIKTAR) SAYIM
FROM Sayim B
FULL JOIN Stok C ON B.SUBE_KODU=C.SUBE_KODU AND B.STOK_KODU=C.STOK_KODU AND C.DEPO_KODU=B.DEPO_KODU
JOIN TBLSTSABIT A (NOLOCK) ON A.STOK_KODU=COALESCE(B.STOK_KODU,C.STOK_KODU) --AND B.SUBE_KODU=B.DEPO_KODU
GROUP BY A.STOK_KODU,STOK_ADI,COALESCE(B.SUBE_KODU,C.SUBE_KODU),COALESCE(B.DEPO_KODU,C.DEPO_KODU),ALIS_FIAT1)
SELECT STOK_KODU,STOK_ADI,FIYAT
,SUM([STOK_0]) AS STOK_0
,SUM([SAYIM_0]) AS SAYIM_0
,SUM([STOK_1]) AS STOK_1
,SUM([SAYIM_1]) AS SAYIM_1
,SUM([STOK_2]) AS STOK_2
,SUM([SAYIM_2]) AS SAYIM_2
,SUM([STOK_3]) AS STOK_3
,SUM([SAYIM_3]) AS SAYIM_3
,SUM([STOK_4]) AS STOK_4
,SUM([SAYIM_4]) AS SAYIM_4
,SUM([STOK_5]) AS STOK_5
,SUM([SAYIM_5]) AS SAYIM_5
,SUM([STOK_6]) AS STOK_6
,SUM([SAYIM_6]) AS SAYIM_6
,SUM([STOK_7]) AS STOK_7
,SUM([SAYIM_7]) AS SAYIM_7
,SUM([STOK_8]) AS STOK_8
,SUM([SAYIM_8]) AS SAYIM_8
,SUM([STOK_9]) AS STOK_9
,SUM([SAYIM_9]) AS SAYIM_9
--,SUM([STOK_10]) AS STOK_10
--,SUM([SAYIM_10]) AS SAYIM_10
FROM (SELECT STOK_KODU,STOK_ADI,FIYAT,'STOK_'+CONVERT(VARCHAR,SUBE_KODU) SUBE_STOK,'SAYIM_'+CONVERT(VARCHAR,SUBE_KODU) SUBE_SAYIM,SUM(STOK) AS STOK,
SUM(SAYIM) As SAYIM
FROM TblPivot
GROUP BY STOK_KODU,STOK_ADI,FIYAT,SUBE_KODU,DEPO_KODU) AS T
PIVOT (SUM(STOK) FOR SUBE_STOK IN ([STOK_0],[STOK_1],[STOK_2],[STOK_3],[STOK_4],[STOK_5],[STOK_6],[STOK_7],[STOK_8],[STOK_9]/*,[STOK_10]*/)) AS P1
PIVOT (SUM(SAYIM) FOR SUBE_SAYIM IN ([SAYIM_0],[SAYIM_1],[SAYIM_2],[SAYIM_3],[SAYIM_4],[SAYIM_5],[SAYIM_6],[SAYIM_7],[SAYIM_8],[SAYIM_9]/*,[SAYIM_10]*/)) AS P2
GROUP BY STOK_KODU,STOK_ADI,FIYAT;