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