Creating a Calendar in a single SQL statement

Wow.... ALl the familiar builtins, still innovative result.
Great! Thank you.

Hi,

When I tried to execute the query I can see all the Saturday days are pushed one row down :(

Should we change the query?

This is the new query for English version:

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,"Sun", "Mon", "Tue",
"Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH,TO_CHAR(dt + 1,'iw') week,
MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sun",
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mon",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Tue",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Wed",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Thu",
MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) "Fri",
MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sat"
FROM ( SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt
FROM all_objects
WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y'))
GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), TO_CHAR( dt + 1, 'iw' ))
ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week);

and, for Italian:

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH, "Lun", "Mar",
"Mer", "Gio", "Ven", "Sab","Dom"
FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH,TO_CHAR(dt,'iw') week,
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Lun",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mar",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mer",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Gio",
MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) "Ven",
MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sab",
MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) "Dom"
FROM ( SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt
FROM all_objects
WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y'))
GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), TO_CHAR( dt, 'iw' ))
ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week);

Really great man!

Great.....

Thanks

Excellent. Thanks. Bring another one ...man.

Its really an excellent learning website. Really wonderful.

Ram

Its Really Great.......

A Proud Member of this site........

thanks man

its working and amazing query

Hi
You given the best

Even though One More view From My side

select b.*,
substr(to_char(to_date(a."1"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "1",
substr(to_char(to_date(a."2"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "2",
substr(to_char(to_date(a."3"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "3",
substr(to_char(to_date(a."4"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "4",
substr(to_char(to_date(a."5"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "5",
substr(to_char(to_date(a."6"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "6",
substr(to_char(to_date(a."7"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "7",
substr(to_char(to_date(a."8"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "8",
substr(to_char(to_date(a."9"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "9",
substr(to_char(to_date(a."10"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "10",
substr(to_char(to_date(a."11"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "11",
substr(to_char(to_date(a."12"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "12",
substr(to_char(to_date(a."13"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "13",
substr(to_char(to_date(a."14"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "14",
substr(to_char(to_date(a."15"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "15",
substr(to_char(to_date(a."16"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "16",
substr(to_char(to_date(a."17"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "17",
substr(to_char(to_date(a."18"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "18",
substr(to_char(to_date(a."19"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "19",
substr(to_char(to_date(a."20"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "20",
substr(to_char(to_date(a."21"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "21",
substr(to_char(to_date(a."22"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "22",
substr(to_char(to_date(a."23"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "23",
substr(to_char(to_date(a."24"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "24",
substr(to_char(to_date(a."25"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "25",
substr(to_char(to_date(a."26"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "26",
substr(to_char(to_date(a."27"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "27",
substr(to_char(to_date(a."28"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "28",
case when b.seq=2 then
case when &year/4=round(&year/4) then
substr(to_char(to_date(a."29"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3)
else
'NA'
end
else
substr(to_char(to_date(a."29"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3)
end "29",
case when b.seq<>2 then
substr(to_char(to_date(a."30"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3)
else
'NA'
end "30",
case when b.seq in (1,3,5,7,8,10,12) then
substr(to_char(to_date(a."30"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3)
else
'NA'
end "31"
from
(
Select
1 "1",
2 "2",
3 "3",
4 "4",
5 "5",
6 "6",
7 "7",
8 "8",
9 "9",
10 "10",
11 "11",
12 "12",
13 "13",
14 "14",
15 "15",
16 "16",
17 "17",
18 "18",
19 "19",
20 "20",
21 "21",
22 "22",
23 "23",
24 "24",
25 "25",
26 "26",
27 "27",
28 "28",
29 "29",
30 "30",
31 "31"
from dual
)
a,
(
select
rownum seq,
case when rownum=1 then 'January'
else
case when rownum=2 then 'February'
else
case when rownum=3 then 'March'
else
case when rownum=4 then 'April'
else
case when rownum=5 then 'May'
else
case when rownum=6 then 'June'
else
case when rownum=7 then 'July'
else
case when rownum=8 then 'August'
else
case when rownum=9 then 'September'
else
case when rownum=10 then 'October'
else
case when rownum=11 then 'November'
else
'December'
end
end
end
end
end
end
end
end
end
end
end
"Months"
from dual connect by level<=12
)
b

its really nice query,
but i could see same week being displayed for 30th and 31st for each months.

correct me if i m wrong here

regards
Vidya

Hi!

A version for spanish:

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) Mes,TO_NUMBER(week) semana,"Dom","Lun", "Mar", "Mie",
"Jue", "Vie", "Sab"
FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH, week,
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Lun",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mar",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mie",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Jue",
MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) "Vie",
MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sab",
MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) "Dom"
FROM ( select dt, (case when dt >=
to_date(to_char(dt, 'dd/')||'12/'||to_char(sysdate,'yyyy'),'dd/mm/yyyy') and wk = '01' then '53' else wk end) week
from(
SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt, TO_CHAR(TRUNC(SYSDATE,'y')-1+ROWNUM+1,'iw') wk
FROM all_objects
WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y'))
)
GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), week)
ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week);

Great Idea from Great Programmer.

Grate Job done

What about future or prior years? This only picks up the current year.

oww tht is really great....

i just completed SQL part and started with PL/SQL...My sir gave me mini project titled "calender with SQL"

i saw the query what u wrote...its really amazing....but i have not understood about all_object table?

what exactly it contains of? and weather to create this table before preceeding this query....?

kindly help me in this regard....

it's really nice...........

still innovative result.
Great!
Thank you.
-------------------------------

دردشة - شات - دردشه - فله - منتديات فله -فلة