KAR ZARAR RAPORU

==========================================

SELECT CONVERT(NVARCHAR,SH.TARIH,104) AS TARIH,S.STOKKODU,S.STOKADI,SUM(SH.MIKTAR) AS MIKTAR,ROUND(SUM(SH.ARATOPLAM),2) AS ARATOPLAM,ROUND(SUM(SH.KDVTOPLAMI),2) AS KDVTOPLAMI,ROUND(SUM(SH.GENELTOPLAM),2) AS GENELTOPLAM
,ROUND(((SELECT TOP 1 SFL.FIYAT FROM STOK_FIYAT_LISTELERI SFL WHERE SFL.TIPI=0 AND SFL.STOKKODU=S.STOKKODU ORDER BY SFL.ID DESC)/(1+(SH.KDVORANI/100))),2) AS ALISFIYATI
,((SELECT TOP 1 SFL.FIYAT FROM STOK_FIYAT_LISTELERI SFL WHERE SFL.TIPI=0 AND SFL.STOKKODU=S.STOKKODU ORDER BY SFL.ID DESC)) AS KDVDAHILALISFIYATI
,ROUND(SUM(SH.ARATOPLAM)/SUM(SH.MIKTAR),2) AS ORTALAMASATISFIYATI
,ROUND((SUM(SH.MIKTAR)*((SELECT TOP 1 SFL.FIYAT FROM STOK_FIYAT_LISTELERI SFL WHERE SFL.TIPI=0 AND SFL.STOKKODU=S.STOKKODU ORDER BY SFL.ID DESC)/(1+(SH.KDVORANI/100)))),2) AS ALISFIYATLARITOPLAMI
,ROUND((SUM(SH.ARATOPLAM) – (SUM(SH.MIKTAR)*((SELECT TOP 1 SFL.FIYAT FROM STOK_FIYAT_LISTELERI SFL WHERE SFL.TIPI=0 AND SFL.STOKKODU=S.STOKKODU ORDER BY SFL.ID DESC)/(1+(SH.KDVORANI/100))))),2) AS ARATOPLAMDANKAR
,ROUND((SUM(SH.GENELTOPLAM) – ((SUM(SH.MIKTAR)*((SELECT TOP 1 SFL.FIYAT FROM STOK_FIYAT_LISTELERI SFL WHERE SFL.TIPI=0 AND SFL.STOKKODU=S.STOKKODU ORDER BY SFL.ID DESC)/(1+(SH.KDVORANI/100)))))*(1+(SH.KDVORANI/100))),2) AS GENELTOPLAMDANKAR
 FROM STOKHAR SH,STOK S WHERE SH.STOKKODU=S.STOKKODU AND SH.EVRAKTIPI=’FATURA’ AND SH.TIPI=’SATIS’
 GROUP BY SH.TARIH,S.STOKKODU,S.STOKADI,SH.KDVORANI ORDER BY CONVERT(NVARCHAR,SH.TARIH,104) ASC
