MySQL query error...it does not display the exact data values

I want to ask if what is wrong (and probably ask for suggestions on how to better do it) with my MySQL query? I'm trying to generate a daily report within a range of 5days... the problem is it does not display the exact datas, It fills up days that does not have data in it.

This is the case of the MySQL query example below: In my database, monday, wed and thursday are with datas.. but if I will run the reports using this query, tuesday will have an existing data which it does not actually has in my database. Although the IT, NonIt, and Total table count is correct. I have errors in counting the datas in my days only. please help me... I don't know where the errors already.

To better explain what I mean, here is the screenshot of the output result of my query: http://www.fileden.com/files/2011/7/27/3174077//daily.JPG

SELECT a.specialist_partner_ID, ss.first_name as SS, ssa.first_name as SSA 

,count(CASE WHEN a.receivedDate BETWEEN '2011-09-5' AND '2011-09-9' 
THEN a.job_order_number ELSE null END) As MON

,count(CASE WHEN a.receivedDate BETWEEN date_add('2011-09-5', INTERVAL 1 DAY) 
AND DATE_ADD('2011-09-9', INTERVAL 1 DAY) THEN a.job_order_number ELSE null END) As TUE 

,count(CASE WHEN a.receivedDate BETWEEN date_add('2011-09-5', INTERVAL 2 DAY) 
AND DATE_ADD('2011-09-9', INTERVAL 2 DAY) THEN a.job_order_number ELSE null END) As WED

,count(CASE WHEN a.receivedDate BETWEEN date_add('2011-09-5', INTERVAL 3 DAY)
AND DATE_ADD('2011-09-9', INTERVAL 3 DAY) THEN a.job_order_number ELSE null END) As THU 

,count(CASE WHEN a.receivedDate BETWEEN date_add('2011-09-5', INTERVAL 4 DAY) 
AND DATE_ADD('2011-09-9', INTERVAL 4 DAY) THEN a.job_order_number ELSE null END) As FRI

,count(case WHEN (a.receivedDate between '2011-09-5 00:00:00' and '2011-09-9 23:59:59' 
and jo.job_o开发者_StackOverflowrder_type LIKE 'IT') then a.job_order_number else null end) as IT 

,count(case WHEN (a.receivedDate between '2011-09-5 00:00:00' and '2011-09-9 23:59:59' 
and jo.job_order_type LIKE 'Non-IT') then a.job_order_number else null end) as NonIT 

,count(a.job_order_number) As Total FROM jo_partner a 

left join specialist_partner sp on a.specialist_Partner_ID = sp.specialistPartnerID       
left join staffing_specialist_asst ssa on sp.SSA_ID = ssa.SSA_ID 
left join staffing_specialist ss on sp.SS_ID = ss.SS_ID 
left join job_order jo on a.job_order_number = jo.job_order_number 
left join candidate_jo cjo on a.JO_partner_ID= cjo.jo_partner_ID 
left join candidate can on cjo.candidate_jo_ID= can.candidate_ID 
WHERE a.receivedDate BETWEEN '2011-09-5 00:00:00' AND '2011-09-9 23:59:59' 
GROUP BY a.specialist_partner_ID

---

I think you have wrong dates. For every day column you are selecting data from 5 days, this has no sense. So instead of:

count(CASE WHEN a.receivedDate BETWEEN date_add('2011-09-5', INTERVAL 1 DAY) 
AND DATE_ADD('2011-09-9', INTERVAL 1 DAY) THEN a.job_order_number ELSE null END) As TUE

You should have:

count(CASE WHEN a.receivedDate BETWEEN date_add('2011-09-5', INTERVAL 1 DAY) 
and DATE_ADD('2011-09-5', INTERVAL 2 DAY) THEN a.job_order_number ELSE null END) As TUE

Or even better:

count(CASE WHEN a.receivedDate >= date_add('2011-09-5', INTERVAL 1 DAY) 
and a.receivedDate < DATE_ADD('2011-09-5', INTERVAL 2 DAY) THEN a.job_order_number ELSE null END) As TUE

The same for the rest of the days.