I tried Hilmar's solution down here below (see 'Solution based on Hilmar') and it worked. I tried an approach based on your solution (see 'Solution based on Naomi') and it produced 2 records for the same customer and I can't figure out why.
>What was the other solution you tried?
>
>Most likely it's similar to this problem:
>
>
http://blogs.lessthandot.com/index.php/datamgmt/datadesign/aggregates-with-multiple-tables/>
>>Well, I applied Hilmar's solution to handling two different ranges, thus. Still can't figure out why my code based on Naomi/Hugo is producing 2 records:
>>
Solution based on Hilmar (This worked):
>>
>>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
>>>>
>>Thanks!
>>
>>Yossi
>>>Hilmar:
>>>
>>>Thanks.
>>>
>>>But now please consider this:
>>>
>>>I tried to expand Naomi's/Hugo's solution for two different ranges of the sales - last 12 months and the 12 months previous to that.
>>>
>>>2 questions - 1. Can I use your solution for that, since I'm not guaranteed to have each customer turn up in each range and 2. Why am I getting 2 records from my code?
>>>
Solution based on Naomi (This produced 2 records):
>>>
>>>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
>>>>>>
>>>I hope I'm making my self clear.
>>>
>>>Thanks,
>>>
>>>Yossi
>>>
>>>>I suggest a UNION with a GROUP BY. Without a subquery, you can create a temporary cursor. Something like this:
>>>>
>>>>
>>>>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
>>>>
>>>>
>>>>I believe VFP 5 doesn't have the NOFILTER clause; therefore I included an extra field, which should produce the same effect.
>>>>
>>>>>Hi All:
>>>>>
>>>>>Please consider the following:
>>>>>
>>>>>
>>>>>Table1:
>>>>>
>>>>>Custno Sales
>>>>>ABCD 40000
>>>>>XYZS 20000
>>>>>
>>>>>Table2:
>>>>>
>>>>>Custno Sales
>>>>>XYZS 100
>>>>>YYYY 7000
>>>>>
>>>>>
>>>>>I want to come up with SQL that will generate a cursor called cursor1 with total sales for each customer:
>>>>>
>>>>>
>>>>>Cursor1:
>>>>>
>>>>>Custno TotalSales
>>>>>ABCD 40000
>>>>>XYZS 20100
>>>>>YYYY 7000
>>>>>
>>>>>
>>>>>Note that custno ABCD is absent from table2, XYZS is present in both and YYYY is absent from table1. Also, please note that this is for VFP 5, so nesting SELECTs may not work.
>>>>>
>>>>>Thanks,
>>>>>
>>>>>Yossi
