Bankadan geçmesi gerekenleri SQL ile yakalamak...

[Tahirsalt]

Selamlar Arkadaşlar,

HSPKOD Fisno aciklama Borc Alacak
600.10 1 A Ltd. 6500
391.10 1 A Ltd. 1000
100.10 1 A Ltd. 7500

600.10 1 A Ltd. 6500
391.10 1 A Ltd. 1000
120.10 1 A Ltd. 7500

254.10 2 B Ltd. 6700
391.10 2 B Ltd. 1200
100.10 2 B Ltd. 7900

Önce derdimi muhasebe diliyle anlatayım: genel muhasebeyi bilenler bilir. Bankadan geçmesi gereken devletin açıkladığı "7000" TL sınırını aşan işlemleri yakalamaya çalışıyorum. Elimde üstteki gibi örnek bir tablo var.

Bu tabloda bankadan geçmesi gerekirken kasadan ödenmiş 2 fatura görüyorum. A Ltd. ve B Ltd. yapılan satışlardan "100.10" kapatılmış iki kayıt vergi cezası gerektiriyor. (Cezası Kanuna göre 2 x 1300= 2600 TL) Şimdi binlerce kayıt içinden bu kayıtları SQL sorgusu ile yakalamak istiyorum.

Şimdide yazılımcı arkadaşların diliyle yazmaya çalışayım: "Borc">7000 büyük olan "HSPKOD" 100% başlayalar ile "Alacak">6000 büyük olan "HSPKOD" 600% ile başlayalardan "Fisno" ve "aciklama" ları aynı olanların listelenmesini istiyorum.

Aşağıdaki gibi bu şartlara uyan HSPKOD "100%" olanlar listelense yeterli veya bunu yakalayabilecek bir sonuç...

HSPKOD Fisno aciklama Borc
100.10 1 A Ltd. 7500


2 günlük araştırma sonucunda üstteki sonuca yaklaşabilmiş değilim.


create table table1 (HSPKOD varchar(30), Fisno int, aciklama varchar(30), Borc varchar(30), Alacak varchar(30))
INSERT INTO table1 (HSPKOD, Fisno, aciklama, Borc, Alacak)
values
(600.10,1,'A Ltd.',Null,6500),
(391.10,1,'A Ltd.',Null,1000),
(100.10,1,'A Ltd.',7500,Null),

(600.10,1,'A Ltd.',Null,6500),
(391.10,1,'A Ltd.',Null,1000),
(120.10,1,'A Ltd.',7500,Null),

(254.10,2,'B Ltd.',Null,6700),
(391.10,2,'B Ltd.',Null,1200),
(100.10,2,'B Ltd.',7900,Null);



GROUP BY HAVING COUNT(*)>1 ile ilgili birşeyler buldum ama onuda başaramadım

Select Fisno, aciklama, COUNT(*) from table1
where (HSPKOD LIKE '1%' and Borc >7000) or
(HSPKOD LIKE '6%' and Alacak >6000)
GROUP BY Fisno, aciklama having COUNT(*) > 1

 

[Asri]

Tam anlamamak ile beraber, istediğiniz bu şekilde mi?

select * from table1 where ((Borc>7000 and HSPKOD like '100%') or (Alacak>6000 and HSPKOD like '600%')) and HSPKOD like '100%'

 

[Tahirsalt]

Merhaba, sonuca yaklaşıyoruz. Elimdeki bir şirketin sql datasında denediğimde istenmeyen sonuçlarıda getirdiğini gördüm.
Sebepleri şöyle:
1.şartımız 100% ile başlayanlar ile 600% ile başlayanların 'fisno' ve 'aciklama' aynı olanlarda işlem yapacağız. Örneğin aşağıdaki veride '100%' gelmemeli.('Aciklama' tutmuyor.)
(600.10,4,'B Ltd.',Null,6700),
(391.10,4,'B Ltd.',Null,1200),
(100.10,4,'Toplu Tahsilatlar',7900,Null)

2.şartımız 600% içermeyen bir kümede 100% gelmemeli.
(254.10,3,'B Ltd.',Null,6700),
(391.10,3,'B Ltd.',Null,1200),
(100.10,3,'B Ltd.',7900,Null),


