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; GROUP BY CustNo; INTO CURSOR TempSummaryThe idea is that:
>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 >>>>>>