Combining data from 2 tables

[audiopro]

I have 2 tables which contain the sales figures for 2 individual retail shops. Both tables have the same format but have different names.
I have programatically combined these tables into a new table and created a report which displays the summed qty of each item as below.

ItemCode    Shop1Qty Shop2Qty
A111   2  0
A112   0  3
A113   2  0
A116   1  0
A118  12  0
A119   3  2
A115   4  2

This works fine and the report detailing the previous days sales from both shops is waiting for the client at head office the following morning. All magic stuff (he says) and has worked a treat for a number of months now.
The whole conversion process takes a couple of minutes but is not a problem as all this happens over night.

Now the client wants to be able to be more selective and create reports in real time and 2 minutes is far too long to wait for results.
Is it possible to create this table using a single query or combine the results of 2 queries into a single table.
Not looking for a coded solution but wanting to know if this is possible and if so, possibly a pointer towards the solution.

Keith

 

[audiopro]

I have been looking at FULL OUTER JOIN to combine the two tables and I could sum the total of each item from both shops but how do I calculate the seperate item totals from each shop?

Keith

 

[mkrausnick]

YOu don't say whether the sales tables contain each transaction detail or only item totals, so I'll assume they contain sales detail.

Typically you would have an item master file and do left outer joins off of that. That way you will see items that have no sales - which should be important to the client:

select master.itemid,storea.qty as qty_a, storeb.qty as qty_n from master left outer join storea on master.id=storea.id 
left outer join storeb on master.id=storeb.id
into cursor sales

To minimize process time, make sure all tables are indexed on ID - and this is important - using EXACTLY the same index expression. To allow Rushmore optimization to do its job the best, don't use functions in the index expression - things like "UPPER(ID)" etc.

Then to get one store's totals by item, you can:

select id,sum(qty_a) as qty_storea from sales group by id

Not sure if this will help, but post back with questions.













Mike Krausnick
Dublin, California

 

[mkrausnick]

Typo alert: 'qty_n' in the first select should have been 'qty_b'

Mike Krausnick
Dublin, California

 

[audiopro]

The tables do indeed contain the sales data at 1 record per item sold. They also contain additional info such as paid in, paid out, refunds etc. but that is not of concern at present.
Been having a think about the functioning of it and felt that I am processing a lot of unnecessary data. The tables contain several years of data but this function is to aid the ordering cycle so any data over say, 3 months old, is irrelevant.
I need the seperate totals of each item sold to appear on the same report.
I am looking into rationalising the present system but wondered if it was possible to achieve all this in 1 query.




Keith

 

[baltman]

If you have an index on your sales date and the query refers to it with a WHERE clause it should run pretty fast.

I wouldn't bother combining the tables into one, rather create two seperate summary cursors and then combine the summaries. No need to combine transaction level data unless there's something you can't aggregate.

A design that stores sales data into seperate years or quarters per table would be more effecient although it would require more work to retrieve the data.

Brian

 

[audiopro]

I launched a seperate thread 'Appending to an existing Cursor' which is concerned with the same problem but asking a different question.
I have received several useful suggestions in both threads but have not had time to really test them out, I will post my findings and probably, more questions when I have done the tests.
Thanks


Keith