Calculating the first/last day of the week for the month

[KENTOY]

Hi,

I have a report that has a parameter which ask for the YEAR and PERIOD (month), can i ask how to the get the starting/ending coverage of the day in terms of weeks for that particular month?[/color red]

ex.

User enter 2006 (year) and 8 (period), the report would generate data starting from 07/30/2006 to 09/02/2006 since the report will be group into weeks.

Week1 - 07/30/2006 to 08/05/2006
Week2 - 08/06/2006 to 08/12/2006
Week3 - 08/13/2006 to 08/19/2006
Week4 - 08/20/2006 to 08/26/2006
Week5 - 08/27/2006 to 09/02/2006

thanks,
Ken

 

[dpatrickcollins]

//First define the begin date of the month:

local datevar begin := Date({?year}, {?month}, 01);

//Then back it up to first of that week:

begin := DateAdd("d", - DayOfWeek(begin) + 1, begin);

//Lastly make that your new parameter:

{table.date} >= begin

//You would do something similar with the end date param:

local datevar end := Date({?year}, {?month} + 1, 01); //first of next month
end := DateAdd("d", - 1, end); //bumps it back to end of current month
end:= DateAdd("d", 7 - DayOfWeek(begin) , end); //move to end of week
{table.date} <= end

 

[dgillz]

So how do the parameters values of 2006 and 8 logically translate into a start date of 7/30/2006? And why 5 weeks?


Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[synapsevampire]

How do we determine which is the first and last week of the month, is it if the majority of the days are in the month, with the start day for each week being Sunday?

-k

 

[dpatrickcollins]

Those are good questions. In my case, I arbitrarily assumed that Kentoy wanted to include the the outlying weeks (beginning with Sunday). Additional logic could be implemented to exclude less-than-half partial weeks, etc., pretty easily. My hunch is that the users simply do not want to see "inaccurate subtotals" for the outlying weeks.

 

[synapsevampire]

Well I'll just address part of dpats formulas then, once you determine what you really need, post back:

This will error in December as the month will be 13:

local datevar end := Date({?year}, {?month} + 1, 01); //first of next month

use:

Dateserial({?year}, {?month} + 1, 01)

-k