kümulatif toplam (Sayfa 1 / 2)

SELECT t1.kod, t1.mik, t1.ay, sum(t2.mik) as kumulatif ;

 FROM myTable t1 ;

 LEFT JOIN myTable t2 ;

 ON t1.kod == t2.kod AND t1.ay >= t2.ay ;

 group BY 1,3,2

SET DATE TO BRITISH 

SET CENTURY ON 

 

create cursor aystok (kod c(10), tarihirs d(8),giris n(8),cikis n(8))

insert into aystok values ('3516',CTOD('27/12/2006'),27,0)

insert into aystok values ('3516',CTOD('02/01/2007'),0,10)

insert into aystok values ('3516',CTOD('18/02/2007'),15,0)

insert into aystok values ('3516',CTOD('28/02/2007'),0,4)

 

 

Ay_Kontur = 1

 

DO WHILE Ay_Kontur <= 12 && Her ay için Toplamları oluşturalım...

 

    IF Ay_Kontur < 10

        TempCursor = "Ay_0"+ALLTRIM(STR(Ay_Kontur))

    ELSE

        TempCursor = "Ay_"+ALLTRIM(STR(Ay_Kontur))

    ENDIF

 

    SELECT ;

        AyStok.Kod AS Kod, ;

        SUM(AyStok.Giris) AS Giris, ;

        SUM(AyStok.Cikis)  AS Cikis ;

        FROM AyStok ;

        WHERE MONTH(AyStok.Tarihirs) = Ay_Kontur ;

        GROUP BY AyStok.Kod ;

        INTO CURSOR &TempCursor

    Ay_Kontur = Ay_Kontur +1

 

ENDDO

 

SET

CREATE CURSOR dene (n1 n(3),kumulatif n(5))

FOR i=1 TO 10

  INSERT INTO dene (n1) VALUES (INT(rand()*10))

ENDFOR

use dbf('dene') again in 0 alias 'dene2'

select dene

set relation to recno()-1 into dene2

 

REPLACE ALL kumulatif WITH dene2.kumulatif+n1

LOCATE

USE IN 'dene2'

BROWSE LAST

*CrossTab.prg

Lparameters tcSource,tcTarget,tcRowFields,tcColumnField, tcDataField

LOCAL ix,lcConnect

LOCAL ARRAY aCols[1]

SELECT distinct &tcRowFields, SPACE(10) as uid ;

    FROM (m.tcSource) ;

    INTO CURSOR __crsUID ;

    readwrite

replace ALL uid WITH SYS(2015)

 

SET TEXTMERGE TO memvar m.lcConnect noshow

SET TEXTMERGE on

FOR ix = 1 TO ALINES(aCols,m.tcRowFields,.t.,',')

\\<<IIF(m.ix > 1,' and ','')>>lft.<<aCols[m.ix]>> == rgt.<<aCols[m.ix]>>

endfor

SET TEXTMERGE to

SET TEXTMERGE off

 

SELECT lft.*, rgt.uid ;

    FROM (m.tcSource) lft ;

    INNER JOIN __crsUID rgt ON &lcConnect ;

    INTO CURSOR __crsTemp ;

    nofilter

 

* Cross tab data

* tcSource,tcTarget,tcRowField,tcColumnField, tcDataField

CreateCrossTab('__crsTemp','__crsXTab','uID',m.tcColumnField, m.tcDataField)

 

SET TEXTMERGE TO memvar m.lcFields noshow

SET TEXTMERGE on

FOR ix = 1 TO ALINES(aCols,m.tcRowFields,.t.,',')

\\<<IIF(m.ix > 1,',','')>>t1.<<aCols[m.ix]>>

endfor

SET TEXTMERGE to

SET TEXTMERGE off

 

IF USED(m.tcTarget)

    USE IN (m.tcTarget)

endif

SELECT DISTINCT &lcFields,t2.* ;

    FROM __crsTemp t1 ;

    INNER JOIN __crsXtab t2 ;

    ON t1.UID == t2.UID ;

    INTO CURSOR (m.tcTarget) ;

    readwrite

ALTER table (m.tcTarget) drop COLUMN uid

USE IN '__crsUID'

USE IN '__crsXTab'

USE IN '__crsTemp'

 