create table table1 (HSPKOD varchar(30), Fisno int, aciklama varchar(30), Borc varchar(30), Alacak varchar(30))
INSERT INTO table1 (HSPKOD, Fisno, aciklama, Borc, Alacak)
values
(600.10,1,'A Ltd.',Null,6500),
(391.10,1,'A Ltd.',Null,1000),
(100.10,8,'A Ltd.',7500,Null), --Bu satır gelmemesi gerekir. Çünkü 600% Fisno ile 100% fisno aynı değil.

(600.10,2,'D Ltd.',Null,6999),
(391.10,2,'D Ltd.',Null,1000),
(120.10,2,'D Ltd.',7999,Null), --Bu satırda gelmemesi gerekir. Çünkü içinde "100%" ile başlayan satır yok

(254.10,3,'B Ltd.',Null,6700),
(391.10,3,'B Ltd.',Null,1200),
(100.10,3,'B Ltd.',7900,Null), --Bu satırda gelmemesi gerekir. "100%" var ancak "600%" ile başlayan yok.

(600.10,4,'B Ltd.',Null,6700),
(391.10,4,'B Ltd.',Null,1200),
(100.10,4,'Toplu Tahsilatlar',7900,Null), --Bu satırda gelmemesi gerekir."100%" ile başlayanın açıklaması "600%" ile başlayanın açıklamasına eşit değil.

(600.10,5,'B Ltd.',Null,6700),
(391.10,5,'B Ltd.',Null,1200),
(100.10,5,'B Ltd.',7900,Null); -- Sadece bu satır gelecek; "600%" ile başlayan kayıt var ve "alacak" rakamı "6000" den büyük, 100% ve 600% ün "Açıklama" ve "fis no"ları aynı. "100%" "Borc" rakamı 7000'den büyük.


İstediğimiz sonuç sadece tek satır gelmeli veya en azından bana 'fisno' sunu sonuç döndürsün ki gidip fişe bakayım.
(100.10,5,'B Ltd.',7900,Null);

Çok teşekkür ederim...

 

[Zeki Gürsoy]

- Aynı fiş numarasında (içinde ve farklı fat. no) birden fazla satış işlemi olabilir mi?
Ör:
Hs. - Fiş - Evrak no - Açıkl. - B - A
100 - 8 - 115 - A ltd - 7500
600 - 8 - 115 - A ltd ------ 6500
391 - 8 - 115 - A ltd ------ 1000
100 - 8 - 116 - A ltd - 7500
600 - 8 - 116 - A ltd ------ 6500
391 - 8 - 116 - A ltd ------ 1000

- Diğer bir konu da, satış türüne göre birden fazla 600 hesabı tek bir 100 hesabına bağlanabilir. Bu durumda 600% > 6000 yerine Sum(600%) > 6000 olmalı. Elbette ki Açıklama ve evrak no grup bazında.

 

[Tahirsalt]

Zeki Bey Merhaba,
- Haklısınız, aynı fişte ayırt etmek için 'Evrakno' da gruplanacak. Hani önce 'fisno' ile 'aciklama' SQL sorgusu çözüme kavuşursa, 'evrakno' yu eklerim diye düşünmekteydim.

- Diğer konuyu örnekle inceleyelim:
600.10,2,'D Ltd.',Null,4000),
(391.10,2,'D Ltd.',Null,800),
(600.10,2,'D Ltd.',Null,3000),
(391.10,2,'D Ltd.',Null,600),
(100.10,2,'Kasa Tahsilat.',8400,Null) bu örnekte aynı firmaya 2 farklı fatura satışı kasadan tahsil edilmiş olsun. Kasanın 'Aciklamasi' toplu işlemlerde muhtemelen 'Kasa tahsilat' şeklinde olacağından bu kaydı yakalamak ikinci bir sorgu gerektirecektir.
Öncelikle her faturanın kasa ile kapandığını varsayımına uygun sorguya ihtiyacım var. Teşekkür ederim.

 

[Zeki Gürsoy]

Kanaatimce şöyle olmalı:
- 600 hesapları evrak no bazında toplanarak sanal bir tablo oluşsun,
- Sanal tablodaki evrak no ile, 100 hesabının geçtiği satırın evrak no ile karşılaşsın (100 hesap hareketi >7000 dir) eşleşiyorsa yakalanmış demektir.
Yevmiye satırında 100 hesabının olduğu satırda da evrak no yazıldığını umuyoruz.

Dikkat edilirse aynı tablo, takma isimlerle (alias) kendi içinde kontrol ediliyor. Deneme imkanım olmadığı için başarılı olacağını tahmin ediyorum.

