Mysql:: Stored Procedure:: Looping Daily Date With Time Range

Posted: June 14, 2011 in MySql

PROCEDURE abc(pmId INT, dateFrom DATE, dateTo DATE)
BEGIN
        DECLARE str TEXT;
        DECLARE dff DATETIME;
        DECLARE dnn DATETIME;
        DECLARE dn DATE;
    DECLARE df DATE;

    SET df = dateFrom;

    WHILE DATE(df) <= DATE(dateTo) DO
            SET dn = DATE_ADD(df, INTERVAL 1 DAY);
  
            SET dff = CONCAT(df, ‘ 08:00:00’);
            SET dnn = CONCAT(dn, ‘ 07:59:00’);
  
            SELECT ‘CT1′ as trip, DATE_FORMAT(date,’%d-%m-%Y’) as date,GROUP_CONCAT(DISTINCT CAST(driver_id AS CHAR)) as  
                        dri, count( IF(size=20, 1, NULL ) ) AS ctOne20, count( IF(size=40, 1, NULL ) ) AS ctOne40
            FROM trip_ct WHERE pm_id=pmId AND DATE_ADD(date, INTERVAL time HOUR_SECOND) BETWEEN dff AND dnn;

            SET df = DATE_ADD(df, INTERVAL 1 DAY);
               
    END WHILE;
END

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s