Home » SQL & PL/SQL » SQL & PL/SQL » Case/Decode
Case/Decode [message #212789] Mon, 08 January 2007 06:31 Go to next message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

Hi,
I am selecting rows using Union All,
Can anyone guide me how to use making a single query using Case or Decode.

SELECT '01-JUL-2005' "From Date" ,'30-SEP-2005' "To Date", COUNT(1) from just_increase
where TRUNC(EXPIRY) BETWEEN '01-Jul-2006' AND '30-SEP-2006'
union all
SELECT '01-OCT-2005' "From Date" ,'30-DEC-2005' "To Date", COUNT(1) from just_increase
where TRUNC(EXPIRY) BETWEEN '01-OCT-2005' AND '30-DEC-2005'
union all
SELECT '01-JAN-2006' "From Date" ,'30-MAR-2006' "To Date", COUNT(1) from just_increase
where TRUNC(EXPIRY) BETWEEN '01-JAN-2006' AND '30-MAR-2006'
union all
SELECT '01-APR-2006' "From Date" ,'30-JUNE-2006' "To Date", COUNT(1) from just_increase
where TRUNC(EXPIRY) BETWEEN '01-APR-2006' AND '30-JUNE-2006'

Thanks
Re: Case/Decode [message #212798 is a reply to message #212789] Mon, 08 January 2007 07:16 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
select dt,count(*) from (
Select case when TRUNC(EXPIRY) between to_date ('01-Oct-2005','dd-Mon-yyyy') AND to_date ('31-Dec-2005','dd-Mon-yyyy')
then 'between Sep and Dec 2005'
when TRUNC(EXPIRY) between to_date ('01-Jan-2006','dd-Mon-yyyy') AND to_date ('31-Mar-2006','dd-Mon-yyyy')
then 'between Jan and Mar 2006'
when TRUNC(EXPIRY) between to_date ('01-Apr-2006','dd-Mon-yyyy') AND to_date ('30-Jun-2006','dd-Mon-yyyy')
then 'between Apr and Jun 2006'
when TRUNC(EXPIRY) between to_date ('01-Jul-2006','dd-Mon-yyyy') AND to_date ('30-Sep-2006','dd-Mon-yyyy')
then 'between Jul and Sep 2006'
else 'Others' end dt,just_increase.*
from  just_increase
where trunc(expiry) between to_date ('01-Oct-2005','dd-Mon-yyyy') and to_date ('30-Sep-2006','dd-Mon-yyyy')
)
group by dt



Note I have also included 30th Dec 2005 and 31st march 2006 in query
Re: Case/Decode [message #212814 is a reply to message #212789] Mon, 08 January 2007 08:00 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
amit_kiran wrote on Mon, 08 January 2007 07:31
Hi,
I am selecting rows using Union All,
Can anyone guide me how to use making a single query using Case or Decode.

SELECT '01-JUL-2005' "From Date" ,'30-SEP-2005' "To Date", COUNT(1) from just_increase
where TRUNC(EXPIRY) BETWEEN '01-Jul-2006' AND '30-SEP-2006'



These are not DATEs, they are character strings. '07-DEC-2099' is an example of a string that falls between that range.
Re: Case/Decode [message #212815 is a reply to message #212789] Mon, 08 January 2007 08:00 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
A simple one but not exactly the same (which is in OP's post).
If you need 31th Dec 2005 and 31st march 2006 also and want to show the quarter stating date and ending date as from date and to date.
select trunc(EXPIRY,'Q') "From Date"
      ,add_months(trunc(EXPIRY,'Q'),3)-1 "To Date"
      ,count(0)

By
Vamsi
Re: Case/Decode [message #212826 is a reply to message #212798] Mon, 08 January 2007 08:21 Go to previous message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

Thanks a lot.
Previous Topic: Error
Next Topic: DBMS_REFRESH
Goto Forum:
  


Current Time: Wed Dec 07 04:41:15 CST 2016

Total time taken to generate the page: 0.18383 seconds