Home » SQL & PL/SQL » SQL & PL/SQL » sql help (oracle 10g)
sql help [message #330842] Tue, 01 July 2008 08:21 Go to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Hi all
I have written the following query:
select count(*) case_assign, NEXT_DAY(to_date(to_char(tempDate.assign_dt-1, 'dd-MON-yyyy')), 'FRI') week_end_date
from (select i.invstg_asgn_dt assign_dt from investigations i
where i.invstg_asgn_dt between sysdate-60 and sysdate ) tempDate
group by NEXT_DAY(to_date(to_char(tempDate.assign_dt-1, 'dd-MON-yyyy')), 'FRI')
UNION ALL

select count(*) case_closed, NEXT_DAY(to_date(to_char(tempDate.closed_dt-1, 'dd-MON-yyyy')), 'FRI') week_end_date
from (select i.invstg_clsd_dt closed_dt from investigations i
where i.invstg_clsd_dt between sysdate-60 and sysdate ) tempDate
group by NEXT_DAY(to_date(to_char(tempDate.closed_dt-1, 'dd-MON-yyyy')), 'FRI')
UNION ALL
select count(*) intrv_scheduled, NEXT_DAY(to_date(to_char(tempDate.intvw_dt-1, 'dd-MON-yyyy')), 'FRI') week_end_date
from (select i.intvw_dt intvw_dt from interviews i
where i.intvw_dt between sysdate-60 and sysdate ) tempDate
group by NEXT_DAY(to_date(to_char(tempDate.intvw_dt-1, 'dd-MON-yyyy')), 'FRI')
UNION ALL

select count(*) present_dt, NEXT_DAY(to_date(to_char(tempDate.prsnt_dt-1, 'dd-MON-yyyy')), 'FRI') week_end_date
from (select i.prsnt_dt prsnt_dt from bpc_prosecutions i
where i.prsnt_dt between sysdate-60 and sysdate ) tempDate
group by NEXT_DAY(to_date(to_char(tempDate.prsnt_dt-1, 'dd-MON-yyyy')), 'FRI')
UNION ALL

select count(*) hearing_dt, NEXT_DAY(to_date(to_char(tempDate.hear_dt, 'dd-MON-yyyy')), 'FRI') week_end_date
from (select ah.hear_dt hear_dt from hearings ah, appeal_source_xref ax, appeal_issues ai
where ah.apel_id = ai.apel_id
and ai.apel_src_id = ax.apel_src_id
and ax.iss_id in (select ix.iss_id from investigation_issue_xref ix ) )tempDate
group by NEXT_DAY(to_date(to_char(tempDate.hear_dt, 'dd-MON-yyyy')), 'FRI')

and i got the following o/p
CASE_ASSIGN|| WEEKEND_DATE
1 || 05/30/2008
5 ||06/13/2008
8
1
1
3
6
1

The date field is not blank, i left it blank...................
but i want the output as
CASE_ASSIGN||CASE_CLOSED||INTRV_SCHEDULE||PRESENTDATE||HEARINGDATE||WEEKEND_DATE

the o/p i'm getting is right but i want them in the above mentioned format

please any help is grtly appreciated

thnks
Re: sql help [message #330844 is a reply to message #330842] Tue, 01 July 2008 08:23 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Please read & follow Posting Guidelines below
http://www.orafaq.com/forum/t/88153/0/

please use <code tags> so post is readable & can be understood
Re: sql help [message #330846 is a reply to message #330842] Tue, 01 July 2008 08:26 Go to previous message
Michel Cadot
Messages: 64101
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the o/p i'm getting is right but i want them in the above mentioned format

As we want you to format your post?
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: how to get data between from previous year April to given month and year
Next Topic: Index
Goto Forum:
  


Current Time: Fri Dec 02 13:54:58 CST 2016

Total time taken to generate the page: 0.08513 seconds