Sysdate Minus 52 weeks

building semantic layer
1

I need to put in a condition to pull the current week and 52 previous weeks.
The object that has weeks is setup as a number that has YYYYMMDD. Any ideas on
how this can be done?
Thanks,
Shirley Knight shkk@ti.com Texas Instruments Business Objects Support

Listserv Archives (BOB member since 2002-06-25)

2

Shirley, >

I accomplished this with the following WHERE clause in a condition [database is Oracle 7.3]:

@Select(<>) BETWEEN trunc(add_months(last_day(sysdate),-12)) AND to_date(to_char(add_months(last_day(sysdate),-1),'MM/DD/YYYY ')||‘23:59.59’,‘MM/DD/YYYY HH24:MI.SS’)

Of course, you should modify it to conform to your numeric date format.  The universe object I used had a DATE datatype, and you will need to convert your comparison dates in the WHERE clause to numbers to match your object.  One thing you don’t need if you are working with YYYYMMDD format you specified is the time stamp padding.

Steven Jones >
Consultant to BT Office Products International
sjones@btopi.com

Approved-By: thompson@POBOX.UPENN.EDU

Date: Fri, 15 Jan 1999 16:17:04 -0500

Shirley,

We have done something similar with a Sybase database. Inefficient, but it works.

  1. Create a view on your DBMS to return the beginning of your period(“create view v_ThisYear as select ThisYear=dateadd(week,-52,getdate())”)
  2. Include the view in your universe (views come up with the list of tables).
  3. Join the view to the table that contains the date you want to restrict. Instead of equality, choose inequality (so the ThisYear < the date you want to restrict).
  4. Create a condition object: where v_ThisYear.ThisYear > “1/1/1900”

When you include this condition in your report, it should force the date to be included in the previous 52 weeks.

Listserv Archives (BOB member since 2002-06-25)

3

Thanks,

Shirley

Listserv Archives (BOB member since 2002-06-25)

4

Suppose today is 19990126 (26-JAN-99) and you want to pull all of the data
for the week of 19980125 (25-JAN-98) through the current week, which ends on
19990130 (30-JAN-99).

@Select(date_object_name) BETWEEN To_Number( To_Char(
Trunc(sysdate-(52*7),'D'), 'YYYYMMDD' ) )
AND To_Number( To_Char( Trunc(sysdate,'D')+6, 'YYYYMMDD' ) )

If the current date is 26-JAN-99, this will pull all data where Date_Object
is between 19980125 and 19990130.

Erich Hurst
Compaq Computer Corporation

“It is so easy to break eggs without making omlettes.” – C.S. Lewis

Listserv Archives (BOB member since 2002-06-25)

5

Okay, I am trying to do something similar only in DB2.
Here is my formula but it is not parsing and I know I am missing or doing something incorrect but can’t find it after starring it for sometime:

date(DB2ADMIN.IA_DATES.CAL_DAY_DT)  BETWEEN  trunc(add_months(last_day(CURRENT DATE -12 months)))  AND to_date(to_char(add_months(last_day(CURRENT DATE) -1 day,'MM/DD/YYYY')||'23:59:59','MM/DD/YYYY HH24:MI:SS')

help is deeply appreciated.

di652 (BOB member since 2005-02-24)

6

One thing I saw is that last_day () is having a problem.

I thought that last_day() was a DB2 function!?
I cannot seem to get this to work or find anyone who has done this with DB2.
Cannot tell you how much I hate this combination of using DB2 and LYNX…

:nonod:

di652 (BOB member since 2005-02-24)

7

Hi,

Try having a look here: http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html

Maybe it helps a bit.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

8

Don’t know about db2, but I’d expect the syntax to be broadly similar to oracle:

last_day: last_day(date)
add_months: add_months(date,n)

You’ve got -1 day and -12 months in your syntax and it also looks like some of your brackets might be in the wrong place?

But db2 might be completely different, in which case ignore me…

Debbie

Debbie :uk: (BOB member since 2005-03-01)