SQL to get interval data

I am using Oracle 11.2.0.4 and working on creating a hierarchial query.  I'd need assistance in  write a SQL code (using CONNECT by LEVEL something like below) to get desired output below.  

                          
                              SELECT (LEVEL - 1) 
                              FROM dual
                              CONNECT BY LEVEL <= 100

Open in new window


Desired Output (for today's date) is:

OPDAYSTART	OPDAYSTOP	               FIRSTDAYSTARTTIME	FIRSTDAYSTOPTIME	 LASTDAYSTARTTIME	LASTDAYSTOPTIME
10/1/2017	10/1/2017 11:59:59 PM	10/1/2017	        10/1/2017 11:59:59 PM         12/31/2017	            12/31/2017 11:59:59PM
10/2/2017	10/2/2017 11:59:59 PM	10/1/2017	         10/1/2017 11:59:59PM	12/31/2017	           12/31/2017 11:59:59 PM
10/3/2017	10/3/2017 11:59:59 PM	10/1/2017	         10/1/2017 11:59:59PM	12/31/2017	           12/31/2017 11:59:59 PM
10/4/2017	10/4/2017 11:59:59 PM	10/1/2017	         10/1/2017 11:59:59PM	12/31/2017	            12/31/2017 11:59:59 PM
..
..
..
12/31/2017   12/31/2017 11:59:59 PM     10/1/2017               10/1/2017 11:59:59 PM      12/31/2017          12/31/2017 11:59:59 PM   

Open in new window



What I have done so far (not sure if this is best way to do it) based on "driving" hardcoded random date of 11/01/2017. The date could be any random date chosen by business.

(select opday as opdaystart, --  --operating day + date offset value obtained from connect by level select
        (opday + 1) - (1/24/60/60) as opdaystop,  -- Add 1 day to operating day and take off a second to get operating day stop time with timestamp
         FirstDayStarttime, -- This is the date without the timestamp from the beginning of quarter basing on the random date chosen
         (FirstDayStarttime + 1) - (1/24/60/60) as FirstDayStoptime,  -- get firstdaystarttime + add a day - subtract 1 second to get the FIRSTDAYSTOPTIME
         trunc(LastdayStoptime) as LastdayStarttime,   -- remove timestamp from LastDayStopTime to get LastDayStartTime
         LastdayStoptime                               -- This is LastDay of the Quarter with timestamp basing on the random day chosen - in this case its 12/31/2017 11:59:59PM
          from (
                -- this query gives 100 rows from beginning from 10/1/2017 until 1/8/2018 since date chosen is 11/1/2017
                select (trunc(x.day_in_yr, 'Q') + y.dateoffset) as opday, --operating day + date offset value obtained from connect by level select
                        trunc(x.day_in_yr, 'Q') as FirstDayStarttime, -- This is the date without the timestamp from the beginning of quarter basing on the random date chosen
                        add_months(trunc(x.day_in_yr, 'Q'), 3) - (1/24/60/60) as LastdayStoptime -- This is LastDay of the Quarter with timestamp basing on the random day chosen - in this case its 12/31/2017 11:59:59PM
                  from (
                        --
                        select to_date('11/1/2017 00:00:00', 'mm/dd/yyyy hh24:mi:ss') as day_in_yr -- This date is the driving date and although hardcoded here, this date has to be derived and could be any date but between firstdaystarttime and lastdaystarttime
                        from dual                                                     -- Change this date if you want to run for a different Period, could be any date chosen by business
                        --
                        ) X
                 cross join (
                             -- 
                              SELECT (LEVEL - 1) as dateoffset
                              FROM dual
                              CONNECT BY LEVEL <= 100 -- Get upto 100 dateoffsets in case period requested by businessi s large (6 months or even 1 year)
                             --                           
                            ) Y
                 --
                 ) Z
        WHERE Z.opday <= Z.LastdayStoptime); 

Open in new window


Thanks,
steve2312Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tomas Helgi JohannssonCommented:
Hi!

Starting from version 11R2 recursive queries can be done using the Common Table Expression (CTE) a.k.a. the WITH clause.
SO you can do this like this

This query produces last 100 days including current date

with  mydates (level,nextdate) as (
select 1 level, current_date from DUAL
union all 
select level+1, current_date - INTERVAL '1' DAY from DUAL nextdate
from mydates
where level < 100
)
SELECT nextdate from mydates
);

Open in new window


Regards,
    Tomas Helgi
steve2312Author Commented:
This query is producing an error when I attempt to test this.  I am using Oracle 11g  and could be a version issue?  Would it require the CONNECT BY clause?
Tomas Helgi JohannssonCommented:
Hi!

