Group by Week 2

[mdj3366]

Hi,

Using CR 2008 or XI, SQL database. I need to be able to group on a datetime field. This is the data that i am currently getting:

03/05/2010 5:31:28 PM
03/05/2010 3:33:15 PM
03/05/2010 3:55:10 PM
Week ending 3/1/2010 5:31:28PM # of validations 3

The 03/05/2010 activity is grouped under weekending 03/01/2010. I am using a formula to group on:{CallLog.call_date}-dayofweek({CallLog.call_date})+2
The section is being printed for each week. What i want to see is:
03/05/2010 5:31:28 PM
03/05/2010 3:33:15 PM
03/05/2010 3:55:10 PM
Week ending 3/7/2010 23:59:59PM # of validations 3

Any help will be greatly appreciated! Thank you.

 

[mdj3366]

One other thing - my week runs from Monday thru Sunday. Thanks.

 

[lbass]

{CallLog.call_date}-dayofweek({CallLog.call_date},crMonday)+1

-LB

 

[mdj3366]

Hi LB,

I tried that and it is returning the same incorrect date - the weekending of 3/7/10 with data from 3/8-3/11. I tried using in a crosstab, and the weekending date for this week is 3/13, but i need it to be 3/14. The row name uses this formula: {CallLog.call_date}-dayofweek({CallLog.call_date},crMonday)+1. The summarized field is a count of call date. It is grouped descending, for each week, the last day in the period.

 

[lbass]

Sorry, I gave you the week starting date. It should be:

{CallLog.call_date}-dayofweek({CallLog.call_date},crMonday)+7

-LB

 

[mdj3366]

That worked! Thanks, LB.