Important Date Related Queries - 2  | Moderator | | Join Date: Dec 2006 Location: Bangalore ,India
Posts: 7,569
# 1
Sep 6 '07
| |
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
|  |
Similar Oracle Database bytes | | |  Forums
Visit our community forums for general discussions and latest on Bytes
/bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 229,155 network members.
|