===========================================================
GÜNLÜK ENVANTER RAPORU
===========================================================
SELECT
S.STOKKODU,S.STOKADI,
dbo.DepodakiMiktar(S.STOKKODU,0,GETDATE()+1) AS MIKTAR,
S.STOKSEVIYESIBIRIMI,
(SELECT TOP 1 SFL.FIYAT FROM STOK_FIYAT_LISTELERI SFL WHERE SFL.TIPI=0 AND SFL.STOKKODU=S.STOKKODU ORDER BY SFL.ID DESC) AS ALISFIYATI,
(dbo.DepodakiMiktar(S.STOKKODU,0,GETDATE()+1)*(SELECT TOP 1 SFL.FIYAT FROM STOK_FIYAT_LISTELERI SFL WHERE SFL.TIPI=0 AND SFL.STOKKODU=S.STOKKODU ORDER BY SFL.ID DESC)) AS TOPLAM,
(dbo.DepodakiMiktar(S.STOKKODU,0,GETDATE()+1)*(SELECT TOP 1 SFL.FIYAT FROM STOK_FIYAT_LISTELERI SFL WHERE SFL.TIPI=1 AND SFL.STOKKODU=S.STOKKODU ORDER BY SFL.ID DESC)) AS SATISTOPLAM
FROM STOK S ORDER BY S.STOKKODU ASC
==========================================================
YILLARA GÖRE CİRO RAPORU
SELECT M.MUSTERIKODU,M.UNVAN,M.VERGIDAIRESI,M.VERGINUMARASI,M.ADRES,M.PLASIYERKODU
,ALACAK2018=ROUND(ISNULL((select sum (CH.ALACAK) FROM CARIHAREKETLER CH WHERE CH.MUSTERIKODU=M.MUSTERIKODU AND YEAR(CH.TARIH)=2018 AND CH.HAREKETTURU=’A’ AND CH.IPTAL=0),0),2)
,BORC2018=ROUND(ISNULL((select sum (CH.BORC) FROM CARIHAREKETLER CH WHERE CH.MUSTERIKODU=M.MUSTERIKODU AND YEAR(CH.TARIH)=2018 AND CH.HAREKETTURU=’B’ AND CH.IPTAL=0),0),2)
,BAKIYE2018=ROUND(ISNULL( (SELECT SUM(CH.BORC-CH.ALACAK) FROM CARIHAREKETLER CH WHERE M.MUSTERIKODU=CH.MUSTERIKODU AND YEAR(CH.TARIH)=2018),0),2)
,ALACAK2019=ROUND(ISNULL((select sum (CH.ALACAK) FROM CARIHAREKETLER CH WHERE CH.MUSTERIKODU=M.MUSTERIKODU AND YEAR(CH.TARIH)=2019 AND CH.HAREKETTURU=’A’ AND CH.IPTAL=0),0),2)
,BORC2019=ROUND(ISNULL((select sum (CH.BORC) FROM CARIHAREKETLER CH WHERE CH.MUSTERIKODU=M.MUSTERIKODU AND YEAR(CH.TARIH)=2019 AND CH.HAREKETTURU=’B’ AND CH.IPTAL=0),0),2)
,BAKIYE2019=ROUND(ISNULL( (SELECT SUM(CH.BORC-CH.ALACAK) FROM CARIHAREKETLER CH WHERE M.MUSTERIKODU=CH.MUSTERIKODU AND YEAR(CH.TARIH)=2019),0),2)
,ALACAK2020=ROUND(ISNULL((select sum (CH.ALACAK) FROM CARIHAREKETLER CH WHERE CH.MUSTERIKODU=M.MUSTERIKODU AND YEAR(CH.TARIH)=2020 AND CH.HAREKETTURU=’A’ AND CH.IPTAL=0),0),2)
,BORC2020=ROUND(ISNULL((select sum (CH.BORC) FROM CARIHAREKETLER CH WHERE CH.MUSTERIKODU=M.MUSTERIKODU AND YEAR(CH.TARIH)=2020 AND CH.HAREKETTURU=’B’ AND CH.IPTAL=0),0),2)
,BAKIYE2020=ROUND(ISNULL( (SELECT SUM(CH.BORC-CH.ALACAK) FROM CARIHAREKETLER CH WHERE M.MUSTERIKODU=CH.MUSTERIKODU AND YEAR(CH.TARIH)=2020),0),2)
,SONBAKIYE=ROUND(ISNULL( (SELECT SUM(CH.BORC-CH.ALACAK) FROM CARIHAREKETLER CH WHERE M.MUSTERIKODU=CH.MUSTERIKODU),0),2)
FROM MUSTERI M Order By UNVAN ASC
==========================================================
Perakende İşlem Yapan Kasalarda Verilerin Temizlenmesi
1. OLARAK BU İŞLEMLER YAPILIR
DELETE FROM STOKHAR
DELETE FROM STOKHAR2
DELETE FROM FATURAMASTER
DELETE FROM FATURAMASTER2
DELETE FROM IRSALIYEMASTER
DELETE FROM IRSALIYEMASTER2
DELETE FROM CARIHAREKETLER
DELETE FROM CARIHAREKETLER2
DELETE FROM KAYITLAR
DELETE FROM EVRAKBAGLAMA
DELETE FROM STOKHAR2_BEDEN_HAREKETLERI
DELETE FROM STOKHAR_BEDEN_HAREKETLERI
DELETE FROM LOGS
DELETE FROM FIYATDEGISIKLIKLERI
DELETE FROM SISTEMLOGLARI
DELETE FROM XZ_RAPORLARI
2. OLARAK SHRINK YAPILIR
Shrinkleme nasıl Yapılır sayfamızdan bakabilirsiniz.
İki tarih Arası Net Alıs ve Satıs Rakamları
SELECT SH.TARIH,SH.NORMALIADE,SH.EVRAKSERI,SH.EVRAKSIRA,SH.MUSTERIKODU,M.UNVAN,S.STOKKODU,S.STOKADI
,ALISMIKTAR=(CASE WHEN SH.GIRISCIKISTIPI=’GIRIS’ THEN ISNULL(SH.MIKTAR,0) ELSE 0 END)
,SATISMIKTAR=(CASE WHEN SH.GIRISCIKISTIPI=’CIKIS’ THEN ISNULL(SH.MIKTAR,0) ELSE 0 END)
,S.STOKSEVIYESIBIRIMI
,NETFIYATALIS=(CASE WHEN SH.GIRISCIKISTIPI=’GIRIS’ THEN ISNULL(SH.ARATOPLAM/SH.MIKTAR,0) ELSE 0 END)
,NETFIYATSATIS=(CASE WHEN SH.GIRISCIKISTIPI=’CIKIS’ THEN ISNULL(SH.ARATOPLAM/SH.MIKTAR,0) ELSE 0 END)
,D.DEPOADI
,DEPODAKIMIKTAR =dbo.DepodakiMiktar(S.STOKKODU,D.DEPOKODU,’2021-12-31 23:59:00.000′)
FROM STOKHAR SH,STOK S,DEPOLAR D,MUSTERI M WHERE SH.MUSTERIKODU=M.MUSTERIKODU AND SH.EVRAKTIPI IN (‘FATURA’,’IRSALIYE’) AND SH.STOKKODU=S.STOKKODU AND D.DEPOKODU=SH.DEPOKODU
AND SH.TARIH<‘2021-12-31 23:59:00.00′ AND SH.TARIH>=’2021-01-01 00:00:00.000’ AND D.DEPOKODU=0
GROUP BY S.STOKKODU,S.STOKADI,SH.GIRISCIKISTIPI,SH.MIKTAR,SH.TARIH,S.STOKSEVIYESIBIRIMI,D.DEPOADI,D.DEPOKODU,SH.ARATOPLAM,SH.EVRAKSERI,SH.EVRAKSIRA,SH.MUSTERIKODU,M.UNVAN,SH.NORMALIADE
ORDER BY SH.TARIH ASC
—-stok bazlı net Rakamlar
SELECT S.STOKKODU,S.STOKADI
,ALISMIKTAR=(SELECT SUM(ISNULL(SH.MIKTAR,0)) FROM STOKHAR SH WHERE SH.TARIH<‘2021-12-31 23:59:00.00′ AND SH.TARIH>=’2021-01-01 00:00:00.000′ AND SH.STOKKODU=S.STOKKODU AND SH.GIRISCIKISTIPI=’GIRIS’ AND SH.NORMALIADE=’NORMAL’ AND SH.EVRAKTIPI IN (‘FATURA’,’IRSALIYE’) AND D.DEPOKODU=SH.DEPOKODU )
,SATISMIKTAR=(SELECT SUM(ISNULL(SH.MIKTAR,0)) FROM STOKHAR SH WHERE SH.TARIH<‘2021-12-31 23:59:00.00′ AND SH.TARIH>=’2021-01-01 00:00:00.000′ AND SH.STOKKODU=S.STOKKODU AND SH.GIRISCIKISTIPI=’CIKIS’ AND SH.NORMALIADE=’NORMAL’ AND SH.EVRAKTIPI IN (‘FATURA’,’IRSALIYE’) AND D.DEPOKODU=SH.DEPOKODU )
,S.STOKSEVIYESIBIRIMI
,NETFIYATALIS=(SELECT SUM(ISNULL(SH.ARATOPLAM/SH.MIKTAR,0)) FROM STOKHAR SH WHERE SH.TARIH<‘2021-12-31 23:59:00.00′ AND SH.TARIH>=’2021-01-01 00:00:00.000′ AND SH.STOKKODU=S.STOKKODU AND SH.GIRISCIKISTIPI=’GIRIS’ AND SH.NORMALIADE=’NORMAL’ AND SH.EVRAKTIPI IN (‘FATURA’,’IRSALIYE’) AND D.DEPOKODU=SH.DEPOKODU )
,NETFIYATSATIS=(SELECT SUM(ISNULL(SH.ARATOPLAM/SH.MIKTAR,0)) FROM STOKHAR SH WHERE SH.TARIH<‘2021-12-31 23:59:00.00′ AND SH.TARIH>=’2021-01-01 00:00:00.000′ AND SH.STOKKODU=S.STOKKODU AND SH.GIRISCIKISTIPI=’CIKIS’ AND SH.NORMALIADE=’NORMAL’ AND SH.EVRAKTIPI IN (‘FATURA’,’IRSALIYE’) AND D.DEPOKODU=SH.DEPOKODU )
,D.DEPOADI
,DEPODAKIMIKTAR =dbo.DepodakiMiktar(S.STOKKODU,D.DEPOKODU,’2021-12-31 23:59:00.000′)
FROM STOK S,DEPOLAR D WHERE
D.DEPOKODU=0
GROUP BY S.STOKKODU,S.STOKADI,S.STOKSEVIYESIBIRIMI,D.DEPOADI,D.DEPOKODU
—merkez depodan alıslar ve dıger depolardan net satıslar toplamı

