Re: calendar.sql

"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)
> /
>
>

Nifty piece of SQL...

I think Connor does sometimes check this newsgroup. Still, I'll take a stab at it:

The p column is used to figure out which week a particular day would fall in. Thus if only days 1 & 2 were in the first week (being Fri and Sat), then they would be assigned a p value of 0. It does this by finding out which day of the week the 1st of that month falls in.

Not sure by naming convention, what would p stand for...

although I do not own Connors book: I believe there is typo in the example you quote: where rownum <=to_number(to_char(last_day(to_date(sysdate)),'DD')) should be
where rownum <=to_number(to_char(last_day(to_date('&CALDATE')),'DD'))

HTH
Anurag Received on Mon Nov 22 2004 - 20:50:26 CST