Need help in setting up tables 1

[aravindk]

Hi,

I have two tables, as follows:

ItemTable:This table has one record per item for each month.

Month_No,Item_no,Total_Sales
200401,0001A,22500.00
200402,0001A,36500.00
200403,0001A,23000.00
:
:

PriceTable:This table has one record per item, each month the price of the item changed. If price of the item 0001A was changed once in Jan 2004 and once in March 2004, then it will like:

Month_No,Item_No,Price
200401,0001A,$55.00
200403,0001A,$57.50
:
:

Now if want to run a report which will return the monthly sales with the latest price as of that month:

MonthNo,ItemNo,Price,Sales
200401,0001A,$55.00,22500.00
200402,0001A,$55.00,36500.00
200403,0001A,$57.50,23000.00

I know that one way to create such report is to come up with a complete join in the sql statement. But if I am allowed to change the structure of the table in order to avoid any complex join, how can i design the table, so that it always return the largest month?

Any help is appreciated,

 

[TRACEYMARYLAND]

Try using the MAX command
(SELECT MAX(Sales)
FROM TABLE

or

Select SUM(Sales) GROUP BY MonthNo

 

[rac2]

Consider creating a view that shows the highest price for items.

CREATE VIEW HighestPrices AS
SELECT ItemNo, MAX(Price) AS "HiPrice"
FROM MonthlyPrices
GROUP BY ItemNo

This view can be joined with any other table or view that has an ItemNo column.

Just for fun you might make another view that shows the latest prices.

CREATE VIEW CurrentPrices AS
SELECT a.ItemNo, a.Price AS "CurrentPrice"
FROM MonthlyPrices a
WHERE a.MonthNo = (
                SELECT MAX(MonthNo)
                FROM MonthlyPrices
                WHERE ItemNo = a.ItemNo
)

Again this view can be joined with other views and tables with information about items. This query uses the technique of the correlated subquery, the subquery is executed for each row of the main query using values from the current row in the main query.

Views are saved in the database so you only create them once, thereafter you refer to them as if they were tables. For example,

SELECT a.ItemNo, b.HiPrice, c.CurrentPrice, a.Sales
FROM MonthlySales a
JOIN HighestPrices b ON
      b.ItemNo = a.ItemNo
JOIN CurrentPrices c ON 
      c.ItemNo = a.ItemNo
WHERE a.Month_No = '200404'
ORDER BY a.ItemDescription

Views and joins are common ways to access data in a relational database. Tables should be designed to eliminate redundant data. It is common to find many tables, each with a very specific purpose, often the purpose is to describe things like Items and MonthlySales. Most requests for information will be fulfilled by queries involving joins; this is not considered complex, rather it is typical, ordinary, run-of-the-mill, dare I say it, normal.

 

[aravindk]

I didnt think about views before. Thanks for reminding me.

Anyway, those two tables are not only joined by the itemno, but also by monthno. The join should be something like this:

A.ITEM_NO = B.ITEM_NO AND A.MONTH_NO <= B.MONTH_NO.

If a price of an item was changed first in January and then again in March, when I am running the report for March, I want the March price, but when I run the report for Jan OR Feb, it should return the Jan price.

Do I make sense at all?