SQL & PL/SQL

This thread explains Uekisan method B-)

****************************************************************************************
1. What is Uekisan ? ?:|

"Uekisan" is Japanese language.
"Uekisan" is one of math problem.

If you remember "Tabibitosan method" 1007478
Tabibitosan and Uekisan are math problems.

http://yslibrary.cool.ne.jp/sansub0301.html
http://www.manabinoba.com/index.cfm/4,1487,73,html?year=2002

In Japanese -> English dictionary,
"Ueki" means "garden tree".

My homepage uses "Uekisan method"
http://www.geocities.jp/oraclesqlpuzzle/5-18.html
http://www.geocities.jp/oraclesqlpuzzle/7-42.html

****************************************************************************************
2. What is basic usage of "Uekisan method" ? ?:|
from 1120325

How many Sundays in 2010-August ?

select
(next_day(date '2010-08-31'   ,'日')-7
-next_day(date '2010-08-01' -1,'日'))/7+1
as "sundays"
from dual;

sundays
-------
      5

I explain above SQL logic ;-)

next_day(date '2010-08-31','日')-7
is the most nearly Sunday which is equal or less than '2010-08-31'
It is '2010-08-29'

next_day(date '2010-08-01' -1,'日')
is the most nearly Sunday which is equal or greater than '2010-08-01'
It is '2010-08-01'

And then please imagine two Ueki at very huge horizontal calendar :8}
One of Ueki exists '2010-08-01'
The another exists '2010-08-29'

And another Ueki exist between '2010-08-01' and '2010-08-29' of each Sunday.

Then How many Ueki exists ?
It is derived by (date '2010-08-29' - date '2010-08-01')/7+1

****************************************************************************************
3. What is complex usage of "Uekisan method" ? ?:|
from 1108468

How many Fridays and Saturdays between '2010-02-11' and '2010-05-19' ?

OK I will use "Uekisan method" twice.

select
 (next_day(date '2010-05-19'   ,'金')-7
 -next_day(date '2010-02-11' -1,'金'))/7+1
+(next_day(date '2010-05-19'   ,'土')-7
 -next_day(date '2010-02-11' -1,'土'))/7+1
as "金土の日数"
from dual;

金土の日数
----------
        28

****************************************************************************************
OracleSQLPuzzle (MyHomePage)
http://www.geocities.jp/oraclesqlpuzzle/

My SQL article of OTN-Japan B-)
http://www.oracle.com/technetwork/jp/articles/otnj-sql-image4-1-365357-ja.html

sister threads
Tabibitosan method tutorial by Aketi Jyuuzou
1007478
Boolean algebra in some Rows tutorial by Aketi Jyuuzou
1007678