Buna göre sorgu aşağıdaki gibi olmalıdır:

[SIZE=2]select main.hesapno, main.fisno, main.evrakno, main.aciklama, main.Borc
from tablo1 main
inner join
    ([COLOR=DarkGreen] /* Derived Table */[/COLOR]
      select satis.evrakno, Sum(Alacak) as [ToplamAlacak]
      from tablo1 satis
      where satis.hesapno like '600%'
      group by satis.evrakno
      [COLOR=DarkGreen]/* Para büyüklüğü Kasa hesabında filitre edildiğinden
         aşağıdaki satıra gerek yoktur.
      having Sum(satis.Alacak) > 6000
      */[/COLOR]
    ) as drv
on main.evrakno = drv.evrakno
where main.hesapno like '100%' And main.Borc > 7000[/SIZE]

 

[Tahirsalt]

Zeki Bey emeğiniz için teşekkür ederim. Tabloya 'evrakno' da ekledim ancak kodunuzu çalıştıramadım.

create table table1 (HSPKOD varchar(30), Fisno int, Evrakno int, aciklama varchar(30), Borc varchar(30), Alacak varchar(30))
INSERT INTO table1 (HSPKOD, Fisno, Evrakno, aciklama, Borc, Alacak)
values
(600.10,1,123,'A Ltd.',Null,6500),
(391.10,1,123,'A Ltd.',Null,1000),
(100.10,8,456,'A Ltd.',7500,Null), --Bu satır gelmemesi gerekir. Çünkü 600% Fisno ile 100% fisno aynı değil.

(600.10,2,221,'D Ltd.',Null,6999),
(391.10,2,221,'D Ltd.',Null,1000),
(120.10,2,221,'D Ltd.',7999,Null), --Bu satırda gelmemesi gerekir. Çünkü içinde "100%" ile başlayan satır yok

(254.10,3,121,'B Ltd.',Null,6700),
(391.10,3,121,'B Ltd.',Null,1200),
(100.10,3,121,'B Ltd.',7900,Null), --Bu satırda gelmemesi gerekir. "100%" var ancak "600%" ile başlayan yok.

(600.10,4,456,'B Ltd.',Null,6700),
(391.10,4,456,'B Ltd.',Null,1200),
(100.10,4,456,'Toplu Tahsilatlar',7900,Null), --Bu satırda gelmemesi gerekir."100%" ile başlayanın açıklaması "600%" ile başlayanın açıklamasına eşit değil.

(600.10,5,554,'B Ltd.',Null,6700),
(391.10,5,554,'B Ltd.',Null,1200),
(100.10,5,554,'B Ltd.',7900,Null); -- Sadece bu satır gelecek; "600%" ile başlayan kayıt var ve "alacak" rakamı "6000" den büyük, 100% ve 600% ün "Açıklama" ve "fis no"ları aynı. "100%" "Borc" rakamı 7000'den büyük.

 

[Zeki Gürsoy]

Örnek scriptinizde sanki tutarsızlık var gibi...
"456" numaralı satış faturasına ait yevmiye satırları aşağıdaki resimdeki gibi.
Biraz zaman ayırıp tutarlı örnek verin ki, akıntıya karşı kürek çekmemiş olalım. (Kayıt yanlış atılabiliyorsa o başka...)
Bundan başka 554 numaralı fatura dönmektedir. Sorgu başarılı diyebiliriz.

.

 

[Tahirsalt]

Cevap veren arkadaşlara çok teşekkür ederim. Bir arkadaşım kodları paylaştı bende burada paylaşayım. Sanırım Zeki beyin yöntemi...

SELECT Fisno FROM table1 A WHERE A.HSPKOD LIKE '100%' AND A.Borc > 7000
AND EXISTS
(SELECT Fisno FROM table1 B WHERE A.Fisno = B.Fisno AND A.aciklama=B.aciklama AND B.HSPKOD LIKE '600%' AND B.Alacak > 6000)

GROUP BY ve HAVINGlede uğraşılsa yapılır ancak o komutlarda arka planda bu işlemi yapacak.
Aslında bu sorgu GROUP BY lı olandan çok daha hızlı çalışır. Direk hedef kayda yöneliyor. GROUP BY'lı olan ise önce tüm tabloyu gerekmediği halde grupluyor, sonra hedefe yöneliyor.