CLOSE ALL *!* USE \pro50\ardata\artran18 ALIAS a_artran SHAR IN 0 *!* USE \pro50\ardata\arytrn18 ALIAS a_arytrn SHAR IN 0 gd_sdate = DATE() * This table remains empty CREATE TABLE ('a_artran') ; (custno c(6), ; invdte D, ; extprice N(12,2)) CREATE TABLE ('a_arytrn') ; (custno c(6), ; invdte D, ; extprice N(12,2)) INSERT INTO a_arytrn VALUES ('OMNI01', {1/1/2014}, 400) INSERT INTO a_arytrn VALUES ('OMNI01', {1/1/2013}, 300) SELE custno, SUM(extprice) lyear ; FROM a_artran ; WHERE invdte BETWEEN (gd_sdate - 364) AND gd_sdate ; AND custno = 'OMNI01' ; GROUP BY custno ; INTO CURS cArtranLastYear SELE custno, SUM(extprice) lyear ; FROM a_arytrn ; WHERE invdte BETWEEN (gd_sdate - 364) AND gd_sdate ; AND custno = 'OMNI01' ; GROUP BY custno ; INTO CURS cArytrnLastYear SELE custno, SUM(extprice) pyear ; FROM a_artran ; WHERE invdte BETWEEN (gd_sdate - (365*2) - 1) AND (gd_sdate - 365 ) ; AND custno = 'OMNI01' ; GROUP BY custno ; INTO CURS cArtranPrevYear SELE custno, SUM(extprice) pyear ; FROM a_arytrn ; WHERE invdte BETWEEN (gd_sdate - (365*2) - 1) AND (gd_sdate - 365 ) ; AND custno = 'OMNI01' ; GROUP BY custno ; INTO CURS cArytrnPrevYear SELE NVL(cArtranLastYear.custno, ; NVL(cArytrnLastYear.custno, ; NVL(cArtranPrevYear.custno, cArytrnPrevYear.custno))) AS custno, ; NVL(cArtranLastYear.lyear,0) + NVL(cArytrnLastYear.lyear,0) AS LastYearSales, ; NVL(cArtranPrevYear.pyear,0) + NVL(cArytrnPrevYear.pyear,0) AS PrevYearSales ; FROM cArtranLastYear ; FULL JOIN cArytrnLastYear ; ON cArtranLastYear.custno = cArytrnLastYear.custno ; FULL JOIN cArtranPrevYear ; ON cArtranLastYear.custno = cArtranPrevYear.custno ; FULL JOIN cArytrnPrevYear ; ON cArtranLastYear.custno = cArytrnPrevYear.custno ; INTO CURS results USE IN a_artran USE IN a_arytrnI hope I'm making my self clear.
>SELECT CustNo, Sales, .T. as DummyField FROM Table1; > UNION ALL SELECT CustNo, Sales, .T. as DummyField FROM Table2; > INTO CURSOR Temp >SELECT CustNo, sum(Sales) as Sales; > FROM Temp INTO CURSOR TempSummary >>
>>Table1: >> >>Custno Sales >>ABCD 40000 >>XYZS 20000 >> >>Table2: >> >>Custno Sales >>XYZS 100 >>YYYY 7000 >>>>
>>Cursor1: >> >>Custno TotalSales >>ABCD 40000 >>XYZS 20100 >>YYYY 7000 >>>>