What error do you get ?
No the connect by is not required in this case.
https://oracle-base.com/articles/11g/recursive-subquery-factoring-11gr2

Regards,
    Tomas Helgi
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

steve2312Author Commented:
I get an ORA-00904 invalid identifier.  The error seem to point to (level, nextdate) line

with  mydates (level, nextdate) as (
select  1  level, current_date from DUAL
union all 
select level+1, current_date - INTERVAL '1' DAY from DUAL nextdate
from mydates
where level < 100
)
SELECT nextdate from mydates
;

Open in new window

sdstuberCommented:
What you had was fine, I would do something like this...

Put your input date in an inner query so you can reference it easily.  In my case I'm using "sysdate" but you could use any input date for that value, ideally it would be a bind variable.  The rest of the query would remain the same to generate all of the values for a quarter.

Rather than generating 100 days, instead generate exactly as many as you need.

SELECT opdaystart,
       opdaystart + 1 - 1 / 86400 opdaystop,
       qstart firstdaystarttime,
       qstart + 1 - 1 / 86400 firstdaystoptime,
       ADD_MONTHS(qstart, 3) - 1 lastdaystoptime,
       ADD_MONTHS(qstart, 3) - 1 / 86400 lastdaystoptime
  FROM (    SELECT TRUNC(input_date, 'Q') qstart, TRUNC(input_date, 'Q') + (LEVEL - 1) opdaystart
              FROM (SELECT SYSDATE input_date FROM DUAL)
        CONNECT BY TRUNC(input_date, 'Q') + (LEVEL - 1) < ADD_MONTHS(TRUNC(input_date, 'Q'), 3))

Open in new window

steve2312Author Commented:
Thank you, I ran a test based on the desired results and your query. The query works for a quarter!

Can this be generalized for 6 months or even a year (a remote chance but a possibility)  if the business decide to run to choose for more bigger ranger like 6 months?
sdstuberCommented:
year is easier than 6-months because there is already a built in truncation for years

SELECT opdaystart,
       opdaystart + 1 - 1 / 86400 opdaystop,
       ystart firstdaystarttime,
       ystart + 1 - 1 / 86400 firstdaystoptime,
       ADD_MONTHS(ystart, 12) - 1 lastdaystoptime,
       ADD_MONTHS(ystart, 12) - 1 / 86400 lastdaystoptime
  FROM (    SELECT TRUNC(input_date, 'YYYY') ystart, TRUNC(input_date, 'YYYY') + (LEVEL - 1) opdaystart
              FROM (SELECT SYSDATE input_date FROM DUAL)
        CONNECT BY TRUNC(input_date, 'YYYY') + (LEVEL - 1) < ADD_MONTHS(TRUNC(input_date, 'YYYY'), 12))


for 6 months what would you expect?  Jan1 - Jun 30,  and Jul 1 - Dec 31?  or some other range?

assuming it is Jan1-Jun30 and Jul1-Dec31 then try something like this...


SELECT opdaystart,
       opdaystart + 1 - 1 / 86400 opdaystop,
       hstart firstdaystarttime,
       hstart + 1 - 1 / 86400 firstdaystoptime,
       ADD_MONTHS(hstart, 6) - 1 lastdaystoptime,
       ADD_MONTHS(hstart, 6) - 1 / 86400 lastdaystoptime
  FROM (    SELECT hstart, hstart + (LEVEL - 1) opdaystart
              FROM (SELECT CASE
                               WHEN input_date < ADD_MONTHS(TRUNC(input_date, 'yyyy'), 6) THEN TRUNC(input_date, 'yyyy')
                               ELSE ADD_MONTHS(TRUNC(input_date, 'yyyy'), 6)
                           END
                               hstart
                      FROM (SELECT SYSDATE input_date FROM DUAL))
        CONNECT BY hstart + (LEVEL - 1) < ADD_MONTHS(hstart, 6))
Tomas Helgi JohannssonCommented:
Hi!

Sorry, I did a typo, This should work.
with  mydates (level, nextdate) as (
select  1  level, current_date nextdate from DUAL
union all 
select level+1, current_date - INTERVAL '1' DAY nextdate from DUAL
from mydates
where level < 100
)
SELECT nextdate from mydates
;

Open in new window


Regards,
   Tomas Helgi
steve2312Author Commented:
Thank for the queries sdstuber.  I tested them based on each scenario.  For the 6 month scenario, I'd like to say they would use Jan 1 - Jun 30 or Jul 1 - Dec 31st, but it's pretty much up to the business for the period they choose to run it.  

