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.