Important Date Related Queries - 2

[debasisdas]

12.Alternate method for determining Quarter Start and End Dates for Quarter(not recommended)
----------------------------------------------------------------------------------------------

select add_months(q_end,-2) q_start,
last_day(q_end) q_end
from (select to_date(substr(yrq,1,4)||mod(yrq,10)*3,'yyyymm') q_end
from (
select 20071 yrq from dual union all
select 20072 yrq from dual union all
select 20073 yrq from dual union all
select 20074 yrq from dual
) x
) y

13.Filling in Missing Dates
----------------------------------------------

with x
as (
select add_months(start_date,level-1) start_date
from (
select min(trunc(hiredate,'y')) start_date,
add_months(max(trunc(hiredate,'y')),12) end_date from emp) connect by level <= months_between(end_date,start_date))     
select x.start_date MTH, count(e.hiredate) num_hired from x, emp e where x.start_date = trunc(e.hiredate(+),'mm')
group by x.start_date
order by 1

14.Alternate Method.
-----------------------------------

with x
as (
select add_months(start_date,level-1) start_date
from (
select min(trunc(hiredate,'y')) start_date,
add_months(max(trunc(hiredate,'y')),12) end_date
from emp)
connect by level <= months_between(end_date,start_date)
)
select x.start_date MTH, count(e.hiredate) num_hired
from x left join emp e
on (x.start_date = trunc(e.hiredate,'mm'))
group by x.start_date
order by 1

15.Find all employees hired in February or December, as well as employees hired on a Tuesday.
-----------------------------------------------------------

select ename from emp
where rtrim(to_char(hiredate,'month')) in ('february','december')
or rtrim(to_char(hiredate,'day')) = 'tuesday'

16.Comparing Records Using Specific Parts of a Date
----------------------------------------------------------------------------------

select a.ename ||
       ' was hired on the same month and weekday as '||
       b.ename as msg
  from emp a, emp b
where to_char(a.hiredate,'DMON') =
      to_char(b.hiredate,'DMON')
  and a.empno < b.empno
order by a.ename

17.Finding Differences Between Rows in the Same Group or Partition
---------------------------------------------------------------------------------------------------------

select deptno, ename, sal, hiredate,
lpad(nvl(to_char(sal-next_sal), 'N/A'), 10) diff from ( select deptno, ename, sal, hiredate,
lead(sal)over(partition by deptno
order by hiredate) next_sal from emp)

18.Locating a Range of Consecutive Values
------------------------------------------------------------------

select proj_id, proj_start, proj_end
from (
select proj_id, proj_start, proj_end,
lead(proj_start)over(order by proj_id) next_proj_start from job)
where next_proj_start = proj_end

19.Locating the Beginning and End of a Range of Consecutive Values
===================================

select proj_grp, min(proj_start), max(proj_end)
  from (
select proj_id,proj_start,proj_end,
sum(flag)over(order by proj_id) proj_grp
from ( select proj_id,proj_start,proj_end,
case when
lag(proj_end)over(order by proj_id) =proj_start
then 0 else 1 end flag from job
)) group by proj_grp

20.Filling in Missing Values in a Range of Values
-----------------------------------------------------------------------------

select x.yr, coalesce(cnt,0) cnt
        from (
      select extract(year from min(hiredate)over( )) -
             mod(extract(year from min(hiredate)over( )),10) +
             rownum-1 yr
        from emp
       where rownum <= 10
             ) x,
             (
     select to_number(to_char(hiredate,'YYYY')) yr, count(*) cnt
       from emp
      group by to_number(to_char(hiredate,'YYYY'))
            ) y
      where x.yr = y.yr(+)

21.Generating Consecutive Numeric Values
------------------------------------------------------------------

with x
as (
select level id
from dual
connect by level <= 10
)
select * from x

22.PRINTING CALANDER
-----------------------------------------

with x
      as (
   select *
     from (
   select to_char(trunc(sysdate,'mm')+level-1,'iw') wk,
          to_char(trunc(sysdate,'mm')+level-1,'dd') dm,
          to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw,
          to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth,
          to_char(sysdate,'mm') mth
    from dual
   connect by level <= 31
         )
   where curr_mth = mth
  )
  select max(case dw when 2 then dm end) Mo,
         max(case dw when 3 then dm end) Tu,
         max(case dw when 4 then dm end) We,
         max(case dw when 5 then dm end) Th,
         max(case dw when 6 then dm end) Fr,
         max(case dw when 7 then dm end) Sa,
         max(case dw when 1 then dm end) Su
    from x
   group by wk
   order by wk