Function CreateCrossTab

    Lparameters tcSource,tcTarget,tcRowField,tcColumnField, tcDataField

    Local ix,lcType,lnLen,lnDec,lcType1,lnLen1,lnDec1,lcLeft,lcRight

    Local Array aStruct[1],aCols[1], aXTab[1]

    For ix=1 To Afields(aStruct,m.tcSource)

        If Upper(Trim(m.tcRowField)) == Upper(Trim(aStruct[m.ix,1]))

            lcType1 = aStruct[m.ix,2]

            lnLen1  = aStruct[m.ix,3]

            lnDec1  = aStruct[m.ix,4]

        Endif

        If Upper(Trim(m.tcDataField)) == Upper(Trim(aStruct[m.ix,1]))

            lcType = aStruct[m.ix,2]

            lnLen  = aStruct[m.ix,3]

            lnDec  = aStruct[m.ix,4]

        Endif

    Endfor

 

    SELECT &tcColumnField,SYS(2015) as orderby ;

        FROM (m.tcSOurce) ;

        INTO CURSOR __xorder__ ;

        nofilter

    lcLeft  = 't1.'+m.tcColumnField

    lcRight = 't2.'+m.tcColumnField

    SELECT t1.orderby,&tcColumnField ;

        FROM __xorder__ t1 ;

        WHERE t1.orderby == ;

        (select MIN(orderby) FROM __xorder__ t2 ;

        WHERE &lcLeft == &lcRight ) ;

        ORDER BY 1 ;

        INTO cursor __sortCols__

    SELECT &tcColumnField ;

        FROM __sortCols__ ;

        INTO ARRAY aCols

    USE IN '__xorder__'

    USE IN '__sortCols__'

 

    Dimension aXTab[Alen(aCols)+1,5]

    aXTab[1,1] = m.tcRowField

    aXTab[1,2] = m.lcType1

    aXTab[1,3] = m.lnLen1

    aXTab[1,4] = m.lnDec1

    aXTab[1,5] = .f.

    For ix=1 To Alen(aCols)

        aXTab[m.ix+1,1] = Chrtran(Trim(aCols[m.ix]),' ','_')

        aXTab[m.ix+1,2] = m.lcType

        aXTab[m.ix+1,3] = m.lnLen

        aXTab[m.ix+1,4] = m.lnDec

        aXTab[m.ix+1,5] = .t.

    Endfor

    Create Cursor (m.tcTarget) From Array aXTab

    Local Array arrRec[Floor(65000/Fcount()),Fcount()]

    arrRec = .null.

    Select Distinct &tcRowField As _RowData ;

        from (m.tcSource) ;

        into Cursor _DRows

    lnRec = 0

    Scan

        If m.lnRec = 65000

            Insert Into (m.tcTarget) From Array arrRec

            arrRec = .F.

            lnRec = 0

        Endif

        lnRec = m.lnRec + 1

        arrRec[m.lnRec,1] = _DRows._RowData

        Select (m.tcSource)

        Scan For Evaluate(m.tcRowField) = arrRec[m.lnRec,1]

            arrRec[m.lnRec, ;

        Asubscript(aXTab, ;

        Ascan(aXTab,;

        Chrtran(Trim(;

        Evaluate(m.tcSource+'.'+m.tcColumnField)),' ','_')),1)] = ;

                Evaluate(m.tcSource+'.'+m.tcDataField)

        Endscan

        Select (m.tcSource)

    Endscan

    Use In '_DRows'

    Dimension arrRec[m.lnRec,Alen(arrRec,2)]

    Insert Into (m.tcTarget) From Array arrRec

    Select (m.tcTarget)

    Locate

Endfunc

CREATE CURSOR dene (kod c(2),ay c(2),miktar n(3),kumulatif n(10))

SELECT dene

FOR i=1 TO 1000

  INSERT INTO dene (kod,ay,miktar) VALUES (TRANSFORM(INT(rand()*10)+1,"@L ##"),TRANSFORM(INT(rand()*12)+1,"@L ##"),INT(rand()*10))

ENDFOR 

 

pkumulatif=0

SCAN

    REPLACE kumulatif WITH miktar+pkumulatif

    pkumulatif=kumulatif

ENDSCAN 

 

GO top

BROWSE LAST

CREATE CURSOR dene (kod c(2),ay c(2),miktar n(3),kumulatif n(10))

SELECT dene

FOR i=1 TO 1000

  INSERT INTO dene (kod,ay,miktar) VALUES (TRANSFORM(INT(rand()*10)+1,"@L ##"),TRANSFORM(INT(rand()*12)+1,"@L ##"),INT(rand()*10))

ENDFOR 

INDEX on kod+ay TAG koday

 

ckoday=kod+ay

pkumulatif=0

SCAN

    IF ckoday=kod+ay

        REPLACE kumulatif WITH miktar+pkumulatif

        pkumulatif=kumulatif

    ELSE

        ckoday=kod+ay

        REPLACE kumulatif WITH miktar

        pkumulatif=kumulatif

    ENDIF 

ENDSCAN 

 

GO top

BROWSE LAST

SET DATE YMD 

Create Cursor aystok (kod c(10),tarihirs d(8),giris N(8),cikis N(8))

Insert Into aystok Values ('3516',{^2006/12/27},27,0)

Insert Into aystok Values ('3516',{^2007/01/02},0,10)

Insert Into aystok Values ('3516',{^2007/02/18},15,0)

