Uncategorized

LOGO SQL Yıl Bazlı Cari Ciro(Satış) Toplamları Raporu

SELECT TBL.CODE,TBL.DEFINITION_,ISNULL(CLC.TOWN,'')
,SUM(CASE WHEN YIL=2021 THEN NETTOTAL ELSE 0 END) T_2021
,SUM(CASE WHEN YIL=2020 THEN NETTOTAL ELSE 0 END) T_2020
,SUM(CASE WHEN YIL=2019 THEN NETTOTAL ELSE 0 END) T_2019
,SUM(CASE WHEN YIL=2018 THEN NETTOTAL ELSE 0 END) T_2018
,SUM(CASE WHEN YIL=2017 THEN NETTOTAL ELSE 0 END) T_2017
,SUM(CASE WHEN YIL=2017 THEN NETTOTAL ELSE 0 END) T_2016
,SUM(CASE WHEN YIL=2017 THEN NETTOTAL ELSE 0 END) T_2015
FROM (
SELECT  '2021' AS YIL,CLC.CODE,CLC.DEFINITION_,SUM(INV.NETTOTAL * CASE WHEN INV.TRCODE IN (7,8,9) THEN 1 ELSE -1 END) NETTOTAL FROM LG_021_CLCARD CLC WITH (NOLOCK)
INNER JOIN LG_021_01_INVOICE INV WITH (NOLOCK) ON INV.CLIENTREF=CLC.LOGICALREF AND INV.CANCELLED=0 AND INV.TRCODE IN (7,8,9,3)
GROUP BY CLC.CODE,CLC.DEFINITION_
UNION ALL
SELECT  '2020' AS YIL,CLC.CODE,CLC.DEFINITION_,SUM(INV.NETTOTAL * CASE WHEN INV.TRCODE IN (7,8,9) THEN 1 ELSE -1 END) NETTOTAL FROM LG_020_CLCARD CLC WITH (NOLOCK)
INNER JOIN LG_020_01_INVOICE INV WITH (NOLOCK) ON INV.CLIENTREF=CLC.LOGICALREF AND INV.CANCELLED=0 AND INV.TRCODE IN (7,8,9,3)
GROUP BY CLC.CODE,CLC.DEFINITION_
UNION ALL
SELECT  '2019' AS YIL,CLC.CODE,CLC.DEFINITION_,SUM(INV.NETTOTAL * CASE WHEN INV.TRCODE IN (7,8,9) THEN 1 ELSE -1 END) NETTOTAL FROM LG_019_CLCARD CLC WITH (NOLOCK)
INNER JOIN LG_019_01_INVOICE INV WITH (NOLOCK) ON INV.CLIENTREF=CLC.LOGICALREF AND INV.CANCELLED=0 AND INV.TRCODE IN (7,8,9,3)
GROUP BY CLC.CODE,CLC.DEFINITION_
UNION ALL
SELECT  '2018' AS YIL,CLC.CODE,CLC.DEFINITION_,SUM(INV.NETTOTAL * CASE WHEN INV.TRCODE IN (7,8,9) THEN 1 ELSE -1 END) NETTOTAL FROM LG_018_CLCARD CLC WITH (NOLOCK)
INNER JOIN LG_018_01_INVOICE INV WITH (NOLOCK) ON INV.CLIENTREF=CLC.LOGICALREF AND INV.CANCELLED=0 AND INV.TRCODE IN (7,8,9,3)
GROUP BY CLC.CODE,CLC.DEFINITION_
UNION ALL
SELECT  '2017' AS YIL,CLC.CODE,CLC.DEFINITION_,SUM(INV.NETTOTAL * CASE WHEN INV.TRCODE IN (7,8,9) THEN 1 ELSE -1 END) NETTOTAL FROM LG_017_CLCARD CLC WITH (NOLOCK)
INNER JOIN LG_017_01_INVOICE INV WITH (NOLOCK) ON INV.CLIENTREF=CLC.LOGICALREF AND INV.CANCELLED=0 AND INV.TRCODE IN (7,8,9,3)
GROUP BY CLC.CODE,CLC.DEFINITION_
UNION ALL
SELECT  '2016' AS YIL,CLC.CODE,CLC.DEFINITION_,SUM(INV.NETTOTAL * CASE WHEN INV.TRCODE IN (7,8,9) THEN 1 ELSE -1 END) NETTOTAL FROM LG_016_CLCARD CLC WITH (NOLOCK)
INNER JOIN LG_016_01_INVOICE INV WITH (NOLOCK) ON INV.CLIENTREF=CLC.LOGICALREF AND INV.CANCELLED=0 AND INV.TRCODE IN (7,8,9,3)
GROUP BY CLC.CODE,CLC.DEFINITION_
UNION ALL
SELECT  '2015' AS YIL,CLC.CODE,CLC.DEFINITION_,SUM(INV.NETTOTAL * CASE WHEN INV.TRCODE IN (7,8,9) THEN 1 ELSE -1 END) NETTOTAL FROM LG_015_CLCARD CLC WITH (NOLOCK)
INNER JOIN LG_015_01_INVOICE INV WITH (NOLOCK) ON INV.CLIENTREF=CLC.LOGICALREF AND INV.CANCELLED=0 AND INV.TRCODE IN (7,8,9,3)
GROUP BY CLC.CODE,CLC.DEFINITION_)TBL 

LEFT JOIN LG_021_CLCARD CLC WITH (NOLOCK) ON CLC.CODE=TBL.CODE 
GROUP BY TBL.CODE,TBL.DEFINITION_,CLC.TOWN

Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir