Re: calendar.sql

"Alan" <alan_at_erols.com> wrote in message news:30f04eF2v4jqeU1_at_uni-berlin.de...
>
> "Oradba Linux" <techiey2k3_at_comcast.net> wrote in message
> news:eFeod.652457$8_6.498621_at_attbi_s04...
> > From connor mcdonald's mastering oracle pl/sql , i am not able to
> understand
> > the logic for using the colum p. The query is below.
> >
> > select
> > max(decode(dow,1,d,null)) Sun,
> > max(decode(dow,2,d,null)) Mon,
> > max(decode(dow,3,d,null)) Tue,
> > max(decode(dow,4,d,null)) Wed,
> > max(decode(dow,5,d,null)) Thu,
> > max(decode(dow,6,d,null)) Fri,
> > max(decode(dow,7,d,null)) Sat
> > from (
> > select rownum d ,
> >
> >
>

rownum-2+to_number(to_char(trunc(to_date('&CALDATE','DD-MON-YYYY'),'MM'),'D'
> > ))p,
> >
> to_char(trunc(to_date('&CALDATE','DD-MON-YYYY'),'MM')-1+rownum,'D')
> > dow
> > from all_objects
> > where rownum <=to_number(to_char(last_day(to_date(sysdate)),'DD'))
> > )
> > group by trunc(p/7)
> > /
> >
> >
>
> I could be wrong on this, as I did not study this in great detail, but I
> suspect the p/7 should be d/7.
>
>

trunc(p/7) works well.
1st column is the rownum. The where condition restricts the number of rows as per the number of days in the month for any given date as input. He gets the last day of the month using the last_day function. The column dow gets the 1st day of the month and adds rownum to it for every row starting with 0. Thats the reason behind -1 + rownum.
But the column 'p' gets first day of the month for the given date and gets the day of the week for that date and adds 1 for every row starting with -1. Received on Mon Nov 22 2004 - 20:15:19 CST