View in Oracle to display rows from last weekday
I need to make a view in Ora开发者_开发问答cle that will display all rows from last weekday.
So for Tuesday it should be like this cause I need all entries from Monday:
select * from table_1 where to_char(Mydate,'yyyy-mm-dd') = to_char(sysdate-**1**,'yyyy-mm-dd');
But if it is Monday then I need all entries from Friday.
select * from table_1 where to_char(Mydate,'yyyy-mm-dd') = to_char(sysdate-**3**,'yyyy-mm-dd');
How can I make one view that will always display correct rows from last weekday?
SELECT *
FROM table_1
WHERE mydate >= SYSDATE - CASE WHEN TO_CHAR(SYSDATE, 'D') > 6 THEN 3 ELSE 1 END
AND mydate < SYSDATE - CASE WHEN TO_CHAR(SYSDATE, 'D') > 6 THEN 3 ELSE 1 END + 1
You could use CASE:
SQL> WITH table_1 AS
2 (SELECT SYSDATE - 20 + ROWNUM mydate FROM dual CONNECT BY ROWNUM <= 40)
3 SELECT *
4 FROM table_1
5 WHERE trunc(Mydate) =
6 CASE WHEN to_char(SYSDATE, 'D') = to_char(DATE '2000-01-02', 'D')
7 THEN -- sunday
8 trunc(SYSDATE - 2)
9 WHEN to_char(SYSDATE, 'D') = to_char(DATE '2000-01-03', 'D')
10 THEN -- monday
11 trunc(SYSDATE - 3)
12 ELSE
13 trunc(SYSDATE - 1)
14 END;
MYDATE
-----------
08/01/2010
Note: Depending upon your NLS setting, to_char(X, 'D')
may return 1 for mondays or sundays. Relying on a known monday (eg: 2000-01-03 in my case) will make this query work in any setting.
精彩评论