1

I need a where clause on task..where its calculates the count of tasks every week between Friday and Thursday..

I know it will be a Aggregate query.I am confused how to calculate every-week..What will be the where clause..I don't need "THIS_WEEK" condition..I need to calculate the task every week between Friday and Thursday.

Please help with the logic of dates so it works dynamically everyweek.

Please its urgent

List<aggregateResult> results1=[Select ownerId, count(id) totalCount from task group by ownerId
4
  • What day are you running your query? Is this really just the last 7 days? Aug 2 '15 at 18:14
  • Its a VF page so its run on constructor..I have a report on VF page..That shows count of tasks..I want to show the count of tasks between Friday and Thursday every-week...SO i need a where clause..so its like when i open the VF page..Its calculates the count of the current week from Friday to Thursday..
    – miku
    Aug 2 '15 at 18:23
  • You will need to determine today's date, determine where it lands in the week, find out how many days to friday and add that to today to create your end date. After that you need to remove 7 days from your end date to get your start date. You can then do your SOQL query to get all tasks in the date range between start and end date. Aug 2 '15 at 18:26
  • I am new to SF..That seems like perfect solution..Can you help me with date functions.?I mean some sample APEX for this calculation...?I will really appreciate...:)
    – miku
    Aug 2 '15 at 18:30
2

What you are asking for requires some prep before your SOQL query (I have not validated the actual code so may require some tweaking):


DateTime today = datetime.now();
Integer dayOfWeek = today.format('u'); //get integer day of week 1=Mon, 7=Sun
Integer daysToThursday;
if(dayOfWeek = 5) daysToThursday = 6; //Fri
else if(dayOfWeek = 6) daysToThursday = 5; //Sat
else if(dayOfWeek = 7) daysToThursday = 4; //Sun
else if(dayOfWeek = 1) daysToThursday = 3; //Mon
else if(dayOfWeek = 2) daysToThursday = 2; //Tue
else if(dayOfWeek = 3) daysToThursday = 1; //Wed
else if(dayOfWeek = 4) daysToThursday = 0; //Thur
Date endDate = today + daysToThursday;
Date startDate = endDate - 6;

List<aggregateResult> results1 = [SELECT ownerId, count(id) totalCount FROM Task WHERE ActivityDate >= :startDate AND ActivityDate <= :endDate GROUP BY ownerId];
2
  • Getting an error...I could't find any method like this...Integer dayOfWeek = today.format('u'); Save error: Method does not exist or incorrect signature: [Date].format(String)
    – miku
    Aug 2 '15 at 18:36
  • Updated code, take a look. Aug 2 '15 at 18:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.