Insert Into aystok Values ('3516',{^2007/02/28},0,4)

Insert Into aystok Values ('3516',{^2007/03/28},0,0)

FOR i=1 to 100

*    Insert Into aystok Values ('3516',CTOD(TRANSFORM(INT(rand()*2)+2006)+"/"+TRANSFORM(INT(rand()*11)+1,"@L ##")+"/"+TRANSFORM(INT(rand()*26)+1,"@L ##")),INT(rand()*5)+5,INT(rand()*5))

ENDFOR 

 

SELECT kod, ;

  TRANSFORM(YEAR(tarihirs),"####") as yil,;

  TRANSFORM(MONTH(tarihirs),"@L ##") as ay,;

  SUM(giris-cikis) as stok, ;

  00000 as kumulatif ;

  FROM aystok ;

  GROUP BY 1,2,3 ;

  INTO CURSOR topStok READWRITE 

 

ckodyilay=kod

pkumulatif=0

SCAN

    IF ckodyilay=kod

        REPLACE kumulatif WITH stok+pkumulatif

        pkumulatif=kumulatif

    ELSE

        ckodyilay=kod

        REPLACE kumulatif WITH stok

        pkumulatif=kumulatif

    ENDIF 

ENDSCAN 

 

SELECT yil,ay FROM topstok ORDER BY 1,2 INTO CURSOR periods

 

runcode=""

runcode=runcode+ "select kod ;"+chr(13)

select periods

go top

do while not eof()

    cperiod="p"+periods.yil+"_"+periods.ay

    runcode=runcode+ [,SUM(IIF(yil+ay="]+periods.yil+periods.ay+[",kumulatif,0)) as ]+cperiod +";"+CHR(13)

    select periods

    skip

enddo 

runcode=runcode+ "from topstok ;"+chr(13)

runcode=runcode+ "group by kod ;"+chr(13)

runcode=runcode+ "into cursor stok_durum readwrite"+chr(13)

=execscript(runcode)

 

GO top

BROWSE LAST

* Query 1

select Depo_kodu, Stok_kodu, Bolge_kodu ;

,SUM(IIF(period="0401",miktar,0)) as U0401 ;

,SUM(IIF(period="0402",miktar,0)) as U0402 ;

,SUM(IIF(period="0403",miktar,0)) as U0403 ;

...

...

...

,SUM(IIF(period="04Y1",miktar,0)) as U04Y1 ;

,SUM(IIF(period="05Y2",miktar,0)) as U05Y2 ;

,SUM(IIF(period="06Y3",miktar,0)) as U06Y3 ;

from data01 group by Depo_kodu, Stok_kodu, Bolge_kodu ;

into table data02 readwrite

* Query 2

select Depo_kodu, Stok_kodu, Bolge_kodu ;

,CAST(SUM(IIF(period="0401",miktar,0))  as numeric(7))    as U0401,CAST(SUM(IIF(period="0401",YTL_tutar,0)) as numeric(11,2)) as V0401,CAST(SUM(IIF(period="0401",USD_tutar,0))   as numeric(11,2)) as D0401 ;

,CAST(SUM(IIF(period="0402",miktar,0))  as numeric(7))    as U0402,CAST(SUM(IIF(period="0402",YTL_tutar,0)) as numeric(11,2)) as V0402,CAST(SUM(IIF(period="0402",USD_tutar,0))   as numeric(11,2)) as D0402 ;

,CAST(SUM(IIF(period="0403",miktar,0))  as numeric(7))    as U0403,CAST(SUM(IIF(period="0403",YTL_tutar,0)) as numeric(11,2)) as V0403,CAST(SUM(IIF(period="0403",USD_tutar,0))   as numeric(11,2)) as D0403 ;

...

...

...

,CAST(SUM(IIF(period="04Y1",miktar,0))  as numeric(7))    as U04Y1,CAST(SUM(IIF(period="04Y1",YTL_tutar,0)) as numeric(11,2)) as V04Y1,CAST(SUM(IIF(period="04Y1",USD_tutar,0))   as numeric(11,2)) as D04Y1 ;

,CAST(SUM(IIF(period="05Y2",miktar,0))  as numeric(7))    as U05Y2,CAST(SUM(IIF(period="05Y2",YTL_tutar,0)) as numeric(11,2)) as V05Y2,CAST(SUM(IIF(period="05Y2",USD_tutar,0))   as numeric(11,2)) as D05Y2 ;

,CAST(SUM(IIF(period="06Y3",miktar,0))  as numeric(7))    as U06Y3,CAST(SUM(IIF(period="06Y3",YTL_tutar,0)) as numeric(11,2)) as V06Y3,CAST(SUM(IIF(period="06Y3",USD_tutar,0))   as numeric(11,2)) as D06Y3 ;

from data01 group by Depo_kodu, Stok_kodu, Bolge_kodu ;

into table data02 readwrite