Discussions
Categories
- 382.6K All Categories
- 2.1K Data
- 213 Big Data Appliance
- 1.9K Data Science
- 448.4K Databases
- 221.1K General Database Discussions
- 3.7K Java and JavaScript in the Database
- 25 Multilingual Engine
- 541 MySQL Community Space
- 469 NoSQL Database
- 7.8K Oracle Database Express Edition (XE)
- 2.9K ORDS, SODA & JSON in the Database
- 499 SQLcl
- 3.9K SQL Developer Data Modeler
- 186.3K SQL & PL/SQL
- 21.1K SQL Developer
- 293.8K Development
- 9 Developer Projects
- 130 Programming Languages
- 290.5K Development Tools
- 96 DevOps
- 3K QA/Testing
- 645.6K Java
- 24 Java Learning Subscription
- 36.9K Database Connectivity
- 151 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.9K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 146 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 200 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 293 LiveLabs
- 36 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.8K Other Languages
- 2.3K Chinese
- 168 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 234 Portuguese
Sequence number in oracle SQL SQL
Hi I have my data as below.
<span class="pln">Year Period Start_Date End_Date <br/></span><span class="lit">2019</span><span class="pln"> </span><span class="lit">6</span><span class="pln"> </span><span class="lit">1</span><span class="pun">-</span><span class="pln">Jun-19 </span><span class="lit">30</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">2019</span><span class="pln"> </span><span class="lit">7</span><span class="pln"> </span><span class="lit">1</span><span class="pun">-</span><span class="pln">Jul-19 </span><span class="lit">31</span><span class="pun">-</span><span class="pln">Jul-19</span>
Period will be the month number
start date and end date will be the month start and end dates.
I want to fetch the data as below based on above.
7 1 05-Jul-19
7 2 12-Jul-19
7 3 19-Jul-19
7 4 26-Jul-19<span class="lit">7</span><span class="pln"> </span><span class="lit">5</span><span class="pln"> </span><span class="lit">26</span><span class="pun">-</span><span class="pln">Aug-19<code>( when ever month last day is not equal to Friday i need to display next month first Friday in current month).
<span class="pln">Period Sequence_no Period_end_dt <br/></span><span class="lit">6</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="lit">07</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">6</span><span class="pln"> </span><span class="lit">2</span><span class="pln"> </span><span class="lit">14</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">6</span><span class="pln"> </span><span class="lit">3</span><span class="pln"> </span><span class="lit">21</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">6</span><span class="pln"> </span><span class="lit">4</span><span class="pln"> </span><span class="lit">28</span><span class="pun">-</span><span class="pln">Jun-19 <br/><code><span class="lit"></span><span class="pln">6 5</span><span class="pln"> </span><span class="lit">05</span><span class="pun">-</span><span class="pln">Jul-19 ( when ever month last day is not equal to Friday i need to display next month first Friday in current month).</span>
in above out put data Period_end_dt values will be the Fridays in between start and end date in my source data. .
Best Answer
-
Muneendra wrote:mathguy can you please alter your query to fetch data as like below. ( when month end date is not equal to Friday, i want to display the next month first Friday in the current month with period as current month)Please refer the below changes. YEAR_ PERIOD START_DATE END_DATE SEQUENCE_NO PERIOD_END_DT---------- ---------- ----------- ----------- ----------- ------------- 2019 6 01-Jun-2019 30-Jun-2019 1 07-Jun-2019 2019 6 01-Jun-2019 30-Jun-2019 2 14-Jun-2019 2019 6 01-Jun-2019 30-Jun-2019 3 21-Jun-2019 2019 6 01-Jun-2019 30-Jun-2019 4 28-Jun-2019 2019 6 01-Jun-2019 30-Jun-2019 5 05-Jul-2019 2019 9 01-Sep-2019 30-Sep-2019 1 06-Sep-2019 2019 9 01-Sep-2019 30-Sep-2019 2 13-Sep-2019 2019 9 01-Sep-2019 30-Sep-2019 3 20-Sep-2019 2019 9 01-Sep-2019 30-Sep-2019 4 27-Sep-2019 2019 9 01-Oct-2019 31-Oct-2019 5 04-Oct-2019 2019 10 01-Oct-2019 31-Oct-2019 1 04-Oct-2019 2019 10 01-Oct-2019 31-Oct-2019 2 11-Oct-2019 2019 10 01-Oct-2019 31-Oct-2019 3 18-Oct-2019 2019 10 01-Oct-2019 31-Oct-2019 4 25-Oct-2019 2019 10 01-Oct-2019 31-Oct-2019 5 01-Nov-2019
To get that output, you need just a very minor change to the code I proposed (highlighted in red below). You may use what I posted (including the WITH clause), just make this minor change in the code and re-run, you will get the modified output you requested.
select year_, period, start_date, add_months(start_date, 1) - 1 as end_date, level as sequence_no, trunc(start_date + 2, 'iw') - 3 + 7 * level as period_end_dtfrom ( select year_, period, to_date(to_char(year_ * 100 + period), 'yyyymm') as start_date from test_data )connect by level < ( add_months(start_date, 1) - (trunc(start_date + 2, 'iw') - 3) + 6) / 7 -- here (add 6)
and prior year_ = year_ and prior period = period and prior sys_guid() is not nullorder by period_end_dt;
Answers
-
Muneendra wrote:Hi I have my data as below.
<span class="pln">Year Period Start_Date End_Date <br/></span><span class="lit">2019</span><span class="pln"> </span><span class="lit">6</span><span class="pln"> </span><span class="lit">1</span><span class="pun">-</span><span class="pln">Jun-19 </span><span class="lit">30</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">2019</span><span class="pln"> </span><span class="lit">7</span><span class="pln"> </span><span class="lit">1</span><span class="pun">-</span><span class="pln">Jul-19 </span><span class="lit">31</span><span class="pun">-</span><span class="pln">Jul-19</span>Period will be the month number
start date and end date will be the month start and end dates.
I want to fetch the data as below based on above.
<span class="pln">Period Sequence_no Period_end_dt <br/></span><span class="lit">6</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="lit">07</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">6</span><span class="pln"> </span><span class="lit">2</span><span class="pln"> </span><span class="lit">14</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">6</span><span class="pln"> </span><span class="lit">3</span><span class="pln"> </span><span class="lit">21</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">6</span><span class="pln"> </span><span class="lit">4</span><span class="pln"> </span><span class="lit">28</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">7</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="lit">05</span><span class="pun">-</span><span class="pln">Jul-19 <br/></span><span class="lit">7</span><span class="pln"> </span><span class="lit">2</span><span class="pln"> </span><span class="lit">12</span><span class="pun">-</span><span class="pln">Jul-19 <br/></span><span class="lit">7</span><span class="pln"> </span><span class="lit">3</span><span class="pln"> </span><span class="lit">19</span><span class="pun">-</span><span class="pln">Jul-19 <br/></span><span class="lit">7</span><span class="pln"> </span><span class="lit">4</span><span class="pln"> </span><span class="lit">26</span><span class="pun">-</span><span class="pln">Jul-19</span>
in above out put data Period_end_dt values will be the Fridays in between start and end date in my source data. .
Please provide details as stated in #5 - #9 inclusive in URL below
-
Muneendra wrote:Hi I have my data as below.
<span class="pln">Year Period Start_Date End_Date <br/></span><span class="lit">2019</span><span class="pln"> </span><span class="lit">6</span><span class="pln"> </span><span class="lit">1</span><span class="pun">-</span><span class="pln">Jun-19 </span><span class="lit">30</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">2019</span><span class="pln"> </span><span class="lit">7</span><span class="pln"> </span><span class="lit">1</span><span class="pun">-</span><span class="pln">Jul-19 </span><span class="lit">31</span><span class="pun">-</span><span class="pln">Jul-19</span>Period will be the month number
start date and end date will be the month start and end dates.
I want to fetch the data as below based on above.
<span class="pln">Period Sequence_no Period_end_dt <br/></span><span class="lit">6</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="lit">07</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">6</span><span class="pln"> </span><span class="lit">2</span><span class="pln"> </span><span class="lit">14</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">6</span><span class="pln"> </span><span class="lit">3</span><span class="pln"> </span><span class="lit">21</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">6</span><span class="pln"> </span><span class="lit">4</span><span class="pln"> </span><span class="lit">28</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">7</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="lit">05</span><span class="pun">-</span><span class="pln">Jul-19 <br/></span><span class="lit">7</span><span class="pln"> </span><span class="lit">2</span><span class="pln"> </span><span class="lit">12</span><span class="pun">-</span><span class="pln">Jul-19 <br/></span><span class="lit">7</span><span class="pln"> </span><span class="lit">3</span><span class="pln"> </span><span class="lit">19</span><span class="pun">-</span><span class="pln">Jul-19 <br/></span><span class="lit">7</span><span class="pln"> </span><span class="lit">4</span><span class="pln"> </span><span class="lit">26</span><span class="pun">-</span><span class="pln">Jul-19</span>
in above out put data Period_end_dt values will be the Fridays in between start and end date in my source data. .
In addition to John's request,
1) It appears that 'period' could/should be derived from the month of either start_date or end_date. If so, then the 'period' column should not even exist.
What is the data type of all of these columns? (We'll see that when you respond to John's request.)
-
Here's how you should provide your "data"
with raw_datum as ( select extract(year from to_date('01-Jun-19', 'dd-Mon-yy')) as my_year ,extract(month from to_date('01-Jun-19', 'dd-Mon-yy')) as my_period ,to_date('01-Jun-19', 'dd-Mon-yy') as start_date ,to_date('30-Jun-19', 'dd-Mon-yy') as end_date ,extract(day from to_date('30-Jun-19', 'dd-Mon-yy')) as num_days_per_month from dual union all select extract(year from to_date('01-Jul-19', 'dd-Mon-yy')) as my_year ,extract(month from to_date('01-Jul-19', 'dd-Mon-yy')) as my_period ,to_date('01-Jul-19', 'dd-Mon-yy') as start_date ,to_date('31-Jul-19', 'dd-Mon-yy') as end_date ,extract(day from to_date('31-Jul-19', 'dd-Mon-yy')) as num_days_per_month from dual )select rd.my_year ,rd.my_period ,rd.start_date ,rd.end_date ,rd.num_days_per_month from raw_datum rd;Output
Time Start: 7/29/2019 1:27:52 PM MY_YEAR MY_PERIOD START_DATE END_DATE NUM_DAYS_PER_MONTH---------- ---------- ---------- --------- ------------------ 2019 6 01-JUN-19 30-JUN-19 30 2019 7 01-JUL-19 31-JUL-19 312 rows selected.Time End: 7/29/2019 1:27:53 PMElapsed Time for Script Execution: 638 msecs
-
with t (sd, ed) as
(
select date '2019-06-01', date '2019-07-31' from dual
)
select
dt
, extract(year from dt) yr
, extract(month from dt) mnth
, row_number() over (partition by extract(month from dt) order by dt) seq
from
(
select sd + level -1 dt, to_char(sd + level - 1, 'DY', 'nls_language=ENGLISH') dy from t
connect by sd + level - 1 <= ed
)
where dy = 'FRI'
;
DT YR MNTH SEQ
--------- ---------- ---------- ----------
07-JUN-19 2019 6 1
14-JUN-19 2019 6 2
21-JUN-19 2019 6 3
28-JUN-19 2019 6 4
05-JUL-19 2019 7 1
12-JUL-19 2019 7 2
19-JUL-19 2019 7 3
26-JUL-19 2019 7 48 rows selected.
-
You shouldn't store period as it's derived from the date range, and in doing so, you create the possibility where period could mismatch the date. You could create a virtual column to expose it.
-
Here is a better (closed form) way to do this. Based on simple computations, rather than an enumeration of all consecutive days and filtering for day-of-the-week = Friday.
Inputs: a set rows showing "year" and "period" (month), assuming no duplicates. Output: for each input row, several output rows, repeating the "year", "period", "start date", "end date", and all the Fridays in the period, with the sequence number in a separate column. Ordered by those Fridays (which in particular will also, automatically, order by year first, and by period second).
Note that YEAR is an Oracle keyword, which shouldn't be used as a column name. I changed it to YEAR_ (notice the trailing underscore). Test data provided in WITH clause.
with test_data as ( select 2019 as year_, 6 as period from dual union all select 2019 , 9 from dual union all select 2019 , 10 from dual )select year_, period, start_date, add_months(start_date, 1) - 1 as end_date, level as sequence_no, trunc(start_date + 2, 'iw') - 3 + 7 * level as period_end_dtfrom ( select year_, period, to_date(to_char(year_ * 100 + period), 'yyyymm') as start_date from test_data )connect by level < ( add_months(start_date, 1) - (trunc(start_date + 2, 'iw') - 3) ) / 7 and prior year_ = year_ and prior period = period and prior sys_guid() is not nullorder by period_end_dt; YEAR_ PERIOD START_DATE END_DATE SEQUENCE_NO PERIOD_END_DT---------- ---------- ----------- ----------- ----------- ------------- 2019 6 01-Jun-2019 30-Jun-2019 1 07-Jun-2019 2019 6 01-Jun-2019 30-Jun-2019 2 14-Jun-2019 2019 6 01-Jun-2019 30-Jun-2019 3 21-Jun-2019 2019 6 01-Jun-2019 30-Jun-2019 4 28-Jun-2019 2019 9 01-Sep-2019 30-Sep-2019 1 06-Sep-2019 2019 9 01-Sep-2019 30-Sep-2019 2 13-Sep-2019 2019 9 01-Sep-2019 30-Sep-2019 3 20-Sep-2019 2019 9 01-Sep-2019 30-Sep-2019 4 27-Sep-2019 2019 10 01-Oct-2019 31-Oct-2019 1 04-Oct-2019 2019 10 01-Oct-2019 31-Oct-2019 2 11-Oct-2019 2019 10 01-Oct-2019 31-Oct-2019 3 18-Oct-2019 2019 10 01-Oct-2019 31-Oct-2019 4 25-Oct-2019
-
@Paulzip Can you please alter your query to fetch the dates like below.
DT YR MNTH SEQ
--------- ---------- ---------- ----------
07-JUN-19 2019 6 1
14-JUN-19 2019 6 2
21-JUN-19 2019 6 3
28-JUN-19 2019 6 4 When month end date is grater than last Friday in month, i need to add one more row with next month first Friday with current month number as shown below.
05-JUL-19 2019 6 5
05-JUL-19 2019 7 1
12-JUL-19 2019 7 2
19-JUL-19 2019 7 3
26-JUL-19 2019 7 4
02-AUG-19 2019 7 5
-
can you please alter your query to fetch data as like below. ( when month end date is not equal to Friday, i want to display the next month first Friday in the current month with period as current month)
Please refer the below changes.
YEAR_ PERIOD START_DATE END_DATE SEQUENCE_NO PERIOD_END_DT
---------- ---------- ----------- ----------- ----------- -------------
2019 6 01-Jun-2019 30-Jun-2019 1 07-Jun-2019
2019 6 01-Jun-2019 30-Jun-2019 2 14-Jun-2019
2019 6 01-Jun-2019 30-Jun-2019 3 21-Jun-2019
2019 6 01-Jun-2019 30-Jun-2019 4 28-Jun-2019
2019 6 01-Jun-2019 30-Jun-2019 5 05-Jul-2019
2019 9 01-Sep-2019 30-Sep-2019 1 06-Sep-2019
2019 9 01-Sep-2019 30-Sep-2019 2 13-Sep-2019
2019 9 01-Sep-2019 30-Sep-2019 3 20-Sep-2019
2019 9 01-Sep-2019 30-Sep-2019 4 27-Sep-2019
2019 9 01-Oct-2019 31-Oct-2019 5 04-Oct-2019
2019 10 01-Oct-2019 31-Oct-2019 1 04-Oct-2019
2019 10 01-Oct-2019 31-Oct-2019 2 11-Oct-2019
2019 10 01-Oct-2019 31-Oct-2019 3 18-Oct-2019
2019 10 01-Oct-2019 31-Oct-2019 4 25-Oct-2019
2019 10 01-Oct-2019 31-Oct-2019 5 01-Nov-2019
-
with t (sd, ed) as
(
select date '2019-05-01', date '2019-07-31' from dual
)
select
dt
, dy
, case when dt = mnth_end then decode(dy, 'FRI', dt, lag(dt) over (order by dt)+7) else dt end dt_1
, extract(year from dt) yr
, extract(month from dt) mnth
, row_number() over (partition by extract(month from dt) order by dt) seq
from
(
select
sd + level -1 dt
, to_char(sd + level - 1, 'DY', 'nls_language=ENGLISH') dy
, last_day(sd + level -1) mnth_end
from t
connect by sd + level - 1 <= ed
)
where dy = 'FRI' or (dt = mnth_end)
;
DT DY DT_1 YR MNTH SEQ
----------- --- ----------- ---------- ---------- ----------
2019-MAY-03 FRI 2019-MAY-03 2019 5 1
2019-MAY-10 FRI 2019-MAY-10 2019 5 2
2019-MAY-17 FRI 2019-MAY-17 2019 5 3
2019-MAY-24 FRI 2019-MAY-24 2019 5 4
2019-MAY-31 FRI 2019-MAY-31 2019 5 5
2019-JUN-07 FRI 2019-JUN-07 2019 6 1
2019-JUN-14 FRI 2019-JUN-14 2019 6 2
2019-JUN-21 FRI 2019-JUN-21 2019 6 3
2019-JUN-28 FRI 2019-JUN-28 2019 6 4
2019-JUN-30 SUN 2019-JUL-05 2019 6 5
2019-JUL-05 FRI 2019-JUL-05 2019 7 1
2019-JUL-12 FRI 2019-JUL-12 2019 7 2
2019-JUL-19 FRI 2019-JUL-19 2019 7 3
2019-JUL-26 FRI 2019-JUL-26 2019 7 4
2019-JUL-31 WED 2019-AUG-02 2019 7 515 rows selected
-
Muneendra wrote:mathguy can you please alter your query to fetch data as like below. ( when month end date is not equal to Friday, i want to display the next month first Friday in the current month with period as current month)Please refer the below changes. YEAR_ PERIOD START_DATE END_DATE SEQUENCE_NO PERIOD_END_DT---------- ---------- ----------- ----------- ----------- ------------- 2019 6 01-Jun-2019 30-Jun-2019 1 07-Jun-2019 2019 6 01-Jun-2019 30-Jun-2019 2 14-Jun-2019 2019 6 01-Jun-2019 30-Jun-2019 3 21-Jun-2019 2019 6 01-Jun-2019 30-Jun-2019 4 28-Jun-2019 2019 6 01-Jun-2019 30-Jun-2019 5 05-Jul-2019 2019 9 01-Sep-2019 30-Sep-2019 1 06-Sep-2019 2019 9 01-Sep-2019 30-Sep-2019 2 13-Sep-2019 2019 9 01-Sep-2019 30-Sep-2019 3 20-Sep-2019 2019 9 01-Sep-2019 30-Sep-2019 4 27-Sep-2019 2019 9 01-Oct-2019 31-Oct-2019 5 04-Oct-2019 2019 10 01-Oct-2019 31-Oct-2019 1 04-Oct-2019 2019 10 01-Oct-2019 31-Oct-2019 2 11-Oct-2019 2019 10 01-Oct-2019 31-Oct-2019 3 18-Oct-2019 2019 10 01-Oct-2019 31-Oct-2019 4 25-Oct-2019 2019 10 01-Oct-2019 31-Oct-2019 5 01-Nov-2019
To get that output, you need just a very minor change to the code I proposed (highlighted in red below). You may use what I posted (including the WITH clause), just make this minor change in the code and re-run, you will get the modified output you requested.
select year_, period, start_date, add_months(start_date, 1) - 1 as end_date, level as sequence_no, trunc(start_date + 2, 'iw') - 3 + 7 * level as period_end_dtfrom ( select year_, period, to_date(to_char(year_ * 100 + period), 'yyyymm') as start_date from test_data )connect by level < ( add_months(start_date, 1) - (trunc(start_date + 2, 'iw') - 3) + 6) / 7 -- here (add 6)
and prior year_ = year_ and prior period = period and prior sys_guid() is not nullorder by period_end_dt;
