Solved: Testing current date...

[MaximS]

hi,

Can anyone advise me how to test if current day is Sunday?

If yes I'd like to show the data from last week otherwise data from current week.

So far my code looks like that

SELECT 
MNM.DIM_DATE.T_DATE, 
SUM(MNM.FACT_RCD.COUNT_UNITS) 
FROM 
MNM.DIM_DATE, 
MNM.FACT_RCD 
WHERE 
( MNM.FACT_RCD.T_DATE=MNM.DIM_DATE.T_DATE ) 
AND 
( MNM.DIM_DATE.CURRENT_WEEK='0' ) 
GROUP BY 
MNM.DIM_DATE.T_DATE

And for current week I'm using:

( MNM.DIM_DATE.CURRENT_WEEK='C' )

[stanl]

what database are you using?

[MaximS]

I am trying to build a querry in SAP Business Object XI R2

I've been playing a bit with to_char(sysdate, 'd') with no luck so far.

[MaximS]

OK finaly I have done it. Code which works for me looks as follows:

 
SELECT 
  MNM.DIM_DATE.T_DATE, 
  SUM(MNM.FACT_RCD.COUNT_UNITS) 
FROM 
  MNM.DIM_DATE, 
  MNM.FACT_RCD 
WHERE 
  ( MNM.FACT_RCD.T_DATE=MNM.DIM_DATE.T_DATE  ) 
  AND  
  ( 
(to_char(sysdate,'DY')='SUN' 
AND 
MNM.DIM_DATE.T_DATE 
BETWEEN 
sysdate-8 and sysdate-1) 
OR 
(to_char(sysdate,'DY')<>'SUN' 
AND 
MNM.DIM_DATE.T_DATE 
BETWEEN 
NEXT_DAY(sysdate-8,'Saturday') 
AND 
sysdate-1) 
  ) 
GROUP BY 
  MNM.DIM_DATE.T_DATE