SELECT S.STOKKODU,S.STOKADI
,ALISMIKTAR=(SELECT ISNULL(SUM(SH.MIKTAR),0) FROM STOKHAR SH WHERE SH.TARIH<‘2021-12-31 23:59:00.00′ AND SH.TARIH>=’2021-01-01 00:00:00.000′ AND SH.STOKKODU=S.STOKKODU AND SH.GIRISCIKISTIPI=’GIRIS’ AND SH.NORMALIADE=’NORMAL’ AND SH.EVRAKTIPI IN (‘FATURA’,’IRSALIYE’) AND SH.DEPOKODU=0 )
,SATISMIKTAR=(SELECT ISNULL(SUM(SH.MIKTAR),0) FROM STOKHAR SH WHERE SH.TARIH<‘2021-12-31 23:59:00.00′ AND SH.TARIH>=’2021-01-01 00:00:00.000′ AND SH.STOKKODU=S.STOKKODU AND SH.GIRISCIKISTIPI=’CIKIS’ AND SH.NORMALIADE=’NORMAL’ AND SH.EVRAKTIPI IN (‘FATURA’,’IRSALIYE’) AND SH.DEPOKODU IN (0,1,2,3,4,5,6,7,8) )
,S.STOKSEVIYESIBIRIMI
,NETFIYATALIS=(SELECT ISNULL(SUM(SH.ARATOPLAM/SH.MIKTAR),0) FROM STOKHAR SH WHERE SH.TARIH<‘2021-12-31 23:59:00.00′ AND SH.TARIH>=’2021-01-01 00:00:00.000′ AND SH.STOKKODU=S.STOKKODU AND SH.GIRISCIKISTIPI=’GIRIS’ AND SH.NORMALIADE=’NORMAL’ AND SH.EVRAKTIPI IN (‘FATURA’,’IRSALIYE’) AND SH.DEPOKODU=0 )
,NETFIYATSATIS=(SELECT ISNULL(SUM(SH.ARATOPLAM/SH.MIKTAR),0) FROM STOKHAR SH WHERE SH.TARIH<‘2021-12-31 23:59:00.00′ AND SH.TARIH>=’2021-01-01 00:00:00.000′ AND SH.STOKKODU=S.STOKKODU AND SH.GIRISCIKISTIPI=’CIKIS’ AND SH.NORMALIADE=’NORMAL’ AND SH.EVRAKTIPI IN (‘FATURA’,’IRSALIYE’) AND SH.DEPOKODU IN (0,1,2,3,4,5,6,7,8) )
,DEPODAKIMIKTAR =dbo.DepodakiMiktar(S.STOKKODU,0,’2021-12-31 23:59:00.000′)
FROM STOK S WHERE 1=1

