|
다른 분들이 올려주신 글을 봤는데 일요일부터가 아니라 월요일부터 시작하는 쿼리를 만들려면 어떻게 해야하는지요?
예를 들어 2006 -11 이면
sun | mon | tue | wed | thu| fri | sat
1 2 3 4
5 6 7 ......................
이렇게가 아니라...
mon | tue | wed | thu| fri | sat | sun
1 2 3 4 5
6 7 . 8 .....................
이렇게 나오도록여....
쉽게 바꿀 수 있을지 알았는데 잘 안되네여... 도움 바랍니다...^^
select
WEEKS,
sum(decode(D,'1', DAYNUM)) as SUN,
sum(decode(D,'2', DAYNUM)) as MON,
sum(decode(D,'3', DAYNUM)) as TUE,
sum(decode(D,'4', DAYNUM)) as WED,
sum(decode(D,'5', DAYNUM)) as THU,
sum(decode(D,'6', DAYNUM)) as FRI,
sum(decode(D,'7', DAYNUM)) as SAT
from (
select
ROWNUM as DAYNUM,
to_char(to_date(b.YYMM||lpad(ROWNUM,2,'0'),'YYYYMMDD'), 'D') as D,
cast(to_char(to_date(b.YYMM||lpad(rownum,2,'0'),'YYYYMMDD'), 'W') as number) +
decode(
sign(cast(to_char(to_date(b.YYMM||lpad(ROWNUM,2,'0'),'YYYYMMDD'), 'D') as NUMBER)
-cast(to_char(to_date(b.YYMM||'01','YYYYMMDD'), 'D') as NUMBER)),
0, -1, 1, -1, 0) +1 as WEEKS
from dual cross join (select '200611' as YYMM from dual) b
connect by level<=to_number(to_char(last_day(to_date(b.YYMM,'YYYYMM')),'DD'))
)
group by WEEKS
|
