Some Playful, Complex & Amazing Queries

Query to get Financial or Yearly Calendar from desired date.

with base as (
  select to_date(:Date2Cal, 'DD-Mon-YYYY') dt,
         to_number(to_char(to_date(:Date2Cal, 'DD-Mon-YYYY'), 'MM')) mm,
         to_number(to_char(to_date(:Date2Cal, 'DD-Mon-YYYY'),
                           'YYYY')) YYYY
    from dual),
     Dates as (
  SELECT case when upper(:fy) = 'Y' then
                to_date('01-Apr-' || case when mm < 4 then 
                                            to_char(YYYY -1)
                                          else to_char(YYYY) end,
                        'DD-Mon-YYYY')
              else trunc(dt, 'Year')
         end as first_date,
         Add_months(case when upper(:fy) = 'Y' then
                           to_date('01-Apr-' || 
                                   case when mm < 4 then          
                                          to_char(YYYY -1)
                                        else to_char(YYYY) end,
                                   'DD-Mon-YYYY')
                         else trunc(dt, 'Year')
                    end, 12) - 1 as last_date
    from base),
     Calandar as (
  SELECT first_date + LEVEL - 1 CDate, 
         to_char(first_date + LEVEL -1, 'DAY') WeekDay,
         to_number(to_char(first_date + LEVEL - 1, 'D')) WeekDayNo,
         ceil(level/7) as WeekNo,
         to_number(to_char(first_date + LEVEL - 1, 'YYYYMM')) YYYYMnth,
         to_number(to_char(first_date + LEVEL - 1, 'MM')) Mnth,
         to_char(first_date + LEVEL - 1, 'DD') dayte
    FROM Dates
 CONNECT BY LEVEL <= last_date + 1 - first_date), 
     Cal as (
  select CDate, WeekDay,
         to_number(to_char(CDATE, 'DD')) - 2 + 
           to_number(to_char(trunc(CDATE, 'Month'), 'D')) Position,
         WeekDayNo, WeekNo, YYYYMnth, Mnth, Dayte
    from Calandar),
     formatcal as (
  select max(weekno) weekno, max(YYYYMnth) YYYYMnth, max(mnth) nth,
         max (decode (WeekDayNo, 1, dayte, null)) Sun,
         max (decode (WeekDayNo, 2, dayte, null)) Mon,
         max (decode (WeekDayNo, 3, dayte, null)) Tue,
         max (decode (WeekDayNo, 4, dayte, null)) Wed,
         max (decode (WeekDayNo, 5, dayte, null)) Thu,
         max (decode (WeekDayNo, 6, dayte, null)) Fri,
         max (decode (WeekDayNo, 7, dayte, null)) Sat
    from Cal 
   group by mnth, trunc(position / 7) 
   order by mnth, trunc(position / 7)),
     kal as (
  select weekno, to_number(to_char(cdate, 'YYYYMM')) - 0.1 YYYYmnth, 
         to_number(to_char(cdate, 'MM'))-.1 mnth, null sun, 
         null mon, null tue,
         case when dayte = '01' then
                trim(to_char(cdate, 'Month')) ||' '|| 
                trim(to_char(cdate, 'YYYY'))
         end wed, null thu, null fri, null sat
    from cal
   where case when dayte = '01' then
                trim(to_char(cdate, 'Month')) ||' '||
                trim(to_char(cdate, 'YYYY'))
         end is not null
  union
  select weekno, YYYYmnth, mnth, sun, mon, tue, wed, thu, fri, sat 
    from formatcal 
   order by weekno, YYYYmnth, sun, mon, tue, wed, thu, fri, sat) 
select sun, mon, tue, wed, thu, fri, sat,
       case when trim(trim(sun) || trim(mon) || trim(tue) || 
                 --trim(wed) || 
                 trim(thu) || trim(fri) || trim(sat)) is null then 
              null
            else weekno end as weekno,
       case when trim(trim(sun) || trim(mon) || trim(tue) || 
                 --trim(wed) || 
                 trim(thu) || trim(fri) || trim(sat)) is null then 
              null
            when mnth <=  3 then 
              case when upper(:fy) = 'Y' then 4 else 1 end
            when mnth <=  6 then
              case when upper(:fy) = 'Y' then 1 else 2 end
            when mnth <=  9 then 
              case when upper(:fy) = 'Y' then 2 else 3 end
            when mnth <= 12 then 
              case when upper(:fy) = 'Y' then 3 else 4 end
       end QrtrNo
  from kal; 

Note:
Input Value for :FY = 'N'
Input Value for :Date2Cal = '30-Jan-1975'

Try with:
Input Value for :FY = 'Y'
Input Value for :Date2Cal set to any desired date of your wish

SUN	MON	TUE	WED	THU	FRI	SAT	WEEKNO	QRTRNO
			January 1975
			01	02	03	04	1	1
05	06	07	08	09	10	11	2	1
12	13	14	15	16	17	18	3	1
19	20	21	22	23	24	25	4	1
26	27	28	29	30	31		5	1
			February 1975
						01	5	1
02	03	04	05	06	07	08	6	1
09	10	11	12	13	14	15	7	1
16	17	18	19	20	21	22	8	1
23	24	25	26	27	28		9	1
			March 1975
						01	9	1
