Retail Analysis Query

[audiopro]

I am automating the analysis of sales from 2 retail outlets. The relevant files are downloaded in the early hours of the morning and reside on the machine of the main branch. So far so good but here is the problem.
The two dbf files which have the same name are stored in seperate directories.
The following code processes the data of one single dbf file, adds up the individual totals and puts the result into a cursor.

    SELECT DAYSALES.Code,SUM(DAYSALES.Qty) AS TQTY,ASTOCK.Product;
        FROM Z:\ASTOCK.DBF ASTOCK INNER JOIN Z:\DAYSALES.DBF DAYSALES ON ASTOCK.CODE = DAYSALES.CODE;
        WHERE DAYSALES.Qty>0 AND DAYSALES.DATE>=DATE()-DAYZ AND LEFT(DAYSALES.CODE,1)=CHR(NUM);
        ORDER BY DAYSALES.Code;
        GROUP BY DAYSALES.CODE;
        INTO CURSOR SHOP

What I would like to do, if it is possible, is combine the results of both tables into a common report but keep the individual shop details seperate. Is it possible to do it with a single query, or will I have to combine the two tables first. I would appreciate your thoughts as to suggested methods.
Thanks in advance

Keith

http://www.studiosoft.co.uk

 

[darrellblackhawk]

I'm assuming that z:\daysales is one of the two sales
tables. You might be able to construct a union on the two
tables, but I'm not at my VFP development system, so I
can't test it now. You can do this with other DBMS systems,
but I've never done it in VFP. I'll test it when I get on
my dev system.

Darrell

 

[ramani]

Hi

SELECT "01" AS Branch, ;
    DAYSALES.Code,SUM(DAYSALES.Qty) AS TQTY,ASTOCK.Product;
        FROM Z:\ASTOCK.DBF ASTOCK INNER JOIN Z:\DAYSALES.DBF DAYSALES ON ASTOCK.CODE = DAYSALES.CODE;
        WHERE DAYSALES.Qty>0 AND ;
          DAYSALES.DATE>=DATE()- DAYZ AND ;
          LEFT(DAYSALES.CODE,1)=CHR(NUM);
UNION ALL ;
   SELECT "02" AS Branch, ;
    DAYSALES.Code,SUM(DAYSALES.Qty) AS TQTY,ASTOCK.Product;
        FROM Y:\ASTOCK.DBF ASTOCK INNER JOIN ;
         Y:\DAYSALES.DBF DAYSALES ON ;
            ASTOCK.CODE = DAYSALES.CODE;
        WHERE DAYSALES.Qty>0 AND ;
            DAYSALES.DATE>=DATE()-DAYZ AND ;
        LEFT(DAYSALES.CODE,1)=CHR(NUM);
  ORDER BY 1,2 ;
  GROUP BY 1,2 ;
  INTO CURSOR totalshop......

The idea is to use the UNION ALL to combine the extraction in one cursor and group it on a total basis. I have not read your total code, but simply copied...



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[audiopro]

Thanks guys - I know you like a challenge
Both files are called daysales.
One is z:\daysales and the other is z:\analysis\daysales
I had thought of combining the two tables first but if it is possible to achieve the goal with a query, that would be much better.
I really need to have the totals displayed together rather than one shop after the other ie.

Code       Item       Qty Shop1     Qty Shop2    Total
A123       BUCKET        8              5          13
B242       SPADE         16             6          22
C343       STICK         23             9          32

The output will be a report but I need to get the data in place first


Keith

http://www.studiosoft.co.uk

 

[audiopro]

Almost forgot, both tables have a Shop ID field to identify the source

Keith

http://www.studiosoft.co.uk

 

[mm0000]

One possible solution:

Dont combine the two tables. With your query keep 2 separate summary tables. Use the item master also for your report.

select 0
use shop1
index on code to shop1

select 0
use shop2
index on code to shop2

seelect 0
use itemmaster
set rela to code into shop1 addi
set rela to code into shop2 addi

Report Format

Code item shop1->qty shop2->qty shop1->qty+shop2->qty

You can use the 'Print When'/'Remove blank lines' report functionality to exclude items with 0 items sold in both shops