| sql help [message #330842] |
Tue, 01 July 2008 08:21  |
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 #330846 is a reply to message #330842] |
Tue, 01 July 2008 08:26  |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
