Satış Analizi - Hangi cariye hangi stok ile ne kadara satış yapıldı raporu

CREATE VIEW SATISANALIZ

AS

SELECT 

SH.STHAR_CARIKOD AS CARI_KODU,

CS.CARI_ISIM,

SH.STOK_KODU,

ST.STOK_ADI,

SH.STHAR_BF AS BRM_BRUTFIYAT,

SH.STHAR_NF AS BRM_NETFIYAT,

(CASE WHEN STHAR_FTIRSIP='1' AND STHAR_HTUR IN ('I','J','K') THEN STHAR_GCMIK ELSE 0 END) AS  SAT_MIKTAR,

(CASE WHEN STHAR_FTIRSIP='1' AND STHAR_HTUR IN ('I','J','K') THEN STHAR_GCMIK ELSE 0 END) * SH.STHAR_BF AS SAT_BRUTTUT,

(CASE WHEN STHAR_FTIRSIP='1' AND STHAR_HTUR IN ('I','J','K') THEN STHAR_GCMIK ELSE 0 END) * SH.STHAR_NF AS SAT_NETTUT,

(CASE WHEN STHAR_HTUR='L' AND STHAR_FTIRSIP='2' THEN STHAR_GCMIK ELSE 0 END) AS SAT_IADE_MIKTAR,

(CASE WHEN STHAR_HTUR='L' AND STHAR_FTIRSIP='2' THEN STHAR_GCMIK ELSE 0 END) * SH.STHAR_BF AS SATIADE_BRUTTUT,

(CASE WHEN STHAR_HTUR='L' AND STHAR_FTIRSIP='2' THEN STHAR_GCMIK ELSE 0 END) * SH.STHAR_NF AS SATIADE_NETTUT,

((CASE WHEN STHAR_FTIRSIP='1' AND STHAR_HTUR IN ('I','J','K') THEN STHAR_GCMIK ELSE 0 END) -

(CASE WHEN STHAR_HTUR='L' AND STHAR_FTIRSIP='2' THEN STHAR_GCMIK ELSE 0 END)) AS IADESIZ_SATMIK,

((CASE WHEN STHAR_HTUR='J' THEN STHAR_GCMIK ELSE 0 END) -

(CASE WHEN STHAR_HTUR='L' AND STHAR_FTIRSIP='2'THEN STHAR_GCMIK ELSE 0 END)) * SH.STHAR_BF AS IADESIZ_SATBFTUT,

((CASE WHEN STHAR_HTUR='J' THEN STHAR_GCMIK ELSE 0 END) -

(CASE WHEN STHAR_HTUR='L' AND STHAR_FTIRSIP='2' THEN STHAR_GCMIK ELSE 0 END)) * SH.STHAR_NF AS IADESIZ_SATNFTUT,

(CASE WHEN STHAR_FTIRSIP='2' AND STHAR_HTUR IN ('I','J','K') THEN STHAR_GCMIK ELSE 0 END) AS ALIS_MIKTAR,

(CASE WHEN STHAR_FTIRSIP='2' AND STHAR_HTUR IN ('I','J','K') THEN STHAR_GCMIK ELSE 0 END) * SH.STHAR_BF AS ALIS_BRUTTUT,

(CASE WHEN STHAR_FTIRSIP='2' AND STHAR_HTUR IN ('I','J','K') THEN STHAR_GCMIK ELSE 0 END) * SH.STHAR_NF AS ALIS_NETTUT,

(CASE WHEN STHAR_HTUR='L' AND STHAR_FTIRSIP='1' THEN STHAR_GCMIK ELSE 0 END) AS ALISIADE_MIKTAR,

(CASE WHEN STHAR_HTUR='L' AND STHAR_FTIRSIP='1' THEN STHAR_GCMIK ELSE 0 END) * SH.STHAR_BF AS ALISIADE_BRUTTUT,

(CASE WHEN STHAR_HTUR='L' AND STHAR_FTIRSIP='1' THEN STHAR_GCMIK ELSE 0 END) * SH.STHAR_NF AS ALISIADE_NETTUT,

((CASE WHEN STHAR_FTIRSIP='2' AND STHAR_HTUR IN ('I','J','K') THEN STHAR_GCMIK ELSE 0 END) -

(CASE WHEN STHAR_HTUR='L' AND SH.STHAR_FTIRSIP='1' THEN STHAR_GCMIK ELSE 0 END)) AS IADESIZ_ALSMIK,

((CASE WHEN STHAR_FTIRSIP='2' AND STHAR_HTUR IN ('I','J','K') THEN STHAR_GCMIK ELSE 0 END) -

(CASE WHEN STHAR_HTUR='L' AND SH.STHAR_FTIRSIP='1' THEN STHAR_GCMIK ELSE 0 END)) * SH.STHAR_NF AS IADESIZ_ALSNFTUT,

((CASE WHEN STHAR_FTIRSIP='2' AND STHAR_HTUR IN ('I','J','K') THEN STHAR_GCMIK ELSE 0 END) -

(CASE WHEN STHAR_HTUR='L' AND SH.STHAR_FTIRSIP='1' THEN STHAR_GCMIK ELSE 0 END)) * SH.STHAR_BF AS IADESIZ_ALSBFTUT,

SH.STHAR_TARIH AS TARIH,

ST.GRUP_KODU,

GR.GRUP_ISIM AS GRUP_ACIKLAMA,

ST.KOD_1,

S1.GRUP_ISIM AS KOD1_ACIKLAMA,

ST.KOD_2,

S2.GRUP_ISIM AS KOD2_ACIKLAMA,

ST.KOD_3,

S3.GRUP_ISIM AS KOD3_ACIKLAMA,

ST.KOD_4,

S4.GRUP_ISIM AS KOD4_ACIKLAMA,

ST.KOD_5,

S5.GRUP_ISIM AS KOD5_ACIKLAMA

FROM TBLSTHAR AS SH

LEFT OUTER JOIN TBLSTSABIT AS ST ON (SH.STOK_KODU = ST.STOK_KODU)

LEFT OUTER JOIN TBLCASABIT AS CS ON (SH.STHAR_CARIKOD = CS.CARI_KOD)

LEFT OUTER JOIN TBLSTGRUP AS GR ON (ST.GRUP_KODU = GR.GRUP_KOD)

LEFT OUTER JOIN TBLSTOKKOD1 AS S1 ON (ST.KOD_1 = S1.GRUP_KOD)

LEFT OUTER JOIN TBLSTOKKOD2 AS S2 ON (ST.KOD_2 = S2.GRUP_KOD)

LEFT OUTER JOIN TBLSTOKKOD3 AS S3 ON (ST.KOD_3 = S3.GRUP_KOD)

LEFT OUTER JOIN TBLSTOKKOD4 AS S4 ON (ST.KOD_4 = S4.GRUP_KOD)

LEFT OUTER JOIN TBLSTOKKOD5 AS S5 ON (ST.KOD_5 = S5.GRUP_KOD)