Need help in SQL query

I have DB table below:

TaskId StartDate   TargetEnd  TaskStatus
1      2011-01-01  2011-02-05 completed
2      2011-02-05  2011-02-05 ongoing
3      2011-02-04  2011-04-04 completed
4      2011-04-04  2011-04-04 completed
5      2011-04-06  2011-04-30 ongoing

I created a query where i can get the total completed and ongoing tasks per month:

SELECT YEAR(TargetEnd) 'YEAR',
  MONTHNAME(TargetEnd) 'MONTH',
  SUM(IF(TaskStatus = 'ongoing',1,0)) 'ONGOING',
  SUM(IF(TaskStatus = 'completed',1,0)) 'COMPLETED',
  COUNT(TaskId) 'TOTAL' FROM Task_Table GROUP BY YEAR(TargetEnd), MONTH(TargetEnd)

and the result is:

   YEAR  MONTH    ONGOING COMPLETED TOTAL
   2011  January   0        1        1
   2011  February  0        1        2
   2011  April     1        2        3

But my requirement is to add task #2 as part of ONGOING while task is still "ongoing" from the TargetEnd month up to the current month(February- April). The expected should be like this:

   YEAR  MONTH    ONGOING COMPLETED TOTAL
   2011  January   0        0        0
   2011  February  1        1        1
   2011  March     1        0        1
   2011  April     2        2        4

Can you give me an idea on how can I accomplish this?开发者_运维百科 I appreciate any help. Thanks.

I edited the expected result above. There should have been no count for January as the count is solely based on the TargetEnd's month.

If I uderstand correctly...
- If a record shows as Completed, it needs to contribute 1 to the completed value, and only for the month in which it completed.
- If a record shows as Ongoing, it needs to contribute 1 to the ongoing value, for every month since it started.

The first issue that you have is that the month March, therefore doesn't have an equivilent record in the input data. My preferred way of dealing with that is to create a lookup table of months.

SELECT
  lookup.month,
  SUM(CASE WHEN task_table.TaskStatus = 'Completed' THEN 1 ELSE 0 END) AS completed,
  SUM(CASE WHEN task_table.TaskStatus = 'Ongoing'   THEN 1 ELSE 0 END) AS completed,
  COUNT(task_table.TaskStatus) AS total
FROM
  lookup
LEFT JOIN
  task_table
    ON
    (
      task_table.TaskStatus = 'Completed'
      AND lookup.month <= task_table.Target_End
      AND lookup.month >  task_table.Target_End - INTERVAL '1 month'
    )
    OR
    (
      task_table.TaskStatus = 'Ongoing'
      AND lookup.month <= task_table.Target_End
      AND lookup.month >  task_table.Start_Date - INTERVAL '1 month'
    )
WHERE
      lookup.month >= '2011-01-01'
  AND lookup.month <= '2011-04-01'
GROUP BY
  lookup.month