SQL Sorgu Alış Satış Karşılaştırma Raporu

SELECT YKN.URUN_KODU,YKN.URUN_ACIKLAMASI,
    SUM(YKN.TOPLAMSATIS)TOPLAM_SATIS,
    SUM(YKN.TOPLAMALIS)TOPLAM_ALIS,
    SUM(YKN.TOPLAMSATIS)-SUM(YKN.TOPLAMALIS)TOPLAM_FARK,
    SUM(YKN.OCAK_SATIS)OCAK_SATIS,SUM(YKN.OCAK_ALIS)OCAK_ALIS,SUM(YKN.OCAK_SATIS)-SUM(YKN.OCAK_ALIS)OCAK_FARK,
    SUM(YKN.SUBAT_SATIS)SUBAT_SATIS,SUM(YKN.SUBAT_ALIS)SUBAT_ALIS,SUM(YKN.SUBAT_SATIS)-SUM(YKN.SUBAT_ALIS)SUBAT_FARK,
    SUM(YKN.MART_SATIS)MART_SATIS,SUM(YKN.MART_ALIS)MART_ALIS,SUM(YKN.MART_SATIS)-SUM(YKN.MART_ALIS)MART_FARK,
    SUM(YKN.NISAN_SATIS)NISAN_SATIS,SUM(YKN.NISAN_ALIS)NISAN_ALIS,SUM(YKN.NISAN_SATIS)-SUM(YKN.NISAN_ALIS)NISAN_FARK,
    SUM(YKN.MAYIS_SATIS)MAYIS_SATIS,SUM(YKN.MAYIS_ALIS)MAYIS_ALIS,SUM(YKN.MAYIS_SATIS)-SUM(YKN.MAYIS_ALIS)MAYIS_FARK,
    SUM(YKN.HAZIRAN_SATIS)HAZIRAN_SATIS,SUM(YKN.HAZIRAN_ALIS)HAZIRAN_ALIS,SUM(YKN.HAZIRAN_SATIS)-SUM(YKN.HAZIRAN_ALIS)HAZIRAN_FARK,
    SUM(YKN.TEMMUZ_SATIS)TEMMUZ_SATIS,SUM(YKN.TEMMUZ_ALIS)TEMMUZ_ALIS,SUM(YKN.TEMMUZ_SATIS)-SUM(YKN.TEMMUZ_ALIS)TEMMUZ_FARK,
    SUM(YKN.AGUSTOS_SATIS)AGUSTOS_SATIS,SUM(YKN.AGUSTOS_ALIS)AGUSTOS_ALIS,SUM(YKN.AGUSTOS_SATIS)-SUM(YKN.AGUSTOS_ALIS)AGUSTOS_FARK,
    SUM(YKN.EYLUL_SATIS)EYLUL_SATIS,SUM(YKN.EYLUL_ALIS)EYLUL_ALIS,SUM(YKN.EYLUL_SATIS)-SUM(YKN.EYLUL_ALIS)EYLUL_FARK,
    SUM(YKN.EKIM_SATIS)EKIM_SATIS,SUM(YKN.EKIM_ALIS)EKIM_ALIS,SUM(YKN.EKIM_SATIS)-SUM(YKN.EKIM_ALIS)EKIM_FARK,
    SUM(YKN.KASIM_SATIS)KASIM_SATIS,SUM(YKN.KASIM_ALIS)KASIM_ALIS,SUM(YKN.KASIM_SATIS)-SUM(YKN.KASIM_ALIS)KASIM_FARK,
    SUM(YKN.ARALIK_SATIS)ARALIK_SATIS,SUM(YKN.ARALIK_ALIS)ARALIK_ALIS,SUM(YKN.ARALIK_SATIS)-SUM(YKN.ARALIK_ALIS)ARALIK_FARK
