Home » SQL & PL/SQL » SQL & PL/SQL » SQL to find Consecutive Halves
SQL to find Consecutive Halves [message #677532] Tue, 24 September 2019 13:24 Go to next message
Messages: 47
Registered: June 2007
Location: India
I am looking for period dates (start and end) for Consecutive Halves. Like as of today (9/24/2019) looking for sql's which can give following outputs and that would be dynamic as per current date.

Period	       Start Date	End Date
First Half	1-Jan-19	30-Jun-19
Previous Half	1-Jul-18	31-Dec-18

I was trying something below but it seems need lot of if else statements

select (case when extract(month from sysdate) <= 6 then 1
else 2
end) as half_year
from dual)
Re: SQL to find Consecutive Halves [message #677536 is a reply to message #677532] Tue, 24 September 2019 14:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68258
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your previous topic:

Michel Cadot wrote on Wed, 05 July 2017 22:15
Michel Cadot wrote on Thu, 21 May 2015 19:47

Don't forget:
Michel Cadot wrote on Mon, 23 July 2012 07:50
With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
And don't forget to feedback to your topics.
Re: SQL to find Consecutive Halves [message #677552 is a reply to message #677532] Wed, 25 September 2019 11:03 Go to previous message
Solomon Yakobson
Messages: 3152
Registered: January 2010
Location: Connecticut, USA
Senior Member
select  case level
          when 1 then 'First Half'
          else 'Previous Half'
        end period,
        add_months(trunc(sysdate,'yy'),6 * (1 - level)) start_date,
        add_months(trunc(sysdate,'yy'),6 * (2 - level)) - 1 end_date
  from  dual
  connect by level <= 2

------------- --------- ---------
First Half    01-JAN-19 30-JUN-19
Previous Half 01-JUL-18 31-DEC-18



[Updated on: Wed, 25 September 2019 11:08]

Report message to a moderator

Previous Topic: Writing Query but not getting desired output
Next Topic: Need help with Error: subprogram or cursor reference is out of scope
Goto Forum:

Current Time: Sat Aug 13 21:42:02 CDT 2022