1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to display every monday dates of the month?

Discussion in 'SQL PL/SQL' started by yang, Apr 22, 2014.

  1. yang

    yang Guest

    Hi! How can I query all monday, tuesday, wednesday, thursday, friday, saturday, and sunday dates monthly individually? Please help me. Thank you!

    Here's the script:

    select 'Data'
    ||','||to_char(d.dtime_day, 'MM/dd/yyyy')
    ||','||nvl(m.mtotal, 0)
    ||','||nvl(tu.tutotal, 0)
    ||','||nvl(w.wtotal, 0)
    ||','||nvl(th.thtotal, 0)
    ||','||nvl(f.ftotal, 0)
    ||','||nvl(s.stotal, 0)
    ||','||nvl(su.sutotal, 0)
    from table1h.dc_date d
    left join (
    select trunc(t.create_time) as monday
    ,count(t.create_time) as mtotal
    from table.table2 t
    left join table.table1 q
    on q.ID = t.QUEUE_ID
    where t.create_time between trunc(sysdate)-12*7 and sysdate -1
    and q.name not like 'item0%'
    or q.name not like 'item1%'
    group by trunc(t.create_time)
    ) m on d.dtime_day = m.monday
    left join (
    select trunc(t.create_time, 'DD') as tuesday
    ,count(t.create_time) as tutotal
    from table.table2 t
    left join table.table1 q
    on q.ID = t.QUEUE_ID
    where t.create_time between trunc(sysdate,'DD')-12*7 and sysdate -1
    and q.name not like 'item0%'
    or q.name not like 'item1%'
    group by trunc(t.create_time, 'DD')
    ) tu on d.dtime_day = tu.tuesday
    left join (
    select trunc(t.create_time, 'DD') as wednesday
    ,count(t.create_time) as wtotal
    from table.table2 t
    left join table.table1 q
    on q.ID = t.QUEUE_ID
    where t.create_time between trunc(sysdate,'DD')-12*7 and sysdate -1
    and q.name not like 'item0%'
    or q.name not like 'item1%'
    group by trunc(t.create_time, 'DD')
    ) w on d.dtime_day = w.wednesday
    left join (
    select trunc(t.create_time, 'DD') as thursday
    ,count(t.create_time) as thtotal
    from table.table2 t
    left join table.table1 q
    on q.ID = t.QUEUE_ID
    where t.create_time between trunc(sysdate,'DD')-12*7 and sysdate -1
    and q.name not like 'item0%'
    or q.name not like 'item1%'
    group by trunc(t.create_time, 'DD')
    ) th on d.dtime_day = th.thursday
    left join (
    select trunc(t.create_time, 'DD') as friday
    ,count(t.create_time) as ftotal
    from table.table2 t
    left join table.table1 q
    on q.ID = t.QUEUE_ID
    where t.create_time between trunc(sysdate,'DD')-12*7 and sysdate -1
    and q.name not like 'item0%'
    or q.name not like 'item1%'
    group by trunc(t.create_time, 'DD')
    ) f on d.dtime_day = f.friday
    left join (
    select trunc(t.create_time, 'DD') as saturday
    ,count(t.create_time) as stotal
    from table.table2 t
    left join table.table1 q
    on q.ID = t.QUEUE_ID
    where t.create_time between trunc(sysdate,'DD')-12*7 and sysdate -1
    and q.name not like 'item0%'
    or q.name not like 'item1%'
    group by trunc(t.create_time, 'DD')
    ) s on d.dtime_day = s.saturday
    left join (
    select trunc(t.create_time, 'DD') as sunday
    ,count(t.create_time) as sutotal
    from table.table2 t
    left join table.table1 q
    on q.ID = t.QUEUE_ID
    where t.create_time between trunc(sysdate,'DD')-12*7 and sysdate -1
    and q.name not like 'item0%'
    or q.name not like 'item1%'
    group by trunc(t.create_time, 'DD')
    ) su on d.dtime_DAY = su.sunday
    where d.dtime_day between trunc(sysdate,'DD')-12*7 and trunc(sysdate) -1
    and trunc(d.dtime_day, 'DD')= d.dtime_day
    order by d.dtime_day;
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    simple example:

    NOTE : it is necessary to consider local settings
    Code (SQL):


    WITH
    calendar
    AS
    (
    SELECT
        trunc(sysdate,'mm')+level-1 dd      
    FROM dual
    CONNECT BY
    level <= EXTRACT (DAY FROM last_day(trunc(sysdate,'mm')))
    )
    SELECT
          to_char(c.dd,'WW') week,
          MAX(CASE WHEN to_char(c.dd,'D') = '2' THEN c.dd END ) MONDAY,
          MAX(CASE WHEN to_char(c.dd,'D') = '3' THEN c.dd END ) TUESDAY  ,
          MAX(CASE WHEN to_char(c.dd,'D') = '4' THEN c.dd END ) WEDNESDAY ,
          MAX(CASE WHEN to_char(c.dd,'D') = '5' THEN c.dd END ) THURSDAY ,      
          MAX(CASE WHEN to_char(c.dd,'D') = '6' THEN c.dd END ) FRIDAY   ,      
          MAX(CASE WHEN to_char(c.dd,'D') = '7' THEN c.dd END ) SATURDAY ,
          MAX(CASE WHEN to_char(c.dd,'D') = '1' THEN c.dd END ) SUNDAY

    FROM calendar c
    GROUP BY to_char(c.dd,'WW')
    ORDER BY week;