SQL Query – How to Return Columns Within a Given Range

Unfortunately, MySQL does not have a PIVOT function and it sounds like you are attempting to get the data into the format:

+---------+-----------------+-----------------+-----------------+
| bike_no | repair_cost_Jan | repair_cost_Feb | repair_cost_Mar |
+---------+-----------------+-----------------+-----------------+
| ABC1234 |              70 |               0 |               0 |
| BMX5678 |               0 |              75 |               0 |
| BON3333 |               0 |               0 |              80 |
+---------+-----------------+-----------------+-----------------+

If that is the case, then in MySQL you will want to use an aggregate function along with a CASE expression to convert your rows of data into columns.

If you have a know number of months that you are returning then you can easily hard-code the query similar to the following:

select bike_no,
  sum(case when month(repair_date) = 1 then repair_cost else 0 end) repair_cost_Jan,
  sum(case when month(repair_date) = 2 then repair_cost else 0 end) repair_cost_Feb,
  sum(case when month(repair_date) = 3 then repair_cost else 0 end) repair_cost_Mar
from bike
where repair_date >= '2013-01-01'
  and repair_date <= '2013-03-31'
group by bike_no;

See SQL Fiddle with Demo.

But in your case it sounds like you want to change the start date and end date to your query. If that is the case, then you will want to use a prepared statement to generate dynamic SQL that would be executed:

SET @sql = NULL;
set @startdate = '2013-01-01';
set @enddate = '2013-03-31';
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN month(repair_date) = ',
      month(repair_date),
      ' THEN repair_cost else 0 END) AS `repair_cost_',
      monthname(repair_date), '`'
    )
  ) INTO @sql
FROM bike
where repair_date >= @startdate
 and repair_date <= @enddate;

SET @sql 
  = CONCAT('SELECT bike_no, ', @sql, ' 
            from bike
            where repair_date >= ''',  @startdate, ''' 
               and repair_date <= ''', @enddate, ''' 
            group by bike_no');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo. Both queries will generate the table of data above.

You can use isnull to take care of the parameters when they are null.

Something like this should do what you want.

select T.*
from #TempTbl as T
where T.StartDtm <= isnull(@StopDtm, '99991231') and 
      (T.StopDtm >= isnull(@StartDtm, '17530101') or T.StopDtm is null)