Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2013
    Posts
    11

    Unanswered: Average of Fridays data for 4 weeks

    I am trying to find the average for Fridays data for a period of 4 weeks,3 months,6 months and 12 months.I am new to db2 and wondering how to use dayofweek and weekofmonth in my query. I have a base query which uses the timestamp (friday's date 07:00:00) to timestamp (Saturdays date 07:00:00).Now i have to do the average for 4 weeks to begin with.Can someone please help.
    Last edited by spetritia; 10-15-13 at 22:33.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    dayofweek
    DAYOFWEEK - IBM DB2 9.7 for Linux, UNIX, and Windows

    What is the definition of weekofmonth?

    There are functions for the week of the year.
    WEEK - IBM DB2 9.7 for Linux, UNIX, and Windows
    WEEK_ISO - IBM DB2 9.7 for Linux, UNIX, and Windows

    The returned values of each functions are different.
    For example
    Code:
    SELECT date_
         , WEEK(date_)      AS week
         , WEEK_ISO(date_)  AS week_iso
         , DAYOFWEEK(date_) AS dayofweek
         , VARCHAR(DAYNAME(date_) , 10) AS dayname
     FROM  Sample_data_in_week_iso_and_more
    ;
    ------------------------------------------------------------------------------
    
    DATE_      WEEK        WEEK_ISO    DAYOFWEEK   DAYNAME   
    ---------- ----------- ----------- ----------- ----------
    1997-12-28          53          52           1 Sunday    
    1997-12-31          53           1           4 Wednesday 
    1998-01-01           1           1           5 Thursday  
    1998-01-07           2           2           4 Wednesday 
    1998-02-01           6           5           1 Sunday
    1998-12-31          53          53           5 Thursday  
    1999-01-01           1          53           6 Friday    
    1999-01-04           2           1           2 Monday    
    1999-12-31          53          52           6 Friday    
    2000-01-01           1          52           7 Saturday  
    2000-01-03           2           1           2 Monday    
    2000-02-01           6           5           3 Tuesday   
    2000-12-31          54          52           1 Sunday    
    
      12 record(s) selected.
    I have a base query which uses the timestamp (friday's date 07:00:00) to timestamp (Saturdays date 07:00:00).
    Would you publish the query to show how to specify "friday's date 07:00:00" and "Saturdays date 07:00:00".
    Last edited by tonkuma; 10-15-13 at 22:57.

  3. #3
    Join Date
    Oct 2013
    Posts
    11

    Average of Fridays data for 4 weeks

    Hey thank you so much for the info;it was very helpful.

    Here's the query with the timestamp from 7:00 to 7:00;

    select field-names from tablename 1 t1
    where (dayofweek(date-time)=6 )
    and date-time between timestamp('2013-09-1 07:00:00') and timestamp('2013-09-30 07:00:00')

    I face an issue with this,everything works fine but the data i need is from Friday 7:00 am to Saturday 7:00 am(and its considered to be Friday's data),so when i use dayofweek=6 the data after 12:00 midnight will not be included.Ideally i need to add 7 hours from 12:00 midnight to the dayofweek.do you have any ideas for this?

    so it should be like ; -- where (dayofweek(date-time)=6 ) **from 7am to (dayofweek(date-time=7 ) **until 7:00am --
    Last edited by spetritia; 10-16-13 at 20:41.

  4. #4
    Join Date
    Nov 2004
    Posts
    67
    Try
    ...
    where
    date(timestamp_field) between date(friday_date) and date(saturday_date) and
    ((dayofweek(timestamp_field)=6 and hour(timestamp_field)>=7) or (dayofweek(timestamp_field)=7 and hour(timestamp_field)<7))
    ...

    It doesn't solve the question how to get data from last 4 Fridays but gives you data from Friday 7 am to Saturday 7 am.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Something like:

    Code:
    select some_cols, avg(some_amt)
    from my_table
    where timestamp_field >= current timestamp - 29 days
      and (dayofweek(timestamp_field)=6
         and hour(timestamp_field)>=7)
           or (dayofweek(timestamp_field)=7
         and hour(timestamp_field)<7))
    group by some_cols
    Dave

  6. #6
    Join Date
    Oct 2013
    Posts
    11
    I tried this and it worked;

    where timestamp-field between timestamp('2012-09-01 07:00:00') and timestamp('2013-09-30 07:00:00')
    and ((dayofweek(timestamp-field)=6 and hour(timestamp-field)>=7) or (dayofweek(timestamp-field)=7 and hour(timestamp-field)<7))

Tags for this Thread

Posting Permissions

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