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