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
==========================================================
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.
====================================================
STOK ENVANTER DETAYLI

SET DATEFORMAT ymd SELECT
STOKHAR.DEPOKODU,
dbo.DepodakiMiktarZamanli(STOKHAR.STOKKODU,STOKHAR.DEPOKODU,0,DATEDIFF(SECOND,’1970-01-01′,’2019-05-02 11:44:06′)) AS MIKTAR,
STOKHAR.STOKKODU,
STOK.STOKADI,
STOK.STOKSEVIYESIBIRIMI,
SFL.FIYAT,
SFL.KDVDAHIL,
SFL.TIPI,
SFL.OZELKOD,
BIRIMLER.PAY,
BIRIMLER.PAYDA,
STOK.STOKSEVIYESI,
BIRIMLER.BARKOD,
(dbo.DepodakiMiktarZamanli(STOKHAR.STOKKODU,STOKHAR.DEPOKODU,0,DATEDIFF(SECOND,’1970-01-01′,GETDATE())))*(SFL.FIYAT) AS DEGER1
FROM STOKHAR,STOK,BIRIMLER,STOK_FIYAT_LISTELERI SFL
WHERE

BIRIMLER.STOKKODU=STOK.STOKKODU AND
SFL.BIRIMLER=BIRIMLER.BIRIMADI AND
BIRIMLER.BIRIMADI=SFL.BIRIMLER AND
SFL.STOKKODU=STOK.STOKKODU AND
–SFL.TIPI=0 AND
STOKHAR.STOKKODU=STOK.STOKKODU AND
TARIH<=GETDATE() AND
SFL.BASLANGICTARIHI<= GETDATE() AND SFL.BITISTARIHI>=GETDATE()

GROUP BY STOKHAR.STOKKODU,STOK.STOKADI,STOK.STOKSEVIYESIBIRIMI,BIRIMLER.PAYDA,STOK.STOKSEVIYESI,SFL.FIYAT,STOKHAR.DEPOKODU,BIRIMLER.BARKOD,SFL.TIPI,SFL.KDVDAHIL,BIRIMLER.PAY,SFL.OZELKOD ORDER BY STOKHAR.STOKKODU ASC,STOK.STOKADI ASC