Home » SQL & PL/SQL » SQL & PL/SQL » Time period condition
Time period condition [message #640559] Thu, 30 July 2015 12:58 Go to next message
patneel
Messages: 47
Registered: June 2007
Location: India
Member
Can anyone please provide some suggestions on SQL logic?

I have calendar table but seems to be struggling on how to identify completed months

-Completed Months (YYYYMM) in Current Quarter of previous Year as of current date (Ex-No output as of 30th July 2015 and 201407 as of 1st Aug 2015 )

-Completed Months (YYYYMM) in Previous Year as of current date (Ex Jan Jun of 2014 as of 30th July 2015)
Re: Time period condition [message #640560 is a reply to message #640559] Thu, 30 July 2015 13:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post CREATE TABLE statement of the calendar table.

"Current Quarter" depends upon either quarter start date or quarter end date.

Is "previous year" calendar year, fiscal year, or ??????

You MUST define your terms much more rigorously!
Re: Time period condition [message #640561 is a reply to message #640560] Thu, 30 July 2015 13:17 Go to previous messageGo to next message
patneel
Messages: 47
Registered: June 2007
Location: India
Member
Current quarter (1 July 2015-30-Sep-2015) depends on quarter start date like as of 30th July 2015 quarter start date is 1 July 2015 so previous year current quarter start date is 1 July 2014 and there is no month got completed in the current quarter of previous year as of 30th July 2015. As of 1st Aug 2015, I am expecting 201407 as output as July month got completed in the current quarter.

Previous and current year is calendar year.
Re: Time period condition [message #640562 is a reply to message #640561] Thu, 30 July 2015 13:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If I understand you, you want the date which is start of the current quarter minus one year.
Please confirm.

Re: Time period condition [message #640566 is a reply to message #640562] Thu, 30 July 2015 14:58 Go to previous messageGo to next message
patneel
Messages: 47
Registered: June 2007
Location: India
Member
No sorry. I am looking for months (YYYYMM) completed in a quarter as of date. Today is 30th July 2015 so no full month got completed as of 30th July 2015 but say as of 1st Aug 2015, July 2015 will be completed. looking for output as months completed in a quarter as of date
Re: Time period condition [message #640567 is a reply to message #640566] Thu, 30 July 2015 15:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
patneel wrote on Thu, 30 July 2015 12:58
No sorry. I am looking for months (YYYYMM) completed in a quarter as of date. Today is 30th July 2015 so no full month got completed as of 30th July 2015 but say as of 1st Aug 2015, July 2015 will be completed. looking for output as months completed in a quarter as of date


So is it correct that during the first month of the quarter the function returns 3.
So is it correct that during the second month of the quarter the function returns 1.
So is it correct that during the third month of the quarter the function returns 2.
So is it correct that the function returns only either 1,2, or 3


[Updated on: Thu, 30 July 2015 15:08]

Report message to a moderator

Re: Time period condition [message #640568 is a reply to message #640566] Thu, 30 July 2015 15:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Still don't understand.

Quote:
looking for output as months completed in a quarter as of date


So what it is the desired result for these dates:
05-JAN-2015
27-JAN-2015
24-FEB-2015
20-APR-2015
17-MAY-2015
08-JUN-2015
16-JUL-2015
12-AUG-2015
10-SEP-2015
14-FEB-2016
08-JUN-2016
10-JUL-2016
03-SEP-2016
18-NOV-2016
04-FEB-2017
Re: Time period condition [message #640569 is a reply to message #640568] Thu, 30 July 2015 15:46 Go to previous messageGo to next message
patneel
Messages: 47
Registered: June 2007
Location: India
Member
Not sure if following table makes clear.

Date        Output
05-JAN-2015 No Output
27-JAN-2015 No Output
24-FEB-2015 201501
20-APR-2015 No Output
17-MAY-2015 201504
08-JUN-2015 201504
            201505
16-JUL-2015 No Output
12-AUG-2015 201507
10-SEP-2015 201507
            201508
14-FEB-2016 201601
08-JUN-2016 201604
            201605
10-JUL-2016 No Output
03-SEP-2016 201607
            201608
18-NOV-2016 201610
04-FEB-2017 201701
Re: Time period condition [message #640577 is a reply to message #640569] Fri, 31 July 2015 01:29 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, it makes it clear:
SQL> create table t as
  2  select trunc(sysdate,'year')+round(dbms_random.value(1,50)*level) dt
  3  from dual
  4  connect by level <= 20
  5  /

Table created.

SQL> select * from t order by dt;
DT
-----------
15-JAN-2015
24-JAN-2015
28-JAN-2015
25-FEB-2015
07-MAR-2015
17-MAR-2015
21-MAR-2015
03-APR-2015
18-APR-2015
06-MAY-2015
11-MAY-2015
24-JUL-2015
23-DEC-2015
21-APR-2016
01-MAY-2016
22-JUN-2016
09-JUL-2016
28-JUL-2016
14-SEP-2016
04-JUN-2017

20 rows selected.

SQL> break on dt
SQL> select dt,
  2         add_months(trunc(dt,'Q'),column_value) res
  3  from t,
  4       table(cast(multiset(select level-1 from dual connect by level <= 3)
  5                  as sys.odciNumberList))
  6  where add_months(trunc(dt,'Q'),column_value) < trunc(dt,'MM')
  7  order by dt, res
  8  /
DT          RES
----------- -----------
25-FEB-2015 01-JAN-2015
07-MAR-2015 01-JAN-2015
            01-FEB-2015
17-MAR-2015 01-JAN-2015
            01-FEB-2015
21-MAR-2015 01-JAN-2015
            01-FEB-2015
06-MAY-2015 01-APR-2015
11-MAY-2015 01-APR-2015
23-DEC-2015 01-OCT-2015
            01-NOV-2015
01-MAY-2016 01-APR-2016
22-JUN-2016 01-APR-2016
            01-MAY-2016
14-SEP-2016 01-JUL-2016
            01-AUG-2016
04-JUN-2017 01-APR-2017
            01-MAY-2017

18 rows selected.

Previous Topic: Include if else in sql statement
Next Topic: i want to find the string 'EMP1' which procedures have that string under the package
Goto Forum:
  


Current Time: Fri Mar 29 05:31:13 CDT 2024