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
