Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2002
    Posts
    6

    Unanswered: weekly to monthly

    I have a data table that contains weekly data.
    The table has a period date column which tells the start of the week.
    Some users would like to view the data as monthly numbers...

    Are there any SQL tricks to allow me to easily show weekly data as
    monthly data?

    Here's a sample of the table format...

    Item | Date | Value
    123 | 02/03/2003 | 100
    123 | 02/09/2003 | 60
    ...
    123 | 09/19/2003 | 60
    124 | 02/03/2003 | 80
    124 | 02/09/2003 | 40
    ...
    124 | 09/19/2003 | 40

  2. #2
    Join Date
    Feb 2003
    Posts
    22
    i'm not sure that I undestood you need...
    why don't you make a group by on first colun and month of the second column the summing the third?
    There is the problem of the year...then you have to add year ....
    Cristiana
    Data Base Administrator
    Bologna
    Italy

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: weekly to monthly

    You don't say what DBMS you are using, and the solution to this is DBMS-specific. I will show you the Oracle solution, other databases will probably have a similar (but different) function:

    SELECT item, TRUNC( datecol, 'MONTH' ) month, SUM( value ) month_value
    FROM mytable
    GROUP BY item, TRUNC( datecol, 'MONTH' );

    Item | Month | Month_Value
    123 | 02/01/2003 | 160
    ...
    123 | 09/01/2003 | 60
    124 | 02/01/2003 | 120
    ...
    124 | 09/01/2003 | 40

    This has "truncated" the date to the 1st of the month.
    If you wanted the month displayed differently, you would format it. e.g.

    TO_CHAR( datecol, 'MM/YYYY' )

    would show 02/2003, 09/2003 etc. Again, this is Oracle-specific, other DBMSs' functions will be a bit different.

    If you are not on Oracle, search your DBMS's documentation for "date functions".

  4. #4
    Join Date
    Oct 2002
    Posts
    6
    I am using ORACLE 8.1.4,

    With the solution given, I am unable to account for a week that falls between two months.
    For example if the week started on 09/26/2003 and qty was 70, then
    because 4 days of the week fall in September and three days fall in October, I would like to get some allocation between....Currently, I am not very picky on the allocation used....

    Is there something I can do here......

    Thanks in advance!!!

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You will need to do the UNION of 2 queries:
    1) Query for month in which the week begins - returning value * fraction of week if it goes over the month end
    2) Query for month in which the week ends (if that will be different) - returning value * fraction of week

    I believe this will do it:

    SELECT month, SUM(month_value) FROM
    (
    SELECT TRUNC(datecol,'MONTH') month, value*LEAST( LAST_DAY(datecol)-datecol+1, 7 ) / 7 month_value
    FROM mytable
    UNION ALL
    SELECT TRUNC(datecol+6,'MONTH') month, value*(datecol+6-TRUNC(datecol+6,'MONTH')+1) / 7 month_value
    FROM mytable
    WHERE TRUNC(datecol+6,'MONTH') > TRUNC(datecol,'MONTH')
    )
    GROUP BY month;

  6. #6
    Join Date
    Oct 2002
    Posts
    6

    Thank you

    That was a perfect solution, thanks for all your help....

Posting Permissions

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