Hello! Please help me. I stuck for an hours. I want to display all days (e.g monday, tuesday) individually. How can I do that?
Desired output:
Data,Date,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday
Data,04/01/2014,10,12,5,6,1,0,15
Data,04/07/2014,14,1,5,6,1,2,18
Data,04/14/2014,11,12,5,6,1,0,17
Here's the SQL Script:
select 'Data,Date,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday' from dual;
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 table0.dc_date d
left join (
select trunc(t.create_time) as monday
,count(t.create_time) as mtotal
from table.table1 t
left join table.table2 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 'item%'
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.table1 t
left join table.table2 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 'item%'
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.table1 t
left join table.table2 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 'item%'
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.table1 t
left join table.table2 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 'item%'
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.table1 t
left join table.table2 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 'item%'
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.table1 t
left join table.table2 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 'item%'
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.table1 t
left join table.table2 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 'item%'
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