Results 1 to 2 of 2
  1. #1
    Join Date
    May 2005

    Unanswered: Group dates into 7 days period

    I have a table which contatins the listing of revenues earned per day since the last 3 years. The fields that would be relevant to my ques are - Date and Amount

    Now I need to create a query to group my data in the following way i.e.
    the generated revenues be grouped first by month and then by a 7 day period: eg.

    Month Weeks Collection Count
    Sep 2005 Week1 2909900 78
    Sep 2005 Week2 3909900 55
    Sep 2005 Week3 6909500 45
    Sep 2005 Week4 9906600 90
    Sep 2005 Week5 306600 10

    and so on..
    I can do grouping by month and by weeks by adding a seprate month and week coulmn using format function, but by dong this way a week can be split into two months
    What I need is it to group days by 7 days period. So 1-7 would be week1, 8-14 be week2 and so on. and the last week period could vary between 0-3 depending on which month it is. It does not matter as to wether 1st day of the week is a sun or mon.
    I hope I have made my requirement clear and hope some expert could guide me into the correct way of doing it.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    if the week number itself has no implicit meaning (ie its not ited to you accounting calendar) you should be able derive a week number using the datepart() function, that weeknumber and a year should uniquely idsentify any 7 day period.

    so you could use a user written function to generate your week (year number say YYYY-WW) which you could then use in your further processing.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts