Aylık Bazda gelir ve Giderler
SQL
SELECT * FROM (
SELECT ‘OCAK’ AS AY,’0001′ AS AYNO,ROUND(ISNULL(SUM(CH.ALACAK),0),2) AS GIDERALACAK,ROUND(ISNULL(SUM(CH.BORC),0),2) AS GIDERBORC,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=1 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’A’),0),2) AS ALISFATURA,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=1 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’B’),0),2) AS SATISFATURA,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=1 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’A’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS TAHSILAT,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=1 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’B’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS ÖDEME FROM CARIHAREKETLER CH,MUSTERI M WHERE M.MUSTERIKODU=CH.MUSTERIKODU AND M.TIPI IN (‘GIDER’,’PERSONEL’) AND MONTH(CH.TARIH)=1 AND YEAR(CH.TARIH)=YEAR(GETDATE()) AND CH.IPTAL=0 AND CH.ACIKLAMA<>’DEVIR’
UNION
SELECT ‘ŞUBAT’ AS AY,’0002′ AS AYNO,ROUND(ISNULL(SUM(CH.ALACAK),0),2) AS GIDERALACAK,ROUND(ISNULL(SUM(CH.BORC),0),2) AS GIDERBORC,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=2 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’A’),0),2) AS ALISFATURA,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=2 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’B’),0),2) AS SATISFATURA,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=2 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’A’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS TAHSILAT,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=2 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’B’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS ÖDEME FROM CARIHAREKETLER CH,MUSTERI M WHERE M.MUSTERIKODU=CH.MUSTERIKODU AND M.TIPI IN (‘GIDER’,’PERSONEL’) AND MONTH(CH.TARIH)=2 AND YEAR(CH.TARIH)=YEAR(GETDATE()) AND CH.IPTAL=0 AND CH.ACIKLAMA<>’DEVIR’
UNION
SELECT ‘MART’ AS AY,’0003′ AS AYNO,ROUND(ISNULL(SUM(CH.ALACAK),0),2) AS GIDERALACAK,ROUND(ISNULL(SUM(CH.BORC),0),2) AS GIDERBORC,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=3 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’A’),0),2) AS ALISFATURA,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=3 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’B’),0),2) AS SATISFATURA,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=3 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’A’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS TAHSILAT,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=3 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’B’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS ÖDEME FROM CARIHAREKETLER CH,MUSTERI M WHERE M.MUSTERIKODU=CH.MUSTERIKODU AND M.TIPI IN (‘GIDER’,’PERSONEL’) AND MONTH(CH.TARIH)=3 AND YEAR(CH.TARIH)=YEAR(GETDATE()) AND CH.IPTAL=0 AND CH.ACIKLAMA<>’DEVIR’
UNION
SELECT ‘NİSAN’ AS AY,’0004′ AS AYNO,ROUND(ISNULL(SUM(CH.ALACAK),0),2) AS GIDERALACAK,ROUND(ISNULL(SUM(CH.BORC),0),2) AS GIDERBORC,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=4 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’A’),0),2) AS ALISFATURA,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=4 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’B’),0),2) AS SATISFATURA,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=4 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’A’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS TAHSILAT,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=4 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’B’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS ÖDEME FROM CARIHAREKETLER CH,MUSTERI M WHERE M.MUSTERIKODU=CH.MUSTERIKODU AND M.TIPI IN (‘GIDER’,’PERSONEL’) AND MONTH(CH.TARIH)=4 AND YEAR(CH.TARIH)=YEAR(GETDATE()) AND CH.IPTAL=0 AND CH.ACIKLAMA<>’DEVIR’
UNION
SELECT ‘MAYIS’ AS AY,’0005′ AS AYNO,ROUND(ISNULL(SUM(CH.ALACAK),0),2) AS GIDERALACAK,ROUND(ISNULL(SUM(CH.BORC),0),2) AS GIDERBORC,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=5 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’A’),0),2) AS ALISFATURA,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=5 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’B’),0),2) AS SATISFATURA,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=5 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’A’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS TAHSILAT,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=5 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’B’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS ÖDEME FROM CARIHAREKETLER CH,MUSTERI M WHERE M.MUSTERIKODU=CH.MUSTERIKODU AND M.TIPI IN (‘GIDER’,’PERSONEL’) AND MONTH(CH.TARIH)=5 AND YEAR(CH.TARIH)=YEAR(GETDATE()) AND CH.IPTAL=0 AND CH.ACIKLAMA<>’DEVIR’
UNION
SELECT ‘HAZİRAN’ AS AY,’0006′ AS AYNO,ROUND(ISNULL(SUM(CH.ALACAK),0),2) AS GIDERALACAK,ROUND(ISNULL(SUM(CH.BORC),0),2) AS GIDERBORC,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=6 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’A’),0),2) AS ALISFATURA,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=6 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’B’),0),2) AS SATISFATURA,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=6 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’A’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS TAHSILAT,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=6 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’B’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS ÖDEME FROM CARIHAREKETLER CH,MUSTERI M WHERE M.MUSTERIKODU=CH.MUSTERIKODU AND M.TIPI IN (‘GIDER’,’PERSONEL’) AND MONTH(CH.TARIH)=6 AND YEAR(CH.TARIH)=YEAR(GETDATE()) AND CH.IPTAL=0 AND CH.ACIKLAMA<>’DEVIR’
UNION
SELECT ‘TEMMUZ’ AS AY,’0007′ AS AYNO,ROUND(ISNULL(SUM(CH.ALACAK),0),2) AS GIDERALACAK,ROUND(ISNULL(SUM(CH.BORC),0),2) AS GIDERBORC,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=7 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’A’),0),2) AS ALISFATURA,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=7 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’B’),0),2) AS SATISFATURA,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=7 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’A’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS TAHSILAT,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=7 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’B’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS ÖDEME FROM CARIHAREKETLER CH,MUSTERI M WHERE M.MUSTERIKODU=CH.MUSTERIKODU AND M.TIPI IN (‘GIDER’,’PERSONEL’) AND MONTH(CH.TARIH)=7 AND YEAR(CH.TARIH)=YEAR(GETDATE()) AND CH.IPTAL=0 AND CH.ACIKLAMA<>’DEVIR’
UNION
SELECT ‘AĞUSTOS’ AS AY,’0008′ AS AYNO,ROUND(ISNULL(SUM(CH.ALACAK),0),2) AS GIDERALACAK,ROUND(ISNULL(SUM(CH.BORC),0),2) AS GIDERBORC,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=8 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’A’),0),2) AS ALISFATURA,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=8 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’B’),0),2) AS SATISFATURA,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=8 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’A’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS TAHSILAT,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=8 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’B’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS ÖDEME FROM CARIHAREKETLER CH,MUSTERI M WHERE M.MUSTERIKODU=CH.MUSTERIKODU AND M.TIPI IN (‘GIDER’,’PERSONEL’) AND MONTH(CH.TARIH)=8 AND YEAR(CH.TARIH)=YEAR(GETDATE()) AND CH.IPTAL=0 AND CH.ACIKLAMA<>’DEVIR’
UNION
SELECT ‘EYLÜL’ AS AY,’0009′ AS AYNO,ROUND(ISNULL(SUM(CH.ALACAK),0),2) AS GIDERALACAK,ROUND(ISNULL(SUM(CH.BORC),0),2) AS GIDERBORC,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=9 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’A’),0),2) AS ALISFATURA,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=9 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’B’),0),2) AS SATISFATURA,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=9 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’A’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS TAHSILAT,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=9 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’B’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS ÖDEME FROM CARIHAREKETLER CH,MUSTERI M WHERE M.MUSTERIKODU=CH.MUSTERIKODU AND M.TIPI IN (‘GIDER’,’PERSONEL’) AND MONTH(CH.TARIH)=9 AND YEAR(CH.TARIH)=YEAR(GETDATE()) AND CH.IPTAL=0 AND CH.ACIKLAMA<>’DEVIR’
UNION
SELECT ‘EKİM’ AS AY,’0010′ AS AYNO,ROUND(ISNULL(SUM(CH.ALACAK),0),2) AS GIDERALACAK,ROUND(ISNULL(SUM(CH.BORC),0),2) AS GIDERBORC,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=10 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’A’),0),2) AS ALISFATURA,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=10 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’B’),0),2) AS SATISFATURA,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=10 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’A’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS TAHSILAT,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=10 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’B’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS ÖDEME FROM CARIHAREKETLER CH,MUSTERI M WHERE M.MUSTERIKODU=CH.MUSTERIKODU AND M.TIPI IN (‘GIDER’,’PERSONEL’) AND MONTH(CH.TARIH)=10 AND YEAR(CH.TARIH)=YEAR(GETDATE()) AND CH.IPTAL=0 AND CH.ACIKLAMA<>’DEVIR’
UNION
SELECT ‘KASIM’ AS AY,’0011′ AS AYNO,ROUND(ISNULL(SUM(CH.ALACAK),0),2) AS GIDERALACAK,ROUND(ISNULL(SUM(CH.BORC),0),2) AS GIDERBORC,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=11 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’A’),0),2) AS ALISFATURA,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=11 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’B’),0),2) AS SATISFATURA,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=11 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’A’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS TAHSILAT,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=11 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’B’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS ÖDEME FROM CARIHAREKETLER CH,MUSTERI M WHERE M.MUSTERIKODU=CH.MUSTERIKODU AND M.TIPI IN (‘GIDER’,’PERSONEL’) AND MONTH(CH.TARIH)=11 AND YEAR(CH.TARIH)=YEAR(GETDATE()) AND CH.IPTAL=0 AND CH.ACIKLAMA<>’DEVIR’
UNION
SELECT ‘ARALIK’ AS AY,’0012′ AS AYNO,ROUND(ISNULL(SUM(CH.ALACAK),0),2) AS GIDERALACAK,ROUND(ISNULL(SUM(CH.BORC),0),2) AS GIDERBORC,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=12 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’A’),0),2) AS ALISFATURA,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=12 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’B’),0),2) AS SATISFATURA,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=12 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’A’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS TAHSILAT,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE MONTH(CH1.TARIH)=12 AND YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’B’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS ÖDEME FROM CARIHAREKETLER CH,MUSTERI M WHERE M.MUSTERIKODU=CH.MUSTERIKODU AND M.TIPI IN (‘GIDER’,’PERSONEL’) AND MONTH(CH.TARIH)=12 AND YEAR(CH.TARIH)=YEAR(GETDATE()) AND CH.IPTAL=0 AND CH.ACIKLAMA<>’DEVIR’
UNION
SELECT ‘NET’ AS AY,’0099′ AS AYNO,ROUND(ISNULL(SUM(CH.ALACAK),0),2) AS GIDERALACAK,ROUND(ISNULL(SUM(CH.BORC),0),2) AS GIDERBORC,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’A’),0),2) AS ALISFATURA,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’FATURA’ AND CH1.HAREKETTURU=’B’),0),2) AS SATISFATURA,ROUND(ISNULL((SELECT SUM(CH1.ALACAK) FROM CARIHAREKETLER CH1 WHERE YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’A’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS TAHSILAT,ROUND(ISNULL((SELECT SUM(CH1.BORC) FROM CARIHAREKETLER CH1 WHERE YEAR(CH1.TARIH)=YEAR(GETDATE()) AND CH1.IPTAL=0 AND CH1.EVRAKTIPI=’TAHSILAT’ AND CH1.HAREKETTURU=’B’ AND CH1.ACIKLAMA<>’DEVIR’),0),2) AS ÖDEME FROM CARIHAREKETLER CH,MUSTERI M WHERE M.MUSTERIKODU=CH.MUSTERIKODU AND M.TIPI IN (‘GIDER’,’PERSONEL’) AND YEAR(CH.TARIH)=YEAR(GETDATE()) AND CH.IPTAL=0 AND CH.ACIKLAMA<>’DEVIR’) T1 ORDER BY T1.AYNO ASC
———————————————————————————————————–
GIDERLER TOPLAMI
SQL:
SELECT ROUND(ISNULL(SUM(CH.BORC),0),2) AS SONUC FROM CARIHAREKETLER CH,MUSTERI M WHERE M.MUSTERIKODU=CH.MUSTERIKODU AND M.TIPI=’GIDER’ AND M.PLASIYERKODU=’0′ AND TARIH>=’2016-01-01′ AND TARIH<=’201631-12′ ORDER BY CH.TARIH ASC
————————————————————————————————————-
STOKKODU 2 İLE BİRBİRİNE BAGLI STOKLAR USERINDEN ANA BIRIM RAPORU
SQL:
SELECT S.STOKKODU,S.STOKADI,D.DEPOKODU,D.DEPOADI,ISNULL((dbo.DepodakiMiktar(S.STOKKODU,D.DEPOKODU,GETDATE())+ (SELECT dbo.DepodakiMiktar(S1.STOKKODU,D.DEPOKODU,GETDATE())*(B1.PAYDA/B1.PAY) FROM STOK S1,BIRIMLER B1 WHERE S.STOKKODU=B1.STOKKODU AND S1.STOKSEVIYESIBIRIMI=B1.BIRIMKODU AND S1.STOKKODU2=S.STOKKODU AND B1.BIRIMKODU<>B1.USTBIRIMKODU)),0) AS DEPODAKIMIKTAR,S.STOKSEVIYESIBIRIMI FROM STOK S,BIRIMLER B, DEPOLAR D WHERE S.STOKKODU=B.STOKKODU AND B.USTBIRIMKODU=S.STOKSEVIYESIBIRIMI AND S.STOKKODU2=” GROUP BY S.STOKKODU,S.STOKADI,D.DEPOKODU,D.DEPOADI,S.STOKSEVIYESIBIRIMI ORDER BY D.DEPOKODU ASC,S.STOKADI ASC
EXCEL EĞER KULLANIMI:
=EĞER(G2=”GIRIS”;E2;-E2)
——————————-
ÇOKLU BİRİMLİ DEPO STOK RAPORU
SELECT
S.STOKKODU,
S.STOKADI,
D.DEPOKODU,
D.DEPOADI,
B.BIRIMKODU,
B.SIRANO AS BIRIMSIRANO,
(CASE WHEN B.SIRANO=1 THEN (SUM(CASE WHEN SH.GIRISCIKISTIPI=’CIKIS’ THEN -1*SH.MIKTAR ELSE SH.MIKTAR END)) ELSE SUM(CASE WHEN SH.GIRISCIKISTIPI=’CIKIS’ THEN -1*SH.MIKTAR ELSE SH.MIKTAR END)*B.PAYDA/B.PAY END) AS MIKTAR
FROM STOK S,DEPOLAR D,STOKHAR SH,BIRIMLER B
WHERE S.STOKKODU=SH.STOKKODU AND D.DEPOKODU=SH.DEPOKODU AND S.STOKKODU=B.STOKKODU AND B.SIRANO=1
GROUP BY S.STOKKODU,S.STOKADI,D.DEPOKODU,D.DEPOADI,B.PAY,B.PAYDA,B.SIRANO,B.BIRIMKODU
S.STOKKODU,
S.STOKADI,
D.DEPOKODU,
D.DEPOADI,
B.BIRIMKODU,
B.SIRANO AS BIRIMSIRANO,
(CASE WHEN B.SIRANO=1 THEN (SUM(CASE WHEN SH.GIRISCIKISTIPI=’CIKIS’ THEN -1*SH.MIKTAR ELSE SH.MIKTAR END)) ELSE SUM(CASE WHEN SH.GIRISCIKISTIPI=’CIKIS’ THEN -1*SH.MIKTAR ELSE SH.MIKTAR END)*B.PAYDA/B.PAY END) AS MIKTAR
FROM STOK S,DEPOLAR D,STOKHAR SH,BIRIMLER B
WHERE S.STOKKODU=SH.STOKKODU AND D.DEPOKODU=SH.DEPOKODU AND S.STOKKODU=B.STOKKODU AND B.SIRANO=1
GROUP BY S.STOKKODU,S.STOKADI,D.DEPOKODU,D.DEPOADI,B.PAY,B.PAYDA,B.SIRANO,B.BIRIMKODU
UNION
SELECT
S.STOKKODU,
S.STOKADI,
D.DEPOKODU,
D.DEPOADI,
B.BIRIMKODU,
B.SIRANO AS BIRIMSIRANO,
(CASE WHEN B.SIRANO=2 THEN (SUM(CASE WHEN SH.GIRISCIKISTIPI=’CIKIS’ THEN -1*SH.MIKTAR ELSE SH.MIKTAR END)*B.PAY/B.PAYDA) ELSE SUM(CASE WHEN SH.GIRISCIKISTIPI=’CIKIS’ THEN -1*SH.MIKTAR ELSE SH.MIKTAR END)*B.PAYDA/B.PAY END) AS MIKTAR
FROM STOK S,DEPOLAR D,STOKHAR SH,BIRIMLER B
WHERE S.STOKKODU=SH.STOKKODU AND D.DEPOKODU=SH.DEPOKODU AND S.STOKKODU=B.STOKKODU AND B.SIRANO=2
GROUP BY S.STOKKODU,S.STOKADI,D.DEPOKODU,D.DEPOADI,B.PAY,B.PAYDA,B.SIRANO,B.BIRIMKODU