Transform ve Pıvot Kullanılarak Bakiye Hesaplatma da Sorun

[F.Atagun]

Selamlar,
Hemen belirteyim Bakiye hesaplatma ile forumda yüzlerce soru ve cevap gördüm ama hiçbiri ile çözmedim. Berken arkaşımız calcfiled ile çözmüş ama ben bir aşamada takıldım.


Veritabanı şu şekilde

Kod: Tümünü seç

Kodu--BA---TUTAR
001------Borc---100
001------Borc----50
001------Alacak--20

Takıldıgım yer ( Access database'ini kullanıyoruz ) tutar fieldini TRANSFORM - PIVOT ile ayırıyorum. Borçları ve Alacakları farklı fielda aktarıyorum.

Sonuçta Gridde şu şekilde oluyor.

Kod: Tümünü seç

[b]Kodu--BA--------TUTAR------Borc------Alacak--[/b]
001------Borc-------100---------100---------0
001------Borc--------50----------50----------0
001------Alacak------20----------0-----------20

İşte bu aşamadan sonra bakiyeyi toplamak istiyorum ama Borc ve Alacak sütunları SQL cümlesinden geldiği için yazacagım SQL kodunda bu alanlar tanımlı değil mesajı ile karşılaşıyorum


Carihareket.BORC dersem yemiyor, Police1.Borc dersem yine yemiyor.
Bu sonradan oluşan Borc ve Alacak kayıtları üzeerinde işlem yapabilmek için ne yapabilirim ?






Çektiğim SQL kodu

Kod: Tümünü seç

 
 AdoQuery1.Close;
    AdoQuery1.SQL.clear;
    AdoQuery1.SQL.add('TRANSFORM sum(Format([CariHareket.Tutar],''Currency''))  as Tutaar');
    AdoQuery1.SQL.add('SELECT CariHareket.Musterikodu, CariHareket.Tarihi as TARIH, CariHareket.islemtipi as TR, '+' First(CariHareket.Evrakno) AS Evrakno, CariHareket.izahat, sum(Format([CariHareket.Tutar],''Currency'')) AS Tutar, CariHareket.BorcAlacak, '+' CariHareket.ParaBirimi as PB,   Police1.Plaka, Police1.RizikoAdresi, Police1.SigortaEttiren AS Sigortalı, Police1.GrupKodu, CariHareket.OzelKod1, '+'  CariHareket.OzelKod2, Police1.TanzimTarihi, Police1.BaslangicTarihi, Police1.BitisTarihi, CariHareket.BK, CariHareket.PN, CariKart.Unvani,  CariKart.Yetkili,CariKart.Tel1, CariKart.Tel2,'+'  CariHareket.PN, CariKart.Faks, CariKart.GrupKodu , CariKart.OzelKod1, CariKart.OzelKod2 ');
    AdoQuery1.SQL.add('FROM ((CariHareket LEFT JOIN Police1 ON (CariHareket.Musterikodu = Police1.MusteriKodu) AND '+'(CariHareket.PoliceSiraNo = Police1.SiraNo)) LEFT JOIN KrediKarti ON (CariHareket.Evrakno = KrediKarti.FormNo) and '+'(KrediKarti.TaksitVade = CariHareket.Vade) and (KrediKarti.TaksitTutari = CariHareket.Tutar)      ) '+' LEFT JOIN CariKart ON CariHareket.Musterikodu = CariKart.Musterikodu ');
    AdoQuery1.SQL.add('WHERE (CariHareket.Musterikodu like "'+edit1.text+'%") and (((CariHareket.DeleteUser)="" Or (CariHareket.DeleteUser) Is Null))     ');
    if combobox1.ItemIndex = 1 then  AdoQuery1.Sql.Add('   and  (CariHareket.ParaBirimi="YTL")    ');
    if combobox1.ItemIndex = 2 then  AdoQuery1.Sql.Add('   and  (CariHareket.ParaBirimi="USD")    ');
    if combobox1.ItemIndex = 3 then  AdoQuery1.Sql.Add('   and  (CariHareket.ParaBirimi="EURO")    ');
    AdoQuery1.SQL.add('GROUP BY CariHareket.ParaBirimi ,CariHareket.Tarihi , CariHareket.Musterikodu, CariHareket.islemtipi, '+'  CariHareket.izahat, CariHareket.BorcAlacak, CariHareket.ParaBirimi, Police1.Plaka, Police1.RizikoAdresi, Police1.SigortaEttiren, Police1.GrupKodu, '+'  CariHareket.OzelKod1, CariHareket.OzelKod2, Police1.TanzimTarihi, Police1.BaslangicTarihi, Police1.BitisTarihi, '+' CariHareket.BK, CariHareket.PN, CariHareket.DeleteUser,CariHareket.DeleteDate , CariKart.Unvani,  CariKart.Yetkili,CariKart.Tel1, CariKart.Tel2,'+'  CariHareket.PN, CariHareket.Evrakno, CariKart.Faks, CariKart.GrupKodu, CariKart.OzelKod1, CariKart.OzelKod2');
    AdoQuery1.SQL.add('ORDER BY CariHareket.ParaBirimi ,CariHareket.Tarihi ');
    AdoQuery1.SQL.add('PIVOT BorcAlacak');

[Battosai]

Kullandığın kod oldukça uzun bakiye işi için daha kolayı var...SQl ile senin tablo yapında bir kod ile basit yani bu işler.Şimdi gelelim dönen sonuç'a nasıl ulaşacağına Eğer filed lar Query de tanımlı değil ise sırası ile değerleri şöyle alırsın...

deger:=Query.Fields[0].text;//Dönen ilk sütun
deger1:=Query.Fields[1].text;// ikinci sütunççç
deger2:=Query.Fields[2].text;

Ayrıca dediğim gibi oldukça basit bir SQL kodu ile yukardaki SQL den kurtulabilirsin forumda ara örnekleri var....

[F.Atagun]

Hocam Allah Razı olsun, Değerli görüşün için çok teşekkür ederim.

Cari hesap ekstresini çekmek için bu uzun SQL kodunu kullanmam gerekiyor.

Ama işte bir noktadan sonra kafa duruyor.

Kod: Tümünü seç

deger:=Query.Fields[0].text;//Dönen ilk sütun 
deger1:=Query.Fields[1].text;// ikinci sütunççç 
deger2:=Query.Fields[2].text; 

Dediğin gibi hiç denememiştim. Bunu bir deneyeceğim.

[F.Atagun]

Arkadaşlar Lütfen yardım rica ediyorum. Dbgridde bu aşamaya kadar gelebildim. ( Lütfen dikkat Transform - PIVOT kullanıldı )

Kod: Tümünü seç

[b]Kodu--BA--------------Borc------Alacak--[/b] 
001------Borc--------------100---------0 
001------Borc---------------50----------0 
001------Alacak------------0-----------20 

Kullandığım Transform kodu budur

Kod: Tümünü seç

TRANSFORM Sum(CariHareket.Tutar) AS ToplaTutar
select .....
From ....
WHERE... 
GROUP BY ...
PIVOT CariHareket.BorcAlacak IN  ("A","B")

Şimdi ben

Kod: Tümünü seç

((CariHareket.BorcAlacak IN  ("B")) -( CariHareket.BorcAlacak IN  ("A")) )   as BAKİYE

ile SQl ile bakiye aldırtmaya çalışıyorum ama Bakiyeye dönen rakam Ya 1 ya 0 yada 4 çıkıyor.

Acaba neyi eksik yapıyorum.

Çözemedim bir türlü

Sevgi ve Saygılarımla


Fatih

[Battosai]

Şimdi arkadaşım tablona göre kayıtları borc ve alacak diye kayıt etmişin bunlarında kendi içinde borc-alacak diye ayrımışın görüldüğü kadarı ile... Ben kendi örneğimde kayıtları herhangi bir hesap için borc-alacak şeklinde kayıt ediyorum temel muhasebede olduğu gibi, bu hesaba ait bakiye hesabımı yaptığım SQL kodu da aşağıdakidir. Sen kendi uygulamana göre ayarla.....

Kod: Tümünü seç

Qtoplam.Close;
Qtoplam.SQL.Clear;
Qtoplam.SQL.Add('select');
Qtoplam.SQL.Add('SUM(CASE WHEN hareket_tipi =True and para_birimi=''YTL'' THEN TUTAR ELSE 0.0 END) AS YTL_ALACAK,');
Qtoplam.SQL.Add('SUM(CASE WHEN hareket_tipi =False and para_birimi=''YTL'' THEN TUTAR ELSE 0.0 END) AS YTL_BORC,');
Qtoplam.SQL.Add('from hsp where ');
Qtoplam.Open;

"hareket_tipi=True" demek alacak hareketi manasındadır. Burda Query iki sütun dönderecek sen bakiyeyi bunların farkını alarak alabilirsin...şöyle

bakiye:=Qtoplam.Fields[0].asfloat-Qtoplam.Fields[1].asfloat;

[F.Atagun]

Takıldıgım yer ( Access database'ini kullanıyoruz ) tutar fieldini TRANSFORM - PIVOT ile ayırıyorum

Değerli Arkadaşım,
Sanırım anlaşamadık. Çünkü database aşağıdaki şekildedir.

Kod: Tümünü seç

Kodu-----BA---TUTAR 
001------Borc---100 
001------Borc----50 
001------Alacak--40 
001------Borc---160 
001------Borc----150 
001------Alacak--75 
001------Borc---80 
001------Borc----40 
001------Alacak--30 

( Hemen not düşeyim, Mevcut kullandığımız program VB ile yazılmıştı. Biz ek özellikler kattıgımız için bu database'i bir süre daha kullanmak zorunda kalıyoruz . İleride SQL 'a geçeceğiz kısmetse)



Veritabanı yukarıdaki şekilde iken SQL ile aşağıdaki gibi yapıyorum ( TRANSFORM - PIVOT ile )

Kod: Tümünü seç

[b]Kodu--BA--------Borc------Alacak--[/b] 
001------Borc--------------100---------0 
001------Borc---------------50----------0 
001------Alacak------------0-----------40
001------Borc---------------60----------0 

İşte Sorunda burada. Gridde görülen bu bölümler Transform ile ayrıdıgından üzerinde işlem yapamıyorum.

Verdiğin örnek çok güzel ama ,
Access database'i bildiğim kadarıyla CASE i desteklemiyor.


Ayrıca bir dostum Stored Procedüre ile yapabilirsin dedi ama sanırım Access bunu da desteklemiyor.

Ne yapacağımı Şaşırdım Valla.






Eğer yukarıdaki yöntemimde bir hata varsa aşağıdaki "TUTAR" olan bölümü başka hangi yöntemle ayırabilirim ?

Kod: Tümünü seç

Kodu-----BA---TUTAR 
001------Borc---100 
001------Borc----50 
001------Alacak--40 
001------Borc---160 
001------Borc----150 
001------Alacak--75 
001------Borc---80 
001------Borc----40 
001------Alacak--30 

[Battosai]

Bu transform-pivot nedir bilmiyorum ama tablo yapın dediğim gibi zaten çok zorlanacak birşey yokki bunda alsana kesin çözüm buda olmadı dersen başka bişey demiyorum....iki adımda çözüyoruz meseleyi eğer case desteklenmiyorsa....

"select sum(tutar) as alacak from tablo where tip=alacak"//ilk önce bunu sonra
"select sum(tutar) as alacak from tablo where tip=borc " kodunu ayrı ayrı çalıştırıp değişkenlere değerlerini at sonra birbirinden çıkarak hallet meseleyi...

[F.Atagun]

Hocam yardımların için çok tşk ederim. Bunu bir deneyip sonucu yazacağım.
SQL kodum ilk mesajımdada görüldüğü üzere biraz uzun . Üstünde biraz çalışma yapacağım


Ayrıca TRANSFORM _ PIVOT KOMUTU şu işe yarıyor.

Database aşağıdaki şekilde iken

Kod: Tümünü seç

Kodu-----BA---TUTAR 
001------Borc---100 
001------Borc----50 
001------Alacak--40 
001------Borc---160 
001------Borc----150 
001------Alacak--75 
001------Borc---80 
001------Borc----40 
001------Alacak--30 

aşağıdaki SQL komutu verilince

Kod: Tümünü seç

TRANSFORM Sum(CariHareket.Tutar) AS ToplaTutar 
select * From TABLO
PIVOT CariHareket.BA IN  ("A","B")

Veriler aşağıdaki hale geliyor.

Kod: Tümünü seç

[b]Kodu--BA--------Borc------Alacak--[/b] 
001------Borc--------------100---------0 
001------Borc---------------50----------0 
001------Alacak------------0-----------40 
001------Borc---------------60----------0 

Kolay ama kullanışlı bir yol değil . Borç ve alacak sütunlarını toplayabilirsunuz ama KÜMÜLATİF BAKİYE alamıyorsunuz.

Yada şimdilik ben bunu bilmiyorum