Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2008
    Posts
    7

    Unanswered: Get first day of privous week and first day of current week to get weekly data

    I am using IBM DB2 version 8 on Unix OS.

    I have a table with Date, Time fields as well as DateTime field.

    I need to find our total number of Claims opened on weekly basis. My 'WEEKLY' logic is little complex here.

    If I run the query on Monday it should give me total no. of Claims opened in last week group by last week MON, TUE, WED, THU, FRI

    If I run the query on Tuesday it should give me only the current week's MON data. If I run on Wed it should give me current week's MON, TUE data
    So on until Friday.

    Can any body help me on this. I did this kind of query in MS Access 2003 db.
    Now I need to implement the same thing in DB2

    Please advise

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    To get the monday of the current week, you take the current date, apply the DAYOFWEEK() function to it and then substract this number of days from CURRENT DATE again:
    Code:
    VALUES CURRENT DATE - ( DAYOFWEEK(CURRENT DATE) - 1 ) DAYS
    Throw in a CASE expression for handling the special case of executing the query on a Monday, and off you go. The rest is straight-forward SQL.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Aug 2008
    Posts
    7
    Quote Originally Posted by stolze
    To get the monday of the current week, you take the current date, apply the DAYOFWEEK() function to it and then substract this number of days from CURRENT DATE again:
    Code:
    VALUES CURRENT DATE - ( DAYOFWEEK(CURRENT DATE) - 1 ) DAYS
    Throw in a CASE expression for handling the special case of executing the query on a Monday, and off you go. The rest is straight-forward SQL.
    CURRENT DATE - ( DAYOFWEEK(CURRENT DATE) - 2 ) DAYS is giving me Monday of current week. How to get MON-FRI of last week ??

    I am going to use this query in Business Objects. User enters their required date in a Prompt. If they enter Monday's date (any month) it should provide them the Claim count for last MON, TUE, WED, THU, FRI group by each Day. If they Enter date other than Monday, it should provide them the current weeks data except their entered date's data. For Ex: If they Enter Thursday Sep 04 2008, it should give results like this

    Mon Tue Wed
    13 2 89

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    Try those expressions:

    Lower boarder: DATE(NEXT_DAY ( DATE(*promted_date*)-8 DAYS,'MON' ))

    Upper boarder: MIN(DATE(NEXT_DAY( DATE(*promted_date*)-1 DAYS,'MON' )-3 DAYS), DATE(*promted_date*) )


    ( just to proof: )

    SELECT
    DATCOL,
    DATE(NEXT_DAY ( DATCOL -8 DAYS,'MON' )) AS LOWER,
    MIN(DATE(NEXT_DAY( DATCOL -1 DAYS,'MON' )-3 DAYS), DATCOL ) AS UPPER
    FROM
    ( table with date column DATCOL )

    returns

    DATCOL.... LOWER..... UPPER
    ---------- ---------- ----------
    30.08.2008 25.08.2008 29.08.2008
    31.08.2008 25.08.2008 29.08.2008
    01.09.2008 25.08.2008 29.08.2008
    02.09.2008 01.09.2008 02.09.2008
    03.09.2008 01.09.2008 03.09.2008
    04.09.2008 01.09.2008 04.09.2008
    05.09.2008 01.09.2008 05.09.2008
    06.09.2008 01.09.2008 05.09.2008
    Last edited by umayer; 09-03-08 at 11:02.

  5. #5
    Join Date
    Jun 2013
    Posts
    1

    Thumbs up Get the date of the first day of current and previous week

    Here, a sql statement you can use to get the date of the previous mondays:

    Code:
    select 
    (CURRENT DATE - ( DAYOFWEEK(CURRENT DATE) - 2 ) DAYS)                      as MONDAY_OF_CURRENT_WEEK_DATE,
    YEAR(CURRENT DATE - ( DAYOFWEEK(CURRENT DATE) - 2 ) DAYS) * 10000 + MONTH(CURRENT DATE - ( DAYOFWEEK(CURRENT DATE) - 2 ) DAYS) * 100 + DAY(CURRENT DATE - ( DAYOFWEEK(CURRENT DATE) - 2 ) DAYS) as MONDAY_OF_CURRENT_WEEK_INTEGER,
    ((CURRENT DATE - 7 DAYS) - ( DAYOFWEEK(CURRENT DATE - 7 DAYS) - 2 ) DAYS)   as MONDAY_OF_LAST_WEEK_DATE,
    YEAR((CURRENT DATE - 7 DAYS) - ( DAYOFWEEK(CURRENT DATE - 7 DAYS) - 2 ) DAYS) * 10000 + MONTH((CURRENT DATE - 7 DAYS) - ( DAYOFWEEK(CURRENT DATE - 7 DAYS) - 2 ) DAYS) * 100 + DAY((CURRENT DATE - 7 DAYS) - ( DAYOFWEEK(CURRENT DATE - 7 DAYS) - 2 ) DAYS) as MONDAY_OF_LAST_WEEK_INTEGER
    from any_table
    Here, a sql statement you can use to get the date of the previous sundays:

    Code:
    select 
    (CURRENT DATE - ( DAYOFWEEK(CURRENT DATE) - 1 ) DAYS)                      as SUNDAY_OF_CURRENT_WEEK_DATE,
    YEAR(CURRENT DATE - ( DAYOFWEEK(CURRENT DATE) - 1 ) DAYS) * 10000 + MONTH(CURRENT DATE - ( DAYOFWEEK(CURRENT DATE) - 1 ) DAYS) * 100 + DAY(CURRENT DATE - ( DAYOFWEEK(CURRENT DATE) - 1 ) DAYS) as SUNDAY_OF_CURRENT_WEEK_INTEGER,
    ((CURRENT DATE - 7 DAYS) - ( DAYOFWEEK(CURRENT DATE - 7 DAYS) - 1 ) DAYS)   as SUNDAY_OF_LAST_WEEK_DATE,
    YEAR((CURRENT DATE - 7 DAYS) - ( DAYOFWEEK(CURRENT DATE - 7 DAYS) - 1 ) DAYS) * 10000 + MONTH((CURRENT DATE - 7 DAYS) - ( DAYOFWEEK(CURRENT DATE - 7 DAYS) - 1 ) DAYS) * 100 + DAY((CURRENT DATE - 7 DAYS) - ( DAYOFWEEK(CURRENT DATE - 7 DAYS) - 1 ) DAYS) as SUNDAY_OF_LAST_WEEK_INTEGER
    from any_table

Posting Permissions

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