Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
SET @qdate := '2014-02-17';
SELECT 
    com.trading_code,
    com.business_segment_id,
    dmkt.close_price,
    dmkt.cdate,
    dmkt.turnover,
    dmkt.volume,
    sh.total_no_share,
    IF(dmkt.close_price IS NULL,
        ((SELECT 
                close_price
            FROM
                daily_mkt_status
            WHERE
                trading_code = com.trading_code
                    AND cdate <= @qdate
            ORDER BY cdate DESC
            LIMIT 1) * sh.total_no_share),
        (dmkt.close_price * sh.total_no_share)) AS mcap
FROM
    company AS com
        LEFT JOIN
    daily_mkt_status AS dmkt ON (com.trading_code = dmkt.trading_code
        AND dmkt.cdate = @qdate )
        LEFT JOIN
    share_info AS sh ON (com.trading_code = sh.trading_code)

This code works only when i use the variable like this, but what i want it to do is SET @qdate :- '2014-02-17' to be an array of dates from another table like this SET @qdate IN (SELECT cdate FROM market_index); the qdate will cycle through all the date from the table market_index. i have gone through all the array variable questions but non worked for me because of this part of my code cdate <= @qdate but i need this part to be there or my query doesnt work the way i want it to.

hope i was clear, and many thanks in advance.

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.