GROUP BY S.STOKKODU,S.STOKADI,S.STOKSEVIYESIBIRIMI
—kar oranı ıle bırlıkte

SELECT *,KARORANI=CASE WHEN T1.NETFIYATSATIS=0 THEN 0 ELSE ROUND( (1-(T1.NETFIYATALIS/T1.NETFIYATSATIS))*100,2) END
FROM (
SELECT S.STOKKODU,S.STOKADI
,ALISMIKTAR=(SELECT ISNULL(SUM(SH.MIKTAR),0) FROM STOKHAR SH WHERE SH.TARIH<‘2021-12-31 23:59:00.00′ AND SH.TARIH>=’2021-01-01 00:00:00.000′ AND SH.STOKKODU=S.STOKKODU AND SH.GIRISCIKISTIPI=’GIRIS’ AND SH.NORMALIADE=’NORMAL’ AND SH.EVRAKTIPI IN (‘FATURA’,’IRSALIYE’) AND SH.DEPOKODU=0 )
,SATISMIKTAR=(SELECT ISNULL(SUM(SH.MIKTAR),0) FROM STOKHAR SH WHERE SH.TARIH<‘2021-12-31 23:59:00.00′ AND SH.TARIH>=’2021-01-01 00:00:00.000′ AND SH.STOKKODU=S.STOKKODU AND SH.GIRISCIKISTIPI=’CIKIS’ AND SH.NORMALIADE=’NORMAL’ AND SH.EVRAKTIPI IN (‘FATURA’,’IRSALIYE’) AND SH.DEPOKODU IN (0,1,2,3,4,5,6,7,8) )
,S.STOKSEVIYESIBIRIMI
,NETFIYATALIS=(SELECT ISNULL(SUM(SH.ARATOPLAM)/SUM(SH.MIKTAR),0) FROM STOKHAR SH WHERE SH.TARIH<‘2021-12-31 23:59:00.00′ AND SH.TARIH>=’2021-01-01 00:00:00.000′ AND SH.STOKKODU=S.STOKKODU AND SH.GIRISCIKISTIPI=’GIRIS’ AND SH.NORMALIADE=’NORMAL’ AND SH.EVRAKTIPI IN (‘FATURA’,’IRSALIYE’) AND SH.DEPOKODU=0 )
,NETFIYATSATIS=(SELECT ISNULL(SUM(SH.ARATOPLAM)/SUM(SH.MIKTAR),0) FROM STOKHAR SH WHERE SH.TARIH<‘2021-12-31 23:59:00.00′ AND SH.TARIH>=’2021-01-01 00:00:00.000′ AND SH.STOKKODU=S.STOKKODU AND SH.GIRISCIKISTIPI=’CIKIS’ AND SH.NORMALIADE=’NORMAL’ AND SH.EVRAKTIPI IN (‘FATURA’,’IRSALIYE’) AND SH.DEPOKODU IN (0,1,2,3,4,5,6,7,8) )
,DEPODAKIMIKTAR =dbo.DepodakiMiktar(S.STOKKODU,0,’2021-12-31 23:59:00.000′)
FROM STOK S WHERE 1=1
–AND S.STOKKODU=’110401′
GROUP BY S.STOKKODU,S.STOKADI,S.STOKSEVIYESIBIRIMI
) T1