>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() > >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, 0000000.00 AS pyear ; > 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, 0000000.00 AS pyear ; > FROM a_arytrn ; > WHERE invdte BETWEEN (gd_sdate - 364) AND gd_sdate ; > AND custno = 'OMNI01' ; > GROUP BY custno ; > INTO CURS cArytrnLastYear > >SELE custno, 0000000.00 AS lyear, 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, 0000000.00 AS lyear, 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 > >SELECT custno, lyear, pyear, .T. AS DummyField FROM cArtranLastYear ; > UNION ALL ; > SELECT custno, lyear, pyear, .T. AS DummyField FROM cArytrnLastYear ; > UNION ALL ; > SELECT custno, lyear, pyear, .T. AS DummyField FROM cArtranPrevYear ; > UNION ALL ; > SELECT custno, lyear, pyear, .T. AS DummyField FROM cArytrnPrevYear ; > INTO CURSOR Temp > >SELECT custno, ; > SUM(lyear) AS lyear, ; > SUM(pyear) AS pyear ; > FROM Temp ; > GROUP BY custno ; > INTO CURSOR results > >USE IN a_artran >USE IN a_arytrn > >RETURN >>
>>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_arytrn >>>>
>>>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 >>>>>>>>