This query will be placed inside a stored procedure/package.
steve2312Author Commented:
One question sdstuber - how would we run these query without having to update the SQL statement for month, quarter or year/ period?

Is a one-stop shop flexible solution a possibility (because the trunc function essentially limits this possibility)?
sdstuberCommented:
yes, it's possible, but it necessarily gets more complicated because you must check the inputs for which type of range and then adjust the start/end points for each.  The basic logic is still the same though.


Try this,  again, simply adjust the input_date value to whatever date you want and change the range type to either 'year', 'quarter', or 'halfyear'.

SELECT opdaystart,
       opdaystart + 1 - 1 / 86400 opdaystop,
       firstdaystarttime,
       firstdaystarttime + 1 - 1 / 86400 firstdaystoptime,
       lastdaystoptime,
       lastdaystoptime + 1 - 1 / 86400 lastdaystoptime
  FROM (    SELECT firstdaystarttime, lastdaystoptime, firstdaystarttime + (LEVEL - 1) opdaystart
              FROM (SELECT CASE
                               WHEN range_type = 'year'
                               THEN
                                   TRUNC(input_date, 'yyyy')
                               WHEN range_type = 'quarter'
                               THEN
                                   TRUNC(input_date, 'q')
                               WHEN range_type = 'halfyear' AND input_date < ADD_MONTHS(TRUNC(input_date, 'yyyy'), 6)
                               THEN
                                   TRUNC(input_date, 'yyyy')
                               WHEN range_type = 'halfyear'
                               THEN
                                   ADD_MONTHS(TRUNC(input_date, 'yyyy'), 6)
                           END
                               firstdaystarttime,
                           CASE
                               WHEN range_type = 'year'
                               THEN
                                   ADD_MONTHS(TRUNC(input_date, 'yyyy'), 12) - 1
                               WHEN range_type = 'quarter'
                               THEN
                                   ADD_MONTHS(TRUNC(input_date, 'q'), 3) - 1
                               WHEN range_type = 'halfyear' AND input_date < ADD_MONTHS(TRUNC(input_date, 'yyyy'), 6)
                               THEN
                                   ADD_MONTHS(TRUNC(input_date, 'yyyy'), 6) - 1
                               WHEN range_type = 'halfyear'
                               THEN
                                   ADD_MONTHS(TRUNC(input_date, 'yyyy'), 12) - 1
                           END
                               lastdaystoptime
                      FROM (SELECT SYSDATE input_date, 'halfyear' range_type FROM DUAL))
        CONNECT BY firstdaystarttime + (LEVEL - 1) <= lastdaystoptime);

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
steve2312Author Commented:
The case statement works for all cases. However, it may not work for an adhoc/random case, should be business decide to go for "arbitrary" date range.

It'd be hard to guess that ahead of time (say a range of 9 months or 5 months). Can that be incorporated with an " case when else.."?
awking00Information Technology SpecialistCommented:
I might have known I would spend 20-30 minutes testing my solution only to find out sdstuber has already posted the exact same query. Got me again, Sean:-)
steve2312Author Commented:
I added the below code to the upper most select and this would address that odd scenario of business entering an arbitrary date range.

to_date(&FirstDay, 'MM/DD/YYYY') as FirstDayStarttime, 
to_date(&LastDay, 'MM/DD/YYYY') as LastDayStarttime

Open in new window

;
awking00Information Technology SpecialistCommented:
If it's truly a date range that is provided (i.e. a begin date and an end date), that is easily managed with the queries provided.
opdaystart becomes trunc(begindate) + level - 1
opdaystop becomes trunc(begindate) + level - 1/86400
firstdaystart becomes trunc(begindate)
firstdaystop becomes trunc(begindate) + 86399/86400
lastdaystart becomes trunc(enddate)
lastdaystop becomes trunc(enddate) + 86399/86400
and connect by level becomes <= enddate - begindate + 1
To be asked for a 5-month range is too ambiguous. You won't know if it's for four months ago plus this month or for this month and the next four. So I don't know if that scenario can be incorporated.
sdstuberCommented:
to extend for other range combinations it's simply a matter of adding additional clauses to the firstdaystarttime and lastdaystoptime CASE statements.

The outer part of the query will remain the same.

You can do 5 month, 3 day,  11 year,  57 week, whatever.  Just add the conditions marking the boundaries into the case statements and let the rest of the query do the work for you.
steve2312Author Commented:
Thank you sdstuber, for the expert comments and valuable suggestions.
steve2312Author Commented:
Thank you for the valuable suggestions and tips on improvizing my queries
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.