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 Go to next message
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 #678425 is a reply to message #678424] Mon, 02 December 2019 05:29 Go to previous messageGo to next message
cookiemonster
Messages: 13755
Registered: September 2008
Location: Rainy Manchester
Senior Member
effectivedate and entrydate are numbers?

Presumably in YYYYMMDD format?

That's asking for trouble, can you change them to dates?

last 12 months from current month is rather imprecise.
Is the current month included?
Are you measuring from the start of the month or the end of the month?
Re: Run Oracle SQL query multiple times with change in filter condition [message #678426 is a reply to message #678425] Mon, 02 December 2019 05:47 Go to previous messageGo to next message
vsaenath
Messages: 3
Registered: December 2019
Junior Member
Hi, yes number formatted YYYYMMDD.
Query to run for previous 12 months. Excludes current month.
Sum based on level id from start of the policy it could be 5 years old or 10 years. That's why i used <= last month last day.
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2899
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 #678428 is a reply to message #678427] Mon, 02 December 2019 16:39 Go to previous messageGo to next message
vsaenath
Messages: 3
Registered: December 2019
Junior Member
Hi, It would give sum of rows >= last 12 months and less than current month.

I'm looking for sum of all rows beyond 12 months but for last 12 months.

Example: my fact table has data going back to 1999 and a policy could exist from 1999 to 2019 with various status. To determine if a policy is inforce or not inforce we use this status change fact table.

So selecting all rows <= 20190530 will get my inforce count as of 201905(month).

If i use between
Example: select all rows >=1999 and <201905 i will get 201905 inforce count. Now how will i get 201906? 201907 etc? I would like to get last 12 months counts each sum starting from 1999 to 201905, 1999 to 201906, 1999 to 201907
:
How can i get corresponding yearmonth in select as well?

Last 12 months
...
...
...
201905 sum 1000
201906 sum 1800
201907 sum 5000
201909 sum 6000
201910 sum 9000
Current month Nov 2019.
Re: Run Oracle SQL query multiple times with change in filter condition [message #678429 is a reply to message #678428] Tue, 03 December 2019 00:29 Go to previous messageGo to next message
Michel Cadot
Messages: 66719
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

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 Go to previous message
flyboy
Messages: 1882
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.
Previous Topic: Want to know insert into
Next Topic: In SEND EMAIL code, how to remove Boundaries from the output
Goto Forum:
  


Current Time: Mon Dec 09 19:55:05 CST 2019