Time period condition [message #640559] |
Thu, 30 July 2015 12:58 |
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 #640561 is a reply to message #640560] |
Thu, 30 July 2015 13:17 |
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 #640566 is a reply to message #640562] |
Thu, 30 July 2015 14:58 |
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 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
patneel wrote on Thu, 30 July 2015 12:58No 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 #640569 is a reply to message #640568] |
Thu, 30 July 2015 15:46 |
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 |
|
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.
|
|
|