02	03	04	05	06	07	08	10	1
09	10	11	12	13	14	15	11	1
16	17	18	19	20	21	22	12	1
23	24	25	26	27	28	29	13	1
30	31						13	1
			April 1975
		01	02	03	04	05	14	2
06	07	08	09	10	11	12	15	2
13	14	15	16	17	18	19	16	2
20	21	22	23	24	25	26	17	2
27	28	29	30				18	2
			May 1975
				01	02	03	18	2
04	05	06	07	08	09	10	19	2
11	12	13	14	15	16	17	20	2
18	19	20	21	22	23	24	21	2
25	26	27	28	29	30	31	22	2
			June 1975
01	02	03	04	05	06	07	23	2
08	09	10	11	12	13	14	24	2
15	16	17	18	19	20	21	25	2
22	23	24	25	26	27	28	26	2
29	30						26	2
Other months will be displayed so on and so forth…

2. Query to print string in X formation.

select rownum, substr(:str, level, 1) Vert,
       rpad(' ', level - 1, ' ') || substr(:str, level, 1) BackSlant,
       rpad(' ', length(:str) - level, ' ') ||
       substr(:str, level, 1) FrontSlant, level
  from dual
CONNECT BY level <= length(:str)
union
select rownum + length(:str),
       substr(reverse(substr(:str, 1, length(:str)-1)), level, 1) Vert,
       rpad(' ', length(reverse(substr(:str, 1, 
                                length(:str)-1))) - level, ' ') ||
         substr(reverse(substr(:str, 1, length(:str)-1)), 
                                              level, 1) FrontSlant,
       rpad(' ', level, ' ') ||
         substr(reverse(substr(:str, 1, length(:str)-1)), level, 
                                                      1) BackSlant,
       length(:str)-1-level+1
  from dual
CONNECT BY level <= length(reverse(substr(:str, 1, length (:str)-1)))
 order by 1;

Note:
Input value for :str = 'Oracle11g'
Query output is as per below image.

3. Query to form left aligned triangle.

select rpad('*', level, '*') a
  from dual 
--where mod(level, 2) <> 0
connect by level <= :lvl;

Note:
Input value for :lvl= '7'
Query output is as per below image.

4. Query to form left aligned inverted triangle.

select rpad('*', decode (:lvl - level +1, 0, 1, :lvl - level +1), '*') a 
  from dual
--where mod(level, 2). <> 0
connect by level <= :lvl;

Note:
Input value for :lvl= '7'
Query output is as per below image.

5. Query to form right aligned triangle.

select lpad(' ', :lvl - level + 1, ' ') ||
       Rpad('*', decode(level, 0, 1, level), '*') a
  from dual
--where mod(level, 2) <> 0
connect by level <= :lvl;

Note:
Input value for :lvl= '7'
Query output is as per below image.

6. Query to form right aligned inverted triangle.

select lpad(' ', level - 1, ' ') ||
       rpad('*', decode (:lvl - level + 1, 0, 1, :lvl - leveL + 1), 
            '*') a 
  from dual
--where mod (level, 2) <>0 
connect by level <= :lvl;

Note:
Input value for :lvl= '7'
Query output is as per below image.

7. Query to form diamond.

select level, lpad(' ', (:lvl - level) / 2, ' ') ||
              rpad('*', level, '*') a
  from dual
 where mod(level, 2) <> 0
connect by level <= :lvl 
 union
select :lvl + level + 1, lpad(' ', ((level + 1) / 2), ' ') ||
       rpad('*', decode(:lvl - level - 2, 0, 1, :lvl - level - 1), 
            '*') a
  from dual
 where mod (level, 2) = 0 
connect by level <= :lvl - 1;

Note:
Input value for :lvl= 14
Query output is as per below image.

8. Query to form reverse diamond.

select rpad('*', decode (:lvl - level +1, 0, 1, :lvl - level + 1),
            '*') a, 
       lpad(' ', level - 1, ' ') ||
       rpad('*', decode(:lvl - level, 0, 1, :lvl - level + 1),
            '*') b
  from dual
--where mod(level, 2) <> 0
connect by level <= :lvl
union all 
select rpad('*', level, '*') a, 
       lpad(' ', :lvl - level, ' ') ||
       rpad('*', decode(level, 0, 1, level), '*') b
  from dual
 where /*mod(level, 2) <> 0 
   and*/ level > 1
connect by level <= :lvl;

Note:
Input value for :lvl= 14
Query output is as per below image.

9. Query to get mathematical table listing for desired number and up to desired limit.

select :No || ' x ' || level || ' = ' || :No * level tbl from dual
connect by level <= :lvl;

Note:
Input value for :lvl= 10
Input Value for :No = 17
Query output is as per below image.

10. Query to generate mathematical tables using cartesian product / cross join.

with a as (
  select level num1
    from dual
 connect by level <= :no),
     b as (
 select level num2
   from dual
connect by level <= :lvl)
select num1, num2, num1 || ' x ' || num2 || ' = ' || num1 * num2 tpl
  from a, b
 order by 1, 2;

Note:
Input value for :lvl= 5
Input Value for :No = 3
Query output is as per below image.

Some Playful, Complex & Amazing Queries

Published by Girish Kirtikumar Mehta.

Leave a comment Cancel reply

Share this:

Related

Published by Girish Kirtikumar Mehta.

Leave a comment Cancel reply