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

    Unanswered: First Monday of previous

    Hi,

    I need help with a query which generates data for the First Monday of previous month to the last Monday of the previous month.

    I used this condition, which works;
    where date_ column between '2014-03-3 07:00:00' and '2014-03-31 07:00:00'

    but now i am setting up a view to have automatically email the report every first of the month, so i need to use a general condition like below;

    where date_column between (current date - 1 month) and (current date-1 day)
    and (dayofweek (inv_start_date)= 2)

    Please help.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    An idea may be...

    Step 1: Find "last day of two months before" and "last day of previous month - 7 days".
    For example:
    "last day of two months before"
    LAST_DAY(current date - 2 MONTHs)
    LAST_DAY - IBM DB2 9.7 for Linux, UNIX, and Windows

    Step 2: Apply NEXT_DAY scalar function.
    NEXT_DAY - IBM DB2 9.7 for Linux, UNIX, and Windows

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    where date_column between (current date - 1 month) and (current date-1 day)
    and (dayofweek (inv_start_date)= 2)
    Will only report on day of week = 2 for the entire month.

    Do you have a calendar table? Use that. Get the min and max monday dates for your month.
    Though I must admit, your data will not be accurate with actual numbers. Take the months of March and April for instance. You will have the March report give you data for dates 3/3/14 - 3/31/14 and then your April dates will be 4/7/14 - 4/28/14. Missing a total of 10 days in those 2 months of reports.
    Dave

  4. #4
    Join Date
    Oct 2013
    Posts
    11
    The data on that table is from Monday to Monday. (March 3-10,March 10-17 etc). So only
    "where date_column between (current date - 1 month) and (current date-1 day) "seems to work.i checked "select (current date - 1 month) from sysibm.sysdummy1" and it returns 2014-03-24.So ideally if the report is run on the first day of any month,it should provide the data for the previous month as per the where condition.

Posting Permissions

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