Konu: dosyaları birleştirme
SELECT YEAR(fattarih) seneler,MONTH(fattarih) ay,SUM(tutar) toplam FROM fatura WHERE tutar>0;
GROUP BY 1,2 ORDER BY 1 INTO CURSOR aytoplam
SELECT seneler, COUNT(ay) ay, SUM(toplam) giren FROM aytoplam ;
GROUP BY 1 ORDER BY 1 INTO CURSOR aytoplam
**** fırmalara yapılan ödemeler
SELECT YEAR(fattarih) sene,MONTH(fattarih) ay,SUM(tutar) toplam FROM fatura WHERE tutar<0;
GROUP BY 1,2 ORDER BY 1 INTO CURSOR aytoplam1
SELECT sene, COUNT(ay) ay, SUM(toplam) cikan FROM aytoplam1 ;
GROUP BY 1 ORDER BY 1 INTO CURSOR aytoplam1
***** iki dosya birleşsin
SELECT seneler,ay,giren, Cast(Nvl(cikan,0) As Int) As cikan FROM (SELECT seneler, COUNT(ay) ay, SUM(giren) giren FROM aytoplam GROUP BY 1 ORDER BY 1) aytoplam ;
right Join ;
(SELECT sene, COUNT(ay) ay, SUM(cikan) cikan FROM aytoplam1 GROUP BY 1) aytoplam1;
ON seneler=aytoplam1.sene INTO CURSOR aytoplam11
hata veriyor "AY is not enique and must be qualified"
fatura doryasında alınan ürünlerin tutarı toplam sütününda pozitif yapılan ödemelerse aynı sütünda negatif olarak var.
2000 yılında 12 ay mal alımı olmuş ödemelerse sadece 7 ay yapılmış
istediğim şey giren kısmında alınan mal tutarı, Ay colonunda çalışılan ay aytoplam.ay=aytoplam1.ay ise cikan yazılacak değilse yeni bir satıra cıkan bölümü ve ay yazılacak
bunu nasıl yapabilirim ?
cevaplar için şimdiden teşekkürler
Örnek data
CreateCursor_AYTOPLAM()
CreateCursor_AYTOPLAM1()
CreateCursor_FATURA()
Procedure CreateCursor_AYTOPLAM
LOCAL lcData, lcTemp
lcTemp = SYS(2015)+'.tmp'
TEXT TO m.lcData noshow
2010,12,11563,506
2011,12,17609,234
2012,9,27681,387
ENDTEXT
STRTOFILE(m.lcData,m.lcTemp)
create CURSOR AYTOPLAM ;
( ;
SENELER N(5,0) NOT NULL ;
,AY N(10,0) NOT NULL ;
,GIREN N(16,3) NOT NULL ;
)
APPEND FROM (m.lcTemp) TYPE delimited
ERASE (m.lcTemp)
endproc
Procedure CreateCursor_AYTOPLAM1
LOCAL lcData, lcTemp
lcTemp = SYS(2015)+'.tmp'
TEXT TO m.lcData noshow
2010,12,-9099,970
2011,12,-16750,660
2012,7,-8034,000
ENDTEXT
STRTOFILE(m.lcData,m.lcTemp)
create CURSOR AYTOPLAM1 ;
( ;
SENE N(5,0) NOT NULL ;
,AY1 N(10,0) NOT NULL ;
,CIKAN N(16,3) NOT NULL ;
)
APPEND FROM (m.lcTemp) TYPE delimited
ERASE (m.lcTemp)
endproc
Procedure CreateCursor_FATURA
LOCAL lcData, lcTemp
lcTemp = SYS(2015)+'.tmp'
TEXT TO m.lcData noshow
"Elit Vet","0221154",27.01.2010,"Defensor 3",50,0,000,0,00,0,000,35,000,"KUDUZ","5843309A",01.07.2010,"KEDİ","","AŞI",""
"Elit Vet","0221055",11.01.2010,"Pro-colin+",11,15,000,8,00,168,742,25,000,"","",. .,"","","İLAÇ",""
"Elit Vet","0221055",11.01.2010,"Synulox 50 Mg",11,10,980,8,00,130,442,25,000,"","",. .,"","","İLAÇ",""
"Elit Vet","0221055",11.01.2010,"Stronghold 240 Mg",3,16,210,8,00,52,520,30,000,"","",. .,"","","İLAÇ",""
"Elit Vet","0221055",11.01.2010,"Stronghold 15 Mg",3,8,380,8,00,27,151,20,000,"","",. .,"","","İLAÇ",""
"Elit Vet","0221055",11.01.2010,"Iskonto",1,15,340,0,00,15,340,0,000,"","",. .,"","","TEDAVİ",""
"AKPA","061103",21.01.2010,"Katküt 2/0",2,25,520,8,00,55,123,25,000,"","",. .,"","","SARF",""
"AKPA","061103",21.01.2010,"Katküt 1/0",1,26,430,8,00,28,544,25,000,"","",. .,"","","SARF",""
"AKPA","192996",11.01.2010,"2 cc Enjektör",300,0,110,8,00,35,640,5,000,"","",. .,"","","SARF",""
"AKPA","192996",11.01.2010,"Fibrex Cat 0 Kg",21,8,830,8,00,200,264,20,000,"","",. .,"KEDİ","","AŞI",""
"AKPA","192996",11.01.2010,"Fibrex Dog 20-40 Kg",21,14,160,8,00,321,149,30,000,"","",. .,"KÖPEK","","AŞI",""
"AKPA","192996",11.01.2010,"Bayticol pour on",100,0,150,8,00,16,200,20,000,"","",. .,"KÖPEK","","AŞI",""
"AKPA","192996",11.01.2010,"Aminosol",250,0,050,8,00,13,500,2,000,"","",. .,"KÖPEK","","İLAÇ",""
"MEDA-VET","103300",28.01.2010,"Ketasol enj.flk.",10,2,000,8,00,21,600,50,000,"","",. .,"","","NARKOTİK",""
"MEDA-VET","002482",28.01.2010,"ÖDEME",0,0,000,0,00,-100,000,0,000,"","",. .,"","NAKİT","TEDAVİ",""
"MEDA-VET","002461",21.01.2010,"ÖDEME",0,0,000,0,00,-100,000,0,000,"","",. .,"","NAKİT","TEDAVİ",""
"Elit Vet","01051",16.01.2010,"ÖDEME",0,0,000,0,00,-50,000,0,000,"","",. .,"","NAKİT","TEDAVİ",""
"AKPA","0862",15.01.2010,"ÖDEME",0,0,000,0,00,-50,000,0,000,"","",. .,"","NAKİT","TEDAVİ",""
"Elit Vet","00988",09.01.2010,"ÖDEME",0,0,000,0,00,-50,000,0,000,"","",. .,"","NAKİT","TEDAVİ",""
"MEDA-VET","002389",14.01.2010,"ÖDEME",0,0,000,0,00,-150,000,0,000,"","",. .,"","NAKİT","TEDAVİ",""
"MEDA-VET","002361",07.01.2010,"ÖDEME",0,0,000,0,00,-50,000,0,000,"","",. .,"","NAKİT","TEDAVİ",""
"Elit Vet","00978",02.01.2010,"ÖDEME",0,0,000,0,00,-50,000,0,000,"","",. .,"","NAKİT","TEDAVİ",""
"AKPA","0880",29.01.2010,"ÖDEME",0,0,000,0,00,-40,000,0,000,"","",. .,"","NAKİT","TEDAVİ",""
"Elit Vet","01078",30.01.2010,"ÖDEME",0,0,000,0,00,-100,000,0,000,"","",. .,"","NAKİT","TEDAVİ",""
"TURUNÇOĞLU PET"," 1",01.01.2010,"Senet",1,520,000,0,00,520,000,0,000,"","",. .,"","","TEDAVİ",""
"MOPSAN","254544",05.01.2010,"Mama F. Adult 15 Kg",1,99,820,18,00,117,788,174,000,"","",. .,"","","MAMA",""
"MOPSAN","254544",05.01.2010,"Iskonto",-1,0,000,0,00,0,000,0,000,"","",. .,"","","TEDAVİ",""
"MOPSAN","296906",26.01.2010,"Mama C. Adult L&r 15",1,99,000,18,00,117,110,175,000,"","",. .,"","","MAMA",""
"ASVET","695546",09.01.2010,"Mama Cpd-s 2,5",1,28,000,18,00,33,040,45,000,"","",. .,"","","MAMA",""
"ASVET","695546",09.01.2010,"Mama Cpd-m 2,5",1,29,000,18,00,34,220,50,000,"","",. .,"","","MAMA",""
ENDTEXT
STRTOFILE(m.lcData,m.lcTemp)
create CURSOR FATURA ;
( ;
FIRMA C(25,0) NOT NULL ;
,FATNO C(10,0) NOT NULL ;
,FATTARIH D(8,0) NOT NULL ;
,CINSI C(25,0) NOT NULL ;
,MIKTAR N(10,0) NOT NULL ;
,BFIYAT N(10,3) NOT NULL ;
,KDV N(5,2) NOT NULL ;
,TUTAR N(15,3) NOT NULL ;
,SATIS N(10,3) NOT NULL ;
,ASI C(25,0) NOT NULL ;
,SERINO C(15,0) NOT NULL ;
,SKUL D(8,0) NOT NULL ;
,GURUP C(30,0) NOT NULL ;
,ODEME C(35,0) NOT NULL ;
,GURUP1 C(15,0) NOT NULL ;
,RESYOL C(254,0) NOT NULL ;
)
APPEND FROM (m.lcTemp) TYPE delimited
ERASE (m.lcTemp)
endproc