FROM (SELECT(SELECT CODE FROM LG_115_ITEMS WHERE LOGICALREF=S.STOCKREF) AS URUN_KODU,
(SELECT NAME FROM LG_115_ITEMS WHERE LOGICALREF=S.STOCKREF) AS URUN_ACIKLAMASI,
CASE WHEN S.TRCODE IN(7,8) THEN ISNULL(SUM(S.VATMATRAH),0) ELSE 0 END AS TOPLAMSATIS,
CASE WHEN S.TRCODE IN(1,14) THEN ISNULL(SUM(S.VATMATRAH),0) ELSE 0 END AS TOPLAMALIS,
    ISNULL(CASE WHEN MONTH(S.DATE_)=1 AND TRCODE IN(7,8) THEN SUM(S.VATMATRAH) ELSE 0 END,0) AS OCAK_SATIS,
    ISNULL(CASE WHEN MONTH(S.DATE_)=1 AND TRCODE IN(1,14) THEN SUM(S.VATMATRAH) ELSE 0 END,0) AS OCAK_ALIS,
    ISNULL(CASE WHEN MONTH(S.DATE_)=2 AND TRCODE IN(7,8) THEN SUM(S.VATMATRAH) ELSE 0 END,0) AS SUBAT_SATIS,
    ISNULL(CASE WHEN MONTH(S.DATE_)=2 AND TRCODE IN(1,14) THEN SUM(S.VATMATRAH) ELSE 0 END,0) AS SUBAT_ALIS,
    ISNULL(CASE WHEN MONTH(S.DATE_)=3 AND TRCODE IN(7,8) THEN SUM(S.VATMATRAH) ELSE 0 END,0) AS MART_SATIS,
    ISNULL(CASE WHEN MONTH(S.DATE_)=3 AND TRCODE IN(1,14) THEN SUM(S.VATMATRAH) ELSE 0 END,0) AS MART_ALIS,
    ISNULL(CASE WHEN MONTH(S.DATE_)=4 AND TRCODE IN(7,8) THEN SUM(S.VATMATRAH) ELSE 0 END,0) AS NISAN_SATIS,
    ISNULL(CASE WHEN MONTH(S.DATE_)=4 AND TRCODE IN(1,14) THEN SUM(S.VATMATRAH) ELSE 0 END,0) AS NISAN_ALIS,
    ISNULL(CASE WHEN MONTH(S.DATE_)=5 AND TRCODE IN(7,8) THEN SUM(S.VATMATRAH) ELSE 0 END,0) AS MAYIS_SATIS,
    ISNULL(CASE WHEN MONTH(S.DATE_)=5 AND TRCODE IN(1,14) THEN SUM(S.VATMATRAH) ELSE 0 END,0) AS MAYIS_ALIS,
    ISNULL(CASE WHEN MONTH(S.DATE_)=6 AND TRCODE IN(7,8) THEN SUM(S.VATMATRAH) ELSE 0 END,0) AS HAZIRAN_SATIS,
    ISNULL(CASE WHEN MONTH(S.DATE_)=6 AND TRCODE IN(1,14) THEN SUM(S.VATMATRAH) ELSE 0 END,0) AS HAZIRAN_ALIS,
    ISNULL(CASE WHEN MONTH(S.DATE_)=7 AND TRCODE IN(7,8) THEN SUM(S.VATMATRAH) ELSE 0 END,0) AS TEMMUZ_SATIS,
    ISNULL(CASE WHEN MONTH(S.DATE_)=7 AND TRCODE IN(1,14) THEN SUM(S.VATMATRAH) ELSE 0 END,0) AS TEMMUZ_ALIS,
    ISNULL(CASE WHEN MONTH(S.DATE_)=8 AND TRCODE IN(7,8) THEN SUM(S.VATMATRAH) ELSE 0 END,0) AS AGUSTOS_SATIS,
    ISNULL(CASE WHEN MONTH(S.DATE_)=8 AND TRCODE IN(1,14) THEN SUM(S.VATMATRAH) ELSE 0 END,0) AS AGUSTOS_ALIS,
    ISNULL(CASE WHEN MONTH(S.DATE_)=9 AND TRCODE IN(7,8) THEN SUM(S.VATMATRAH) ELSE 0 END,0) AS EYLUL_SATIS,
    ISNULL(CASE WHEN MONTH(S.DATE_)=9 AND TRCODE IN(1,14) THEN SUM(S.VATMATRAH) ELSE 0 END,0) AS EYLUL_ALIS,
    ISNULL(CASE WHEN MONTH(S.DATE_)=10 AND TRCODE IN(7,8) THEN SUM(S.VATMATRAH) ELSE 0 END,0) AS EKIM_SATIS,
    ISNULL(CASE WHEN MONTH(S.DATE_)=10 AND TRCODE IN(1,14) THEN SUM(S.VATMATRAH) ELSE 0 END,0) AS EKIM_ALIS,
    ISNULL(CASE WHEN MONTH(S.DATE_)=11 AND TRCODE IN(7,8) THEN SUM(S.VATMATRAH) ELSE 0 END,0) AS KASIM_SATIS,
    ISNULL(CASE WHEN MONTH(S.DATE_)=11 AND TRCODE IN(1,14) THEN SUM(S.VATMATRAH) ELSE 0 END,0) AS KASIM_ALIS,
    ISNULL(CASE WHEN MONTH(S.DATE_)=12 AND TRCODE IN(7,8) THEN SUM(S.VATMATRAH) ELSE 0 END,0) AS ARALIK_SATIS,
    ISNULL(CASE WHEN MONTH(S.DATE_)=12 AND TRCODE IN(1,14) THEN SUM(S.VATMATRAH) ELSE 0 END,0) AS ARALIK_ALIS,
(SELECT SPECODE FROM LG_115_ITEMS WHERE LOGICALREF=S.STOCKREF) AS SPECODE,
(SELECT SPECODE2 FROM LG_115_ITEMS WHERE LOGICALREF=S.STOCKREF) AS SPECODE2
from LG_115_01_STLINE S WHERE S.CANCELLED=0 AND S.TRCODE IN(1,7,8,14) AND
S.STOCKREF IN(SELECT LOGICALREF FROM LG_115_ITEMS WHERE SPECODE IN(SELECT SPECODE
FROM LG_115_SPECODES WHERE SPECODETYPE=1 AND SPETYP1=1)) GROUP BY S.STOCKREF,
MONTH(S.DATE_),S.TRCODE) AS YKN WHERE 1=1 GROUP BY YKN.URUN_KODU,YKN.URUN_ACIKLAMASI