How to get a date range for a week?

gdkve9

Hello friends,

I have a question on date topic..

How to get a date range for a Week?

Example:
1. User has inputed the Year=2011 and Month=APR in the Options
2. Now I have to show in the report, week wise customer sales in the APR Month of 2011
3. For this I need to know what is the date range of Week1, Week2, Week3... so that I could apply the Week1 date range on the customer ledgers for Week1 sales and soon.

Your suggestions would greatly help in resolving this.

Awaiting on the replies.. Thanks in advance.

Belias

just to make it clear, can you confirm this before we think about a solution?

Example: this month (may 2011)
you want to have these ranges (american notation)
1. 05/01/11..05/01/11
2. 05/02/11..05/08/11
3. 05/09/11..05/15/11
4. 05/16/11..05/22/11
5. 05/23/11..05/29/11
6. 05/30/11..05/31/11

Correct?

gdkve9

Exactly Mirko :-k

vijay_g

Use Date table as data item with "Period Type" Week.

Belias

vijay_g wrote:

Use Date table as data item with "Period Type" Week.

With this approach, you won't get the first and the last period of my example. (only full weeks are took into account)
but indeed, you have to manage the date table (i need some time to think about it, maybe on lunch break, because i'm busy :whistle: )

Sog

Belias wrote:

vijay_g wrote:

Use Date table as data item with "Period Type" Week.

With this approach, you won't get the first and the last period of my example. (only full weeks are took into account)
but indeed, you have to manage the date table (i need some time to think about it, maybe on lunch break, because i'm busy :whistle: )

why not?
ending date > first of the month & starting date < first of the next month.
You'll get all the weeks you need. A check of the first and last week should be enough to have all the filters one needs.

Belias

Sorry, I meant, filtered date table is not enough...I wrote: "but indeed, you have to manage the date table".

I think we have to play with:
-first day of month/first day of first week
-last day of last week/last day of month

Sog, you meat this, didn't you?

gdkve9

Mirko and Sog,

I was just wondering if I could not arrive at exactly the ranges mentioned by Mirko in his example

Belias

gdkve9 wrote:

Mirko and Sog,

I was just wondering if I could not arrive at exactly the ranges mentioned by Mirko in his example

What does it mean? :?

gdkve9

I am struggling to arrive at all the date ranges of all the weeks in a month :?

vijay_g

Belias wrote:

Sorry, I meant, filtered date table is not enough...I wrote: "but indeed, you have to manage the date table".

I think we have to play with:
-first day of month/first day of first week
-last day of last week/last day of month

Sog, you meat this, didn't you?

Exactly..
it would be better to insert all week in temp table(date).
is not this right?

Sog

Fahn
Pseudocode

date.setrange(type,date.type::week);
date.setfilter("ending date",'>%1',the first of the month);//american thingy=05/31/2011
date.setfilter("starting date",'<%1',the last of the month);//american thingy=05/01/2011
if date.findset then
repeat
  if date."starting date" < the first of the month then
    date."starting date" := the first of the month;
  if date."ending date" > the end of the month then
    date."ending date" := the end of the month;
  filter[x] := format to string(date."starting date") + .. + format to string(date."ending date");
until date.next = 0;

filter[1] = 05/01/11..05/01/11
filter[2] = 05/02/11..05/08/11
filter[3] = 05/09/11..05/15/11
filter[4] = 05/16/11..05/22/11
filter[5] = 05/23/11..05/29/11
filter[6] = 05/30/11..05/31/11

btw Belias what do you mean with: Sog you mea(n)t this? I'm much more of a cheesy person

Belias

Sog wrote:

date.setrange(type,date.type::week);
date.setfilter("ending date",'>%1',the first of the month);//american thingy=05/31/2011
date.setfilter("starting date",'<%1',the last of the month);//american thingy=05/01/2011
.....

btw Belias what do you mean with: Sog you mea(n)t this? I'm much more of a cheesy person

...did you try to do these filters on a form based on date? you'll get only 4 lines, so your repeat/until loop will create an array of 4 element max (i know it's pseudo code, but i want to avoid misunderstandings). That's why i said that we have to play around a bit...

P.S.: i prefer meat, anyway, i don't like cheese

gdkve9

Wonderful Sog =D>

I could now understand what Mirko was saying about "Playing with date" :thumbsup:

Thankyou all guys

Sog

I've tested this out quickly and I get 6 lines.
But to be entirely correct it should be <= and >= for the starting/ending date filters

Belias

#-o
i was filtering Starting date 010511..310511
what a fool! ](*,)

einsTeIn.NET

Even with CALCDATE that's possible. Must be something like this...

MyDate := TODAY;
REPEAT
  DateExpression := STRSUBSTNO('<-CM + %1W>',i);

  FirstDate := CALCDATE('<-CW>',CALCDATE(DateExpression,MyDate));
  IF FirstDate < CALCDATE('<-CM>',MyDate) THEN
    FirstDate := CALCDATE('<-CM>',MyDate);

  LastDate := CALCDATE('<CW>',CALCDATE(DateExpression,MyDate));
  IF LastDate > CALCDATE('<CM>',MyDate) THEN
    LastDate := CALCDATE('<CM>',MyDate);

  MESSAGE('%1..%2',FirstDate,LastDate);
  i += 1;
UNTIL (LastDate = CALCDATE('<CM>',MyDate));

catiamatos1991

einsTeIn.NET wrote: »

Even with CALCDATE that's possible. Must be something like this...

MyDate := TODAY;
REPEAT
  DateExpression := STRSUBSTNO('<-CM + %1W>',i);

  FirstDate := CALCDATE('<-CW>',CALCDATE(DateExpression,MyDate));
  IF FirstDate < CALCDATE('<-CM>',MyDate) THEN
    FirstDate := CALCDATE('<-CM>',MyDate);

  LastDate := CALCDATE('<CW>',CALCDATE(DateExpression,MyDate));
  IF LastDate > CALCDATE('<CM>',MyDate) THEN
    LastDate := CALCDATE('<CM>',MyDate);

  MESSAGE('%1..%2',FirstDate,LastDate);
  i += 1;
UNTIL (LastDate = CALCDATE('<CM>',MyDate));

With this piece of code I can get the last week range ?