Thats not bad, but I think I found a slightly less complicated way using
a subquery in the where clause:
SELECT *
FROM lu_board_buy AS b
WHERE effective_date =
(SELECT MAX(effective_date)
FROM lu_board_buy
WHERE brecno = b.brecno
AND supp_no = b.supp_no
AND effective_date < NOW()
)
I was initially trying to do it using a HAVING clause, which could have
aggregated the unique combinations of supp_no/brecno. However, this
always returned the FIRST record in the table that matched in the
aggregate result.
Just empirically testing it now. Anyone see any major bloopers?
B.
mat wrote:
> I'll have a go :)
>
> First of all you need to filter out any price after today's date. That's easy:
>
> select * from lu_board_buy where EFFECTIVE_DATE > NOW()
>
> Next you need the single record for each product that has the last date:
>
> select BRECNO, max(EFFECTIVE_DATE) from lu_board_buy where
> EFFECTIVE_DATE <= NOW()
> group by BRECNO
>
> This gives you a list of brecno's and effective date's. Assuming that
> the combination BRECNO, EFFECTIVE_DATE is always unique, you're just
> about finished:
>
> SELECT BRECNO, EFFECTIVE_DATE, BAND1
> FROM lu_board_buy,
> (
> SELECT BRECNO, MAX(EFFECTIVE_DATE)
> FROM lu_board_buy
> WHERE EFFECTIVE_DATE <= NOW()
> GROUP BY BRECNO
> ) last_effective_date
> ON
> (
> lu_board_buy.BRECNO = last_effective_date.BRECNO
> AND
> lu_board_buy.EFFECTIVE_DATE= last_effective_date.EFFECTIVE_DATE
> )
>
> Buyer beware: I don't have a mysql handy so I didn't test the code...
>
>
> regards,
>
> Mathieu
>
>
>
> On Thu, 02 Mar 2006 15:22:13 +0000, Barry Zubel wrote:
>> There must be a complete SQL guru here somewhere ;)
>>
>> Imagine a table like this:
>>
>> This table holds a price for a particular item (denoted by BRECNO) from
>> a particular supplier (SUPP_NO). Prices for various quantities of an
>> item are held in BAND1-BAND7
>>
>> Now then... The Effective date is the date when this price will become
>> effective.
>>
>> Whats I would like to do is create a view that shows me the *current*
>> price of an item based upon the system date.
>>
>> Ideally, there would be a "Effective_date_end" field, so I could just
>> return a row based upon:
>>
>> NOW() BETWEEN EFFECTIVE_DATE AND EFFECTIVE_DATE_END
>>
>> However, there is NOT, and I cannot add this field. :(
>>
>> How would you construct an SQL query that returns the currently valid
>> "price" record?
>>
>> Bear in mind that the table may hold a lot of historical records, and
>> future records.
>>
>> Ideally, the "view" would return rows 2, 5, and 8 from the attached SQL
>> to populate the table.
>>
>> Ideas?
>>
>> Barry.
>>
>>
>> --- BEGIN SQL FOR TABLE
>> CREATE TABLE `lu_board_buy` (
>> `ID` int(11) NOT NULL auto_increment,
>> `TIMESTAMP` timestamp NOT NULL default CURRENT_TIMESTAMP on update
>> CURRENT_TIMESTAMP,
>> `BRECNO` varchar(4) NOT NULL,
>> `SUPP_NO` int(11) NOT NULL,
>> `EFFECTIVE_DATE` date NOT NULL,
>> `BAND1` int(11) NOT NULL,
>> `BAND2` int(11) NOT NULL,
>> `BAND3` int(11) NOT NULL,
>> `BAND4` int(11) NOT NULL,
>> `BAND5` int(11) NOT NULL,
>> `BAND6` int(11) NOT NULL,
>> `BAND7` int(11) NOT NULL,
>> PRIMARY KEY (`ID`),
>> KEY `BRECNO` (`BRECNO`),
>> KEY `SUPP_NO` (`SUPP_NO`)
>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;
>>
>> --
>> -- Dumping data for table `lu_board_buy`
>> --
>>
>> INSERT INTO `lu_board_buy` VALUES (1, '2006-03-02 15:00:10', 'A01', 1,
>> '2006-01-01', 10, 10, 10, 10, 10, 10, 10);
>> INSERT INTO `lu_board_buy` VALUES (2, '2006-03-02 15:00:10', 'A01', 1,
>> '2006-02-01', 20, 20, 20, 20, 20, 20, 20);
>> INSERT INTO `lu_board_buy` VALUES (3, '2006-03-02 15:17:15', 'A01', 1,
>> '2006-05-01', 30, 30, 30, 30, 30, 30, 30);
>> INSERT INTO `lu_board_buy` VALUES (4, '2006-03-02 15:17:15', 'B01', 1,
>> '2006-01-01', 15, 15, 15, 15, 15, 15, 15);
>> INSERT INTO `lu_board_buy` VALUES (5, '2006-03-02 15:17:45', 'B01', 1,
>> '2006-02-01', 25, 25, 25, 25, 25, 25, 25);
>> INSERT INTO `lu_board_buy` VALUES (6, '2006-03-02 15:17:45', 'B01', 1,
>> '2006-05-01', 35, 35, 35, 35, 35, 35, 35);
>> INSERT INTO `lu_board_buy` VALUES (7, '2006-03-02 15:18:19', 'A01', 2,
>> '2006-01-01', 123, 123, 123, 123, 123, 123, 123);
>> INSERT INTO `lu_board_buy` VALUES (8, '2006-03-02 15:18:19', 'A01', 2,
>> '2006-02-01', 234, 234, 234, 234, 234, 234, 234);
>> INSERT INTO `lu_board_buy` VALUES (9, '2006-03-02 15:18:47', 'A01', 2,
>> '2006-05-01', 345, 345, 345, 345, 345, 345, 345);
>>
>> --- END SQL FOR TABLE
>>
>>
>> --
>> MySQL on .NET Mailing List
>> For list archives: http://lists.mysql.com/dotnet
>> To unsubscribe:
>> http://lists.mysql.com/dotnet?unsub=1
>>
>>
>>
>
