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)
——————————-
Müşterilere Kaç Aydır Fatura Kesilmiyor.
SQL :
select *,DATEDIFF(MONTH,SONFATURATARIHI,GETDATE()) AS KESILMEYENAYSAYISI from (
SELECT
M.POSTAKODU,M.UNVAN,M.ADRES,M.IL,M.EMAIL,M.TELEFON,M.ILCE,M.PLASIYERKODU,M.MUSTERIKODU,convert(VARCHAR(10),
(SELECT TOP 1 CHF.TARIH FROM CARIHAREKETLER CHF
WHERE CHF.MUSTERIKODU=CH.MUSTERIKODU ORDER BY CHF.TARIH DESC),110) AS SONFATURATARIHI,
ROUND((SELECT SUM(B.BORC-B.ALACAK) FROM CARIHAREKETLER B WHERE B.MUSTERIKODU=CH.MUSTERIKODU),2) AS BAKIYE
FROM
MUSTERI M, CARIHAREKETLER CH WHERE CH.MUSTERIKODU=M.MUSTERIKODU
GROUP BY M.POSTAKODU,M.UNVAN,M.ADRES,M.IL,M.EMAIL,M.ILCE,M.PLASIYERKODU,M.MUSTERIKODU,CH.MUSTERIKODU,M.TELEFON
) T1 ORDER BY T1.UNVAN ASC
——————————————
Müşterilere Kaç Gündür Fatura Kesilmiyor
SQL :
select *,DATEDIFF(DAY,SONFATURATARIHI,GETDATE()) AS KESILMEYENGUNSAYISI from (
SELECT
M.POSTAKODU,M.UNVAN,M.ADRES,M.IL,M.EMAIL,M.TELEFON,M.ILCE,M.PLASIYERKODU,M.MUSTERIKODU,convert(VARCHAR(10),
(SELECT TOP 1 CHF.TARIH FROM CARIHAREKETLER CHF
WHERE CHF.MUSTERIKODU=CH.MUSTERIKODU ORDER BY CHF.TARIH DESC),110) AS SONFATURATARIHI,
ROUND((SELECT SUM(B.BORC-B.ALACAK) FROM CARIHAREKETLER B WHERE B.MUSTERIKODU=CH.MUSTERIKODU),2) AS BAKIYE
FROM
MUSTERI M, CARIHAREKETLER CH WHERE CH.MUSTERIKODU=M.MUSTERIKODU
GROUP BY M.POSTAKODU,M.UNVAN,M.ADRES,M.IL,M.EMAIL,M.ILCE,M.PLASIYERKODU,M.MUSTERIKODU,CH.MUSTERIKODU,M.TELEFON
) T1 ORDER BY T1.UNVAN ASC
———————————————