Date Constraints of different time granularities in Oracle

Recently, we have used arcplan for reports. When we need to display data at different date granularities, it is easier to use SQL statements to directly query data from the database. It is highly reusable and recorded.

Annual granularity: displays the data pushed for the first five years of the current year.

1. T. d_day> = add_months (to_date ('123'|'123','Yyyymmdd'),-60)
2. AndT. d_day <= last_day (to_date ('123'|'123','Yyyymmdd'))

Half-year granularity: displays 12 data points pushed six months ago.

1. D_day> = add_months (to_date (substr ('1h2', 1, 4) | decode (substr ('1h2', 6, 6), 1,'123', 2,'123'),'Yyyymmdd'),-72)
2. AndT3.d _ day <= to_date (substr ('1h2', 1, 4) | decode (substr ('1h2', 6, 6), 1,'123', 2,'123'),'Yyyymmdd')

Quarterly granularity: displays the first 12 data points in the current quarter.

1. T. d_day> = add_months (to_date (substr ('20142q1', 1, 4) | decode (substr ('20142q1', 6, 6), 1,'123', 2,'123', 3,'123', 4,'123'),'Yyyymmdd'),-36)
2. AndT. d_day <= to_date (substr ('20142q1', 1, 4) | decode (substr ('20142q1', 6, 6), 1,'123', 2,'123', 3,'123', 4,'123'),'Yyyymmdd')

Monthly granularity: displays the data of the 12 months of the current year, which is pushed less than 12 months ago.

1. T. d_day <= last_day (to_date ('2017
2. ','Yyyymm') And t. d_day> add_months (last_day (to_date ('201204','Yyyymm'),-12)

Granularity: displays the data of the first 12 points in the current quarter.

1. T. d_day> = to_date (to_char (add_months (to_date (substr ('20140901', 1, 6 ),'Yyymmm',-Ceil (11/3 )),'Yyymmm') | Decode (to_char (
2. Case WhenTo_number (substr ('20140901', 8, 1)-mod (11, 3)> 0THENTo_number (substr ('20140901', 8, 1)-mod (11, 3)
3. ElseTo_number (substr ('20140901', 8, 1)-mod (11, 3) + 3END), 1,'01', 2,'11', 3,'21'),'Yyyymmdd')
4. AndT. d_day <= decode (substr ('20140901', 8, 8), 1, to_date (substr ('20140901', 1, 6) |'10','Yyyymmdd'), 2, to_date (substr ('20140901', 1, 6) |'20','Yyyymmdd'), 3, last_day (to_date (substr ('20140901', 1, 6) |'01','Yyyymmdd')))

Weekly granularity: displays the first 12 data points in the week of the current year.

1. T. d_day <= trunc (to_date (substr ('1w53', 1, 4) |'01'|'01','Yyyymmdd'),'Aw') + 7 * to_number (substr ('1w53', 6, 2)-1
2. AndT. d_day> trunc (to_date (substr ('1w53', 1, 4) |'01'|'01','Yyyymmdd'),'Aw') + 7 * to_number (substr ('1w53', 6, 2)-1-12*7

Daily granularity: displays the 30 days of the current month and 30 days before the last 30 days.

1. T. d_day <= to_date ('2017
2. ','Yyyymmdd') And t. d_day> (to_date ('20120326','Yyyymmdd')-30)