Hi,

I'm attempting to find the following per product:

The average price over the last 3 months for the commodity,

OR

If there has been no price over the last 3 months then the price of the
most recent item.

To simplify, let's say that the data I have is as follows.

Date   Buyer   Product      Price
1-Sep   ABC   Cans      $1.00
1-Oct   ABC   Cans      $1.20*
1-Nov   ABC   Cans      $1.30*
1-Dec   ABC   Cans      $1.40*
1-Sep   ABC   Boxes      $2.00
1-Oct   ABC   Boxes      $2.20*
1-Nov   ABC   Boxes      $2.20*
1-July   ABC   Bottles      $3.30
1-Aug   ABC   Bottles      $3.60*
1-Aug   DEF   Bottles      $3.50*

The result should show:

Product   Buyer   Price
Cans   ABC   $1.30
Boxes   ABC   $2.20
Bottles   ABC   $3.60
Bottles   DEF   $3.50

(Astrixed rows are what should be used to calculate)

For the first part it's simple:

Select Product, Avg(Price)
From Mytable
Where Date >= 1-Oct

The second part is where it gets more trickier.

From what I can tell I need to get the maximum date for each
Buyer/Product combination, and then find the record that meets that
where it doesn't already exist in the first query.

After that, join the two together.

What I'm not sure of (even though this works) if this is the best way,
or if there's a more efficient solution?


Select Product, Buyer, Avg(Price) AvgPx
Into Memory\X1
From MyTable
Where (date >= '2014-10-01')
group by PRODUCT, buyer;

Select Product, Buyer, Max(Date) MaxDate
Into Memory\MaxDates
From MyTable
group by PRODUCT, buyer;

Select Product, Buyer, Price
From MyTable MT
inner join Memory\MaxDates MD on (MD.Product = MT.Product) and (MD.Buyer
= MT.Buyer) and (MD.MaxDate = MT.Date)
left outer join Memory\X1 on (X1.Product = MT.Product) and (X1.Buyer =
MT.Buyer)
where (X1.product is null)

UNION ALL

Select Product, Buyer, AvgPx
From Memory\X1

Order by Buyer, Product

Is there a more correct / simpler solution than this, or is what I've
done best practice?

(I guess in particular I don't know if there's a cleaner solution to
grab the last record of each group)?

Thanks & Regards

Adam.

-------------

(If anyone wants to play with the same data - the following is SQL to
recreate the table):

/* SQL-92 Table Creation Script with DBISAM Extensions */

CREATE TABLE IF NOT EXISTS "MyTable"
(
   "Product" VARCHAR(10),
   "Buyer" VARCHAR(10),
   "Date" DATE,
   "Price" MONEY,
PRIMARY KEY ("RecordID") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 1
);
INSERT INTO "MyTable" VALUES (
         'CANS',
         'ABC', '2014-09-01', 1);
INSERT INTO "MyTable" VALUES (
         'CANS',
         'ABC', '2014-10-01', 1.2);
INSERT INTO "MyTable" VALUES (
         'CANS',
         'ABC', '2014-11-01', 1.3);
INSERT INTO "MyTable" VALUES (
         'CANS',
         'ABC', '2014-12-01', 1.4);
INSERT INTO "MyTable" VALUES (
         'BOXES',
         'ABC', '2014-09-01', 2);
INSERT INTO "MyTable" VALUES (
         'BOXES',
         'ABC', '2014-10-01', 2.2);
INSERT INTO "MyTable" VALUES (
         'BOXES',
         'ABC', '2014-11-01', 2.2);
INSERT INTO "MyTable" VALUES (
         'BOTTLES',
         'ABC', '2014-07-01', 3.3);
INSERT INTO "MyTable" VALUES (
         'BOTTLES',
         'ABC', '2014-08-01', 3.6);
INSERT INTO "MyTable" VALUES (
         'BOTTLES',
         'DEF', '2014-08-01', 3.5);

Adam


I don't know if its any better but


SELECT MAX(_Date) AS _Date, _Name, _Buyer

INTO Memory\Temp

FROM new WHERE _Name+_Buyer NOT IN (SELECT _Name+_Buyer FROM MyTable WHERE _Date >= '2014-10-01')
GROUP BY _Name,_Buyer;



SELECT _Name,_Buyer, AVG(_Price) AS _Price FROM MyTable WHERE _Date >= '2014-10-01' GROUP BY _Buyer,_Name
UNION
SELECT _Name, _Buyer, Y._Price FROM Memory\Temp X
LEFT JOIN MyTable Y ON
x._Date = y._Date
AND
x._Name = y._Name
AND
x._Buyer = y._Buyer;


I created an in-memory table and tested everything in memory and it "seems" to work


Roy Lambert

> I don't know if its any better but

Certainly seems cleaner.

Thanks Roy.

Cheers

Adam.

Adam

>> I don't know if its any better but
>
>Certainly seems cleaner.
>
I seem to be getting quite good at this sql thingy - still think it sucks big time though

Roy