Selamlar,
İade ile ilgili sorgu
WITH TABLOA
AS (
SELECT '2016' FİRMA,
ITM.CODE,
ITM.NAME,
SUM(CASE WHEN STL.TRCODE IN(7,8) THEN STL.AMOUNT ELSE -STL.AMOUNT END) AS MİKTAR2016,
SUM(CASE WHEN STL.TRCODE IN(7,8) THEN STL.VATMATRAH ELSE -STL.VATMATRAH END)AS TUTAR2016
FROM dbo.LG_016_01_STLINE AS STL
INNER JOIN dbo.LG_016_ITEMS AS ITM
ON ITM.LOGICALREF = STL.STOCKREF
WHERE
STL.LINETYPE IN(0)
AND STL.CANCELLED =0
AND STL.TRCODE IN(7,8,2,3)
GROUP BY
ITM.NAME,
ITM.CODE
),
TABLOB
AS (SELECT '2015' FİRMA,
ITM.CODE,
ITM.NAME,
SUM(CASE WHEN STL.TRCODE IN(7,8) THEN STL.AMOUNT ELSE -STL.AMOUNT END) AS MİKTAR2015,
SUM(CASE WHEN STL.TRCODE IN(7,8) THEN STL.VATMATRAH ELSE -STL.VATMATRAH END) AS TUTAR2015
FROM dbo.LG_015_01_STLINE AS STL
INNER JOIN dbo.LG_015_ITEMS AS ITM
ON ITM.LOGICALREF = STL.STOCKREF
WHERE
STL.LINETYPE IN(0)
AND STL.CANCELLED =0
AND STL.TRCODE IN(7,8,2,3)
GROUP BY
ITM.NAME,
ITM.CODE
)
SELECT TABLOA.CODE,
TABLOA.NAME,
TABLOA.MİKTAR2016,
TABLOB.MİKTAR2015,
TABLOA.TUTAR2016,
TABLOB.TUTAR2015,
TABLOA.TUTAR2016 - TABLOB.TUTAR2015 AS FARK
FROM TABLOA
INNER JOIN TABLOB
ON TABLOB.NAME = TABLOA.NAME;
Karşılaştırma ile ilgili sorgu
WITH TABLOA
AS (
SELECT '2016' FİRMA,
ITM.CODE,
ITM.NAME,
SUM(CASE WHEN STL.TRCODE IN(7,8) THEN STL.AMOUNT ELSE -STL.AMOUNT END) AS MİKTAR2016,
SUM(CASE WHEN STL.TRCODE IN(7,8) THEN STL.VATMATRAH ELSE -STL.VATMATRAH END)AS TUTAR2016
FROM dbo.LG_016_01_STLINE AS STL
INNER JOIN dbo.LG_016_ITEMS AS ITM
ON ITM.LOGICALREF = STL.STOCKREF
WHERE
STL.LINETYPE IN(0)
AND STL.CANCELLED =0
AND STL.TRCODE IN(7,8,2,3)
GROUP BY
ITM.NAME,
ITM.CODE
),
TABLOB
AS (SELECT '2015' FİRMA,
ITM.CODE,
ITM.NAME,
SUM(CASE WHEN STL.TRCODE IN(7,8) THEN STL.AMOUNT ELSE -STL.AMOUNT END) AS MİKTAR2015,
SUM(CASE WHEN STL.TRCODE IN(7,8) THEN STL.VATMATRAH ELSE -STL.VATMATRAH END) AS TUTAR2015
FROM dbo.LG_015_01_STLINE AS STL
INNER JOIN dbo.LG_015_ITEMS AS ITM
ON ITM.LOGICALREF = STL.STOCKREF
WHERE
STL.LINETYPE IN(0)
AND STL.CANCELLED =0
AND STL.TRCODE IN(7,8,2,3)
GROUP BY
ITM.NAME,
ITM.CODE
)
SELECT TABLOA.CODE,
TABLOA.NAME,
TABLOA.MİKTAR2016,
TABLOB.MİKTAR2015,
TABLOA.TUTAR2016,
TABLOB.TUTAR2015,
TABLOA.TUTAR2016 - TABLOB.TUTAR2015 AS FARK
FROM TABLOA
LEFT OUTER JOIN TABLOB
ON TABLOB.NAME = TABLOA.NAME;