Last post Jul 08, 2014 08:37 AM by PatriceSc
Jul 08, 2014 07:45 AM|kishore13|LINK
select p.project_name,u.first_name as ProjectManager,a.first_name as leadDeveloper,b.first_name as leadQA
,sum(case when DATE_SUB(m.actual_start,INTERVAL 6 DAY) = '2014-06-24' then worked else 0 end) as '1'
,sum(case when DATE_SUB('2014-06-23', INTERVAL 6 DAY) = '2014-06-17' then worked else 0 end) as '2'
,sum(case when DATE_SUB('2014-06-16', INTERVAL 6 DAY) = '2014-06-10' then worked else 0 end) as '3'
,sum(case when DATE_SUB('2014-06-09', INTERVAL 6 DAY) = '2014-06-03' then worked else 0 end) as '4'
,sum(case when DATE_SUB('2014-06-02', INTERVAL 6 DAY) = '2014-05-27' then worked else 0 end) as '5'
,sum(case when DATE_SUB('2014-05-26', INTERVAL 6 DAY) = '2014-05-20' then worked else 0 end) as '6'
,sum(worked) as TotalHours
from tbl_req_task_map m join tbl_project p on m.project_id=p.project_id
join tbl_user u on p.project_manager=u.user_id
join tbl_user a on p.lead_developer=a.user_id
join tbl_user b on p.lead_qa=b.user_id where m.actual_start='2014-06-30'
group by p.project_id,u.first_name,a.first_name,b.first_name order by p.project_name;
in the column 1 i want the sum of worked hours between 2014-06-30 and 2014-06-24 and like that in column 2 to column 6 i want the same result but i am not getting between dates.
Jul 08, 2014 08:21 AM|JoyceW|LINK
You're using a sub; DATE_SUB
What is this sub doing?
Jul 08, 2014 08:24 AM|kishore13|LINK
date_sub is for subtraction for 6 days interval
Jul 08, 2014 08:37 AM|PatriceSc|LINK
I don't really get your current substraction based logic. I'm not familiar with MySQL but it should be close from SQL Server it could be something such as:
SUM(CASE WHEN m.actual_start>='2014-06-24' AND m.actual_start<'2015-06-31' THEN worked else 0 END) As '1',
SUM(CASE WHEN m.actual_start>='2014-06-17' AND m.actual_start<'2015-06-24' THEN worked else 0 END As '2', etc...
Basically my understanding is that you want to sum up values based on the "slice" in which they are. So if actual_start is in inside the slice I want I take into account the value else I'm using 0 instead. Is this what you want ? The later test uses < against
the next day so that the value is taken into account even if it has hours (ie 2015-06-30 10:00 PM goes into the slice that ends before 2015-06-31 at 0 AM.