Date calculation, date Calculator

Date calculation, date Calculator

Database environment: Oracle 11g R2

I haven't written a blog for a long time. The main reason is that I have met some of the requirements I have done in my blog before, so I am too lazy to sort them out.

Question: If the number of days in the first week of this month is <= 3 days, the first week will be classified as the previous month.

Implementation ideas:

1. Generate the month corresponding to all dates and dates of the current year, week

2. count the days of each week in each month

3. If the number of days in the first week is <= 3 days, then month-1

The following is an SQL script. The related notes are described in the script.

/* Date of birth and end of year */with x0 as (select to_date ('2017-01-01 ', 'yyyy-mm-dd') as Year of year, to_date ('1970-12-31 ', 'yyyy-mm-dd') as year-end from dual ), /* generate the year-round date */x1 as (select Year-end + level-1 as date from x0 connect by level <= (year-end-year-end) + 1 ), /* calculate the month of the date, week */x2 as (select date, to_char (date, 'mm') as month, to_char (date, 'iw ') as week, to_number (to_char (date, 'D') from x1 ), /* calculate the week and day of the month */x3 as (select date, month, week, dense_rank () over (the month where partition by is located, the week where order by is located) as the week of the month, count (*) over (the month in which partition by is located, the day of the week), the day of the week from x2 ), /* Based on the subject rule, process the month */x4 as (select date, month, week, day of the week, week, case when week of the current month = 1 and day of the week <= 3 then to_number (month)-1 else to_number (month) end nominal month from x3) select nominal month as month, min (date) as month, max (date) as month end from x4 group by nominal month order by 1View Code

Implementation result: