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

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:
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

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