我想知道是否有办法获取上个月的记录?例如,如果运行以下查询,则查询应提供 01-04-2019 到 30-04-2019(4 月 1 日至 4 月 30 日(之间的事务的输出。
select * from table where transdate>='01-01-2019' and transdate <='31-05-2019'
Sample data
TRANSDATE LABOR Hours
01-01-2019 A 2.5
23-01-2019 B 1.0
01-02-2019 B 3.0
12-03-2019 A 0.5
02-04-2019 A 1.5
12-04-2019 B 4.5
17-04-2019 B 2.0
22-04-2019 C 2.5
12-05-2019 A 3.5
Expected Output
TRANSDATE LABOR Hours
Apr-2019 A 1.5
Apr-2019 B 6.5
Apr-2019 C 2.5
如果last previous month
是根据用户输入计算的(例如2019-05-31
(,则:
select to_char(TRANSDATE, 'Mon-YYYY', 'en-US') as TRANSDATE, LABOR, sum(Hours) as Hours
from table
where transdate between
date('2019-05-31') - (day(date('2019-05-31')) - 1) days - 1 month
and date('2019-05-31') - day(date('2019-05-31')) days
group by to_char(TRANSDATE, 'Mon-YYYY', 'en-US'), LABOR
;
用户可以提供月份中的任何日期。between
子句中表达式的结果对于同一月份的任何提供日期都是相同的。