469,082 Members | 1,172 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

Important Date Related Queries - 2

debasisdas
8,127 Expert 4TB
12.Alternate method for determining Quarter Start and End Dates for Quarter(not recommended)
----------------------------------------------------------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. select add_months(q_end,-2) q_start,
  2. last_day(q_end) q_end
  3. from (select to_date(substr(yrq,1,4)||mod(yrq,10)*3,'yyyymm') q_end
  4. from (
  5. select 20071 yrq from dual union all
  6. select 20072 yrq from dual union all
  7. select 20073 yrq from dual union all
  8. select 20074 yrq from dual
  9. ) x
  10. ) y
13.Filling in Missing Dates
----------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. with x
  2. as (
  3. select add_months(start_date,level-1) start_date
  4. from (
  5. select min(trunc(hiredate,'y')) start_date,
  6. add_months(max(trunc(hiredate,'y')),12) end_date from emp) connect by level <= months_between(end_date,start_date))     
  7. select x.start_date MTH, count(e.hiredate) num_hired from x, emp e where x.start_date = trunc(e.hiredate(+),'mm')
  8. group by x.start_date
  9. order by 1
14.Alternate Method.
-----------------------------------
Expand|Select|Wrap|Line Numbers
  1. with x
  2. as (
  3. select add_months(start_date,level-1) start_date
  4. from (
  5. select min(trunc(hiredate,'y')) start_date,
  6. add_months(max(trunc(hiredate,'y')),12) end_date
  7. from emp)
  8. connect by level <= months_between(end_date,start_date)
  9. )
  10. select x.start_date MTH, count(e.hiredate) num_hired
  11. from x left join emp e
  12. on (x.start_date = trunc(e.hiredate,'mm'))
  13. group by x.start_date
  14. order by 1
15.Find all employees hired in February or December, as well as employees hired on a Tuesday.
-----------------------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. select ename from emp
  2. where rtrim(to_char(hiredate,'month')) in ('february','december')
  3. or rtrim(to_char(hiredate,'day')) = 'tuesday'
16.Comparing Records Using Specific Parts of a Date
----------------------------------------------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. select a.ename ||
  2.        ' was hired on the same month and weekday as '||
  3.        b.ename as msg
  4.   from emp a, emp b
  5. where to_char(a.hiredate,'DMON') =
  6.       to_char(b.hiredate,'DMON')
  7.   and a.empno < b.empno
  8. order by a.ename
17.Finding Differences Between Rows in the Same Group or Partition
---------------------------------------------------------------------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. select deptno, ename, sal, hiredate,
  2. lpad(nvl(to_char(sal-next_sal), 'N/A'), 10) diff from ( select deptno, ename, sal, hiredate,
  3. lead(sal)over(partition by deptno
  4. order by hiredate) next_sal from emp)
18.Locating a Range of Consecutive Values
------------------------------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. select proj_id, proj_start, proj_end
  2. from (
  3. select proj_id, proj_start, proj_end,
  4. lead(proj_start)over(order by proj_id) next_proj_start from job)
  5. where next_proj_start = proj_end
19.Locating the Beginning and End of a Range of Consecutive Values
===================================
Expand|Select|Wrap|Line Numbers
  1. select proj_grp, min(proj_start), max(proj_end)
  2.   from (
  3. select proj_id,proj_start,proj_end,
  4. sum(flag)over(order by proj_id) proj_grp
  5. from ( select proj_id,proj_start,proj_end,
  6. case when
  7. lag(proj_end)over(order by proj_id) =proj_start
  8. then 0 else 1 end flag from job
  9. )) group by proj_grp
20.Filling in Missing Values in a Range of Values
-----------------------------------------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. select x.yr, coalesce(cnt,0) cnt
  2.         from (
  3.       select extract(year from min(hiredate)over( )) -
  4.              mod(extract(year from min(hiredate)over( )),10) +
  5.              rownum-1 yr
  6.         from emp
  7.        where rownum <= 10
  8.              ) x,
  9.              (
  10.      select to_number(to_char(hiredate,'YYYY')) yr, count(*) cnt
  11.        from emp
  12.       group by to_number(to_char(hiredate,'YYYY'))
  13.             ) y
  14.       where x.yr = y.yr(+)
21.Generating Consecutive Numeric Values
------------------------------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. with x
  2. as (
  3. select level id
  4. from dual
  5. connect by level <= 10
  6. )
  7. select * from x
22.PRINTING CALANDER
-----------------------------------------
Expand|Select|Wrap|Line Numbers
  1. with x
  2.       as (
  3.    select *
  4.      from (
  5.    select to_char(trunc(sysdate,'mm')+level-1,'iw') wk,
  6.           to_char(trunc(sysdate,'mm')+level-1,'dd') dm,
  7.           to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw,
  8.           to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth,
  9.           to_char(sysdate,'mm') mth
  10.     from dual
  11.    connect by level <= 31
  12.          )
  13.    where curr_mth = mth
  14.   )
  15.   select max(case dw when 2 then dm end) Mo,
  16.          max(case dw when 3 then dm end) Tu,
  17.          max(case dw when 4 then dm end) We,
  18.          max(case dw when 5 then dm end) Th,
  19.          max(case dw when 6 then dm end) Fr,
  20.          max(case dw when 7 then dm end) Sa,
  21.          max(case dw when 1 then dm end) Su
  22.     from x
  23.    group by wk
  24.    order by wk
Sep 6 '07 #1
0 6622

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by Thomas R. Hummel | last post: by
5 posts views Thread by David B | last post: by
67 posts views Thread by PC Datasheet | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.