Home » SQL & PL/SQL » SQL & PL/SQL » Run Oracle SQL query multiple times with change in filter condition (12c)
Run Oracle SQL query multiple times with change in filter condition [message #678424] |
Mon, 02 December 2019 05:20 |
|
vsaenath
Messages: 3 Registered: December 2019
|
Junior Member |
|
|
Select 201905,sum(s.amount)
From sales s
Where greatest(s.effectivedate,s.entrydate)<=20190530 --last day of each month
Group by s.level1;
Can some one let me know the best way to run above query for last 12 months from current month.
S.effectivedate and s.entrydate are numbers.
|
|
|
|
|
Re: Run Oracle SQL query multiple times with change in filter condition [message #678427 is a reply to message #678426] |
Mon, 02 December 2019 08:53 |
Solomon Yakobson
Messages: 3284 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Storing dates as numbers is bad idea leading to all sorts of issues starting from making sure numbers map into valid dates. Anyway:
where greatest(to_date(s.effectivedate,'YYYYMMDD'),to_date(s.entrydate,'YYYYMMDD')) >= add_months(trunc(sysdate,'mm'),-12)
and greatest(to_date(s.effectivedate,'YYYYMMDD'),to_date(s.entrydate,'YYYYMMDD')) < trunc(sysdate,'mm')
SY.
[Updated on: Mon, 02 December 2019 08:54] Report message to a moderator
|
|
|
|
|
Re: Run Oracle SQL query multiple times with change in filter condition [message #678433 is a reply to message #678428] |
Tue, 03 December 2019 10:38 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
No problem, just use the table containing all days/months required in the report and outer join it with SALES.
If you do not have any such table, you may generate its rows on the fly as demonstrated in the query below.
I would prefer treating real dates as numbers (the opposite Solomon suggested) - then the query will not fail for data beyond the last day of real month.
I am using day 99 as the "last day"; you may pick any other, but then (wrong) data not belonging to real days would be reported to the next month.
Note those funny TO_NUMBER/TO_CHAR calls to overcome the design flaw. Though, that computation (including its floor division by 100 for getting month) is just a basic school mathematics.
So, the code shall look something like this:
with calendar (act_period) as (
-- or any query returning reported list of "last day of each month" values
select to_number(to_char(add_months(sysdate, -column_value), 'YYYYMM')||'99')
from table(sys.odcinumberlist(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)))
, sales (effectivedate, entrydate, amount) as (
-- something to mimic your data as you did not post any
-- when running on your DB, just remove it to use "real" sales table instead
select to_number(to_char(sysdate - column_value*100, 'YYYYMMDD'))
, to_number(to_char(sysdate - column_value*200, 'YYYYMMDD'))
, 100 + column_value
from table(sys.odcinumberlist(1, 2, 3, 4, 5, 6)))
select floor(c.act_period/100) reported_month
, nvl(sum(case when greatest(s.effectivedate, s.entrydate) <= c.act_period then s.amount end), 0) sum_amount
from calendar c left join sales s on
-- exclude data older than 13 months, if needed
greatest(s.effectivedate, s.entrydate) > to_number(to_char(add_months(sysdate, -13), 'YYYYMM')||'99')
and
-- exclude data from current month
greatest(s.effectivedate, s.entrydate) <= to_number(to_char(add_months(sysdate, -1), 'YYYYMM')||'99')
group by floor(c.act_period/100)
order by floor(c.act_period/100);
Result I received so far:
REPORTED_MONTH SUM_AMOUNT
-------------- ----------
201812 0
201901 0
201902 103
201903 103
201904 103
201905 205
201906 205
201907 205
201908 306
201909 306
201910 306
201911 306
12 rows selected.
|
|
|
Goto Forum:
Current Time: Fri Sep 20 20:29:31 CDT 2024
|