Serbest Raporlar SQL 3

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