How to get a date range for a week?

gdkve9gdkve9 Member Posts: 161
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.
Dilip
Falling down is not a defeat..defeat is when you refuse to get up.

Answers

  • BeliasBelias Member Posts: 2,998
    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?
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • gdkve9gdkve9 Member Posts: 161
    Exactly Mirko :-k
    Dilip
    Falling down is not a defeat..defeat is when you refuse to get up.
  • vijay_gvijay_g Member Posts: 884
    Use Date table as data item with "Period Type" Week.
  • BeliasBelias Member Posts: 2,998
    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: )
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • SogSog Member Posts: 1,023
    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.
    |Pressing F1 is so much faster than opening your browser|
    |To-Increase|
  • BeliasBelias Member Posts: 2,998
    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?
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • gdkve9gdkve9 Member Posts: 161
    Mirko and Sog,

    I was just wondering if I could not arrive at exactly the ranges mentioned by Mirko in his example :(
    Dilip
    Falling down is not a defeat..defeat is when you refuse to get up.
  • BeliasBelias Member Posts: 2,998
    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? :?
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • gdkve9gdkve9 Member Posts: 161
    I am struggling to arrive at all the date ranges of all the weeks in a month :?
    Dilip
    Falling down is not a defeat..defeat is when you refuse to get up.
  • vijay_gvijay_g Member Posts: 884
    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? :)
  • SogSog Member Posts: 1,023
    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 :mrgreen:
    |Pressing F1 is so much faster than opening your browser|
    |To-Increase|
  • BeliasBelias Member Posts: 2,998
    edited 2011-05-26
    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 :mrgreen:

    ...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 :)
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • gdkve9gdkve9 Member Posts: 161
    Wonderful Sog =D>

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

    Thankyou all guys :D
    Dilip
    Falling down is not a defeat..defeat is when you refuse to get up.
  • SogSog Member Posts: 1,023
    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
    |Pressing F1 is so much faster than opening your browser|
    |To-Increase|
  • BeliasBelias Member Posts: 2,998
    #-o
    i was filtering Starting date 010511..310511
    what a fool! ](*,)
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • einsTeIn.NETeinsTeIn.NET Member Posts: 1,050
    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));
    
    "Money is likewise the greatest chance and the greatest scourge of mankind."
  • catiamatos1991catiamatos1991 Member Posts: 158
    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 ?
Sign In or Register to comment.