Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 4 of 4 total |
Retrieving average from most recent records |
Tue, Dec 9 2014 11:30 PM | Permanent Link |
Adam H. | 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); |
Wed, Dec 10 2014 4:16 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Dec 10 2014 10:08 PM | Permanent Link |
Adam H. | > I don't know if its any better but
Certainly seems cleaner. Thanks Roy. Cheers Adam. |
Thu, Dec 11 2014 7:04 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Thursday, May 23, 2024 at 07:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |