SQL Puzzle
I have a table that holds order details thus :
CREATE TABLE orders
(
custno CHAR(8),
product CHAR(3),
month SMALLINT,
volume INTEGER
)
I want to create a table (or view) that will show the rolling 12 month
sales history broken down by product for each customer. In other words,
I want to produce a report that lists the sales volume of each product
for the last 12 months for a particular customer.
I'm sure this is a fairly standard requirement but the SQL I am
producing is awkward and slow to run. Any suggestions?
TIA
Mark Capaldi
Sent via Deja.com http://www.deja.com/
Before you buy.
In article <81u0mi$gks$1@nnrp1.deja.com>,
mcapaldi@my-deja.com wrote:
> I have a table that holds order details thus :
>
> CREATE TABLE orders
> (
> custno CHAR(8),
> product CHAR(3),
> month SMALLINT,
> volume INTEGER
> )>
> I want to create a table (or view) that will show the rolling 12 month
> sales history broken down by product for each customer. In other
words,
> I want to produce a report that lists the sales volume of each product
> for the last 12 months for a particular customer.
>
> I'm sure this is a fairly standard requirement but the SQL I am
> producing is awkward and slow to run. Any suggestions?
I see one problem, unless the Orders header table has an order date,
including year and day, how will you distinguish between this year's
orders for December and next year's orders for December? How to
determine which records are the last 12 months at all? And if you do
have a date or datetime type column with order date in the header
record then you do not need the month column in the detail. So assume
the following schema:
create table OrderHeader (
OrderNumber serial primary key,
OrderDate DATETIME YEAR TO MINUTE,
CustNo char(8),
NumLines smallint,
...
);
create table OrderDetail (
OrderNumber integer,
ItemLine smallint,
Product char(3),
Volume integer,
....,
primary key (OrderNumber, ItemLine),
foreign key (OrderNumber) references OrderHeader(OrderNumber)
);
(I'm just typing this in so the SQL syntax may be off a bit.)
So a view showing the last 12 months detail by customer and product:
CREATE VIEW Rolling12 ()AS
SELECT h.CustNo, Product, Sum(Volume)
FROM OrderHeader h, OrderDetail d
WHERE h.OrderNumber = d.OrderNumber
AND OrderDate > (CURRENT - INTERVAL(12) MONTHS(3) TO MONTH)
GROUP BY 1,2
ORDER BY 1,2;
Art S. Kagel
Sent via Deja.com http://www.deja.com/
Before you buy.
In article <81u0mi$gks$1@nnrp1.deja.com>,
mcapaldi@my-deja.com wrote:
> I have a table that holds order details thus :
>
> CREATE TABLE orders
> (
> custno CHAR(8),
> product CHAR(3),
> month SMALLINT,
> volume INTEGER
> )>
> I want to create a table (or view) that will show the rolling 12 month
> sales history broken down by product for each customer. In other
words,
> I want to produce a report that lists the sales volume of each product
> for the last 12 months for a particular customer.
>
> I'm sure this is a fairly standard requirement but the SQL I am
> producing is awkward and slow to run. Any suggestions?
I see one problem, unless the Orders header table has an order date,
including year and day, how will you distinguish between this year's
orders for December and next year's orders for December? How to
determine which records are the last 12 months at all? And if you do
have a date or datetime type column with order date in the header
record then you do not need the month column in the detail. So assume
the following schema:
create table OrderHeader (
OrderNumber serial primary key,
OrderDate DATETIME YEAR TO MINUTE,
CustNo char(8),
NumLines smallint,
...
);
create table OrderDetail (
OrderNumber integer,
ItemLine smallint,
Product char(3),
Volume integer,
....,
primary key (OrderNumber, ItemLine),
foreign key (OrderNumber) references OrderHeader(OrderNumber)
);
(I'm just typing this in so the SQL syntax may be off a bit.)
So a view showing the last 12 months detail by customer and product:
CREATE VIEW Rolling12 (CustNo, Product, Volume)AS
SELECT h.CustNo, Product, Sum(Volume)
FROM OrderHeader h, OrderDetail d
WHERE h.OrderNumber = d.OrderNumber
AND OrderDate > (CURRENT - INTERVAL(12) MONTHS(3) TO MONTH)
GROUP BY 1,2
ORDER BY 1,2;
Art S. Kagel
Sent via Deja.com http://www.deja.com/
Before you buy.
Fishy column, month, in your table. How is a SMALLINT going to store
"month"? Not as 9901, 9902, etc, is it? What will the value be in 33 days?
"0001"? Can't think of any clear options, in that case.
If the column "month" was an integer, on the other hand, containing values
like '199901', you could use a view as follows :
create view <whatever> (custno, product, vol_past_12_mths)
as select custno, product, sum(volume)
from orders
where month >= ((year(today)-1) || month(today))
group by custno, product;
This, of course, assumes that baseline for the "last 12 months" is TODAY.
It is possible that these months are actually relative to the "last closed
month" or some such entity stored in the database. In which case, the view
would need to use that value rather than "today".
If its just a report you're after, I wouldn't use a view - a Stored
procedure would be a lot faster, primarily because it would be possible
for it to use an index on "orders.month".
Rudy
mcapaldi@my-deja.com wrote:
> I have a table that holds order details thus :
>
> CREATE TABLE orders
> (
> custno CHAR(8),
> product CHAR(3),
> month SMALLINT,
> volume INTEGER
> )>
> I want to create a table (or view) that will show the rolling 12 month
> sales history broken down by product for each customer. In other words,
> I want to produce a report that lists the sales volume of each product
> for the last 12 months for a particular customer.
>
> I'm sure this is a fairly standard requirement but the SQL I am
> producing is awkward and slow to run. Any suggestions?
>
> TIA
>
> Mark Capaldi
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.