SQL to find Consecutive Halves [message #677532] |
Tue, 24 September 2019 13:24 |
patneel
Messages: 47 Registered: June 2007 Location: India
|
Member |
|
|
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 #677552 is a reply to message #677532] |
Wed, 25 September 2019 11:03 |
Solomon Yakobson
Messages: 3273 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
/
PERIOD START_DAT END_DATE
------------- --------- ---------
First Half 01-JAN-19 30-JUN-19
Previous Half 01-JUL-18 31-DEC-18
SQL>
SY.
[Updated on: Wed, 25 September 2019 11:08] Report message to a moderator
|
|
|