List:MySQL and .NET« Previous MessageNext Message »
From:Barry Zubel Date:March 2 2006 3:22pm
Subject:SQL Question (Offtopic)
View as plain text  
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

Thread
SQL Question (Offtopic)Barry Zubel2 Mar
Re: SQL Question (Offtopic)Barry Zubel2 Mar