Right. I got it to work perfectly. Thanks.
>Oh, sorry, the sample I posted was incomplete. I forgot the GROUP BY. Here is a corrected version:
>
>
>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 TempSummary
>
>
>The idea is that:
>1. The UNION ALL copies all the records, one table below the other - e.g., if one table has 100 records, the other 50, you get a total of 150 records - the 100 from one table, and 50 from another table.
>2. The GROUP BY combines all records for a single customer (CustNo) into one. The SUM() adds all the sales values for each combined record group, and places it into the result (in each combined record).
>
>You can GROUP BY more than one field; in this case, you'll get a single record for each unique combination of the two or more fields.
>
>You can also use UNION ALL more than once, to combine three or more tables.
>
>Try out this corrected version, and check the results.
>
>I don't expect major problems if a customer doesn't appear in every range. If a customer appears in a single record, that single record will be copied with the GROUP BY. If a customer doesn't appear at all, the customer won't appear in the results.
>
>>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?
>>
>>
>>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
