Impromptu Join Logic 1

[debdas]

Hi Experts,

I have two tables one "Sales Table - SALES_TABLE01" and another "Inventory Table - INVENTORY_TABLE02" now I want to merge two tables, Logic is to add sales & margin item-wise & month-wise and extract last transaction itemwise & month-wise from Inventory table.

Now my question is : How I will join two tables so at a same time I am adding sales, margin itemwise & monthwise and extracting LAST transaction itemwise & monthwise "ON HAND BALANCE" from Inventory table.

If there is any other way around please let me know.



SALES_TABLE01
ITEM NO TRAN DT SALES MARGIN
001 01-APR-2003 1000.00 250.00
001 05-APR-2003 2000.00 500.00
001 07-MAY-2003 3000.00 750.00
001 10-MAY-2003 1000.00 250.00
001 12-MAY-2003 1000.00 250.00
002 01-APR-2003 1000.00 100.00
002 15-APR-2003 1500.00 100.00



INVENTORY_TABLE02
ITEM NO TRAN DT ON HAND BALANCE
001 01-APR-2003 15
001 05-APR-2003 20
001 07-APR-2003 10
001 15-APR-2003 25
001 05-MAY-2003 100
001 18-MAY-2003 60
002 01-APR-2003 150
002 15-APR-2003 600

OUTPUT_TABLE03
ITEM NO MONTH SALES MARGIN INVENTORY
001 APR 3000.00 750.00 25
001 MAY 2000.00 1250.00 60
002 APR 2500.00 200.00 600

 

[griffindm]

Hi Debdas,

The problem is that there is no column to identify a transaction (assuming there would be multiple rows in the inventory table for a single day if there were multiple transactions), or more likely, that in your example there is not a tran_dt row for every day with sales transactions, which I think might be an inadvertant omission on your part (i.e. see Sales and Inventory for Tran_Dt 10-May-2003 for Item 001: Has Sales row but no Inventory row).

If this is not really true, and there is always a single inventory row when there is sales data for a date, then you could make a join between the two based on Item_No and Tran_Dt. This would give one and only one row in Inventory for any row in Sales (i.e. would not confound the result via a 1-to-many join).

However, if you only want the monthly report, I would make a view of the Inventory table to return only the last entry for each month and join this to the Sales table. This would make the report construction a little easier. Alternatively you could do this with a separate report and use this as a smart hotfile.

Hope this helps,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[debdas]

Thanks Dave Griffin

D Das