logo
down
shadow

Oracle SQL: Dynamic timeframe calculation


Oracle SQL: Dynamic timeframe calculation

Content Index :

Oracle SQL: Dynamic timeframe calculation
Tag : sql , By : littlefuzz
Date : November 28 2020, 11:01 PM

Hope this helps Greetings all knowing Stack. , Try this approach:
add four months to the current date
Add_Months(Trunc(Add_Months(SYSDATE, 4), 'year'), -4)

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Oracle timeframe conversion


Tag : chash , By : jay
Date : March 29 2020, 07:55 AM
Hope that helps I'm assuming valid_from/valid_to never spans multiple years; i.e., TO_DATE(valid_from,'YYYY') always = TO_DATE(valid_to,'YYYY').
SQL> CREATE TABLE source (
  2      bid         VARCHAR2(5)
  3  ,   valid_from  DATE
  4  ,   valid_to    DATE
  5  ,   qty         NUMBER
  6  );

Table created.

SQL> INSERT INTO source VALUES ('00001',TO_DATE('20010201','YYYYMMDD'),TO_DATE('20010630','YYYYMMDD'),132);

1 row created.

SQL> INSERT INTO source VALUES ('00002',TO_DATE('20020301','YYYYMMDD'),TO_DATE('20021231','YYYYMMDD'),59);

1 row created.

SQL> CREATE TABLE destination (
  2      bid         VARCHAR2(5)
  3  ,   jan         NUMBER
  4  ,   feb         NUMBER
  5  ,   mar         NUMBER
  6  ,   apr         NUMBER
  7  ,   may         NUMBER
  8  ,   jun         NUMBER
  9  ,   jul         NUMBER
 10  ,   aug         NUMBER
 11  ,   sep         NUMBER
 12  ,   oct         NUMBER
 13  ,   nov         NUMBER
 14  ,   dec         NUMBER
 15  );

Table created.

SQL> COLUMN jan FORMAT 999
SQL> COLUMN feb FORMAT 999
SQL> COLUMN mar FORMAT 999
SQL> COLUMN apr FORMAT 999
SQL> COLUMN may FORMAT 999
SQL> COLUMN jun FORMAT 999
SQL> COLUMN jul FORMAT 999
SQL> COLUMN aug FORMAT 999
SQL> COLUMN sep FORMAT 999
SQL> COLUMN oct FORMAT 999
SQL> COLUMN nov FORMAT 999
SQL> COLUMN dec FORMAT 999
SQL> INSERT INTO destination
  2  SELECT bid
  3  ,      NVL(MAX(DECODE(r,01,split_qty)),0)  jan
  4  ,      NVL(MAX(DECODE(r,02,split_qty)),0)  feb
  5  ,      NVL(MAX(DECODE(r,03,split_qty)),0)  mar
  6  ,      NVL(MAX(DECODE(r,04,split_qty)),0)  apr
  7  ,      NVL(MAX(DECODE(r,05,split_qty)),0)  may
  8  ,      NVL(MAX(DECODE(r,06,split_qty)),0)  jun
  9  ,      NVL(MAX(DECODE(r,07,split_qty)),0)  jul
 10  ,      NVL(MAX(DECODE(r,08,split_qty)),0)  aug
 11  ,      NVL(MAX(DECODE(r,09,split_qty)),0)  sep
 12  ,      NVL(MAX(DECODE(r,10,split_qty)),0)  oct
 13  ,      NVL(MAX(DECODE(r,11,split_qty)),0)  nov
 14  ,      NVL(MAX(DECODE(r,12,split_qty)),0)  dec
 15  FROM
 16  (
 17  SELECT x.bid
 18  ,      x.month_abbr
 19  ,      x.r
 20  ,      x.rn
 21  ,      x.total_months
 22  ,      x.qty
 23  ,      FLOOR(x.qty / x.total_months)
 24         + DECODE(x.rn
 25                  ,      x.total_months, MOD(x.qty, x.total_months)
 26                  ,      0)                           split_qty
 27  FROM  (SELECT     s.bid
 28         ,          months.r
 29         ,          ROW_NUMBER()
 30                    OVER (PARTITION BY s.bid
 31                          ORDER BY     months.r)            rn
 32         ,          COUNT(*)
 33                    OVER (PARTITION BY s.bid)               total_months
 34         ,          s.qty
 35         FROM      (SELECT     ROWNUM   r
 36                    FROM       DUAL
 37                    CONNECT BY LEVEL <= 12)  months
 38         ,          source                   s
 39         WHERE      TO_CHAR(s.valid_from,'YYYY') = TO_CHAR(s.valid_to,'YYYY')
 40         AND        months.r BETWEEN TO_NUMBER(TO_CHAR(s.valid_from,'MM'))
 41                                 AND TO_NUMBER(TO_CHAR(s.valid_to,'MM'))) x
 42  )
 43  GROUP BY bid
 44  ;

2 rows created.

SQL> SELECT *
  2  FROM   destination
  3  ;

BID    JAN  FEB  MAR  APR  MAY  JUN  JUL  AUG  SEP  OCT  NOV  DEC
----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
00001    0   26   26   26   26   28    0    0    0    0    0    0
00002    0    0    5    5    5    5    5    5    5    5    5   14

SQL>

Oracle select months out of a timeframe


Tag : sql , By : Stephen Dewar
Date : March 29 2020, 07:55 AM
wish help you to fix your issue You should use a row generator:
 select 
    id,  
    ADD_MONTHS( "from", N.N ), 
    to_char( dt_column, 'mm' ) , 
    to_char( dt_column, 'yyyy' ) 
 from
  yourTable t 
 inner join
  (SELECT ROWNUM n
   FROM   ( SELECT 1 just_a_column
         FROM   dual
         CONNECT BY LEVEL <= 365
  ) N
    on ADD_MONTHS( "from", N.N ) <= last_day( t."to" )

Oracle Apex: Dynamic Calculation in Classic Report


Tag : javascript , By : user149634
Date : March 29 2020, 07:55 AM
this one helps. Im assuming that you know how to get the name attribute of the textbox under the attribute5 column using Inspect element feature of your browser. It's something like "f01","f02" or maybe higher, just check it out. Another assumption(based on your given data) is that row 5 contains the total of rows 1 to 4 and row 7 contains the total of rows 5 and 6.
Once you got the name, paste this function on your page's "Function and Global Variable Declaration" property:
    function setTotals(itemname){
            var v_subtotal=0;
            var v_total=0;
            for(i=0;i<4;i++){
                v_subtotal = Number(v_subtotal) + Number(document.getElementsByName(itemname)[i].value);
            }
            v_total = Number(v_subtotal) + Number(document.getElementsByName(itemname)[5].value);
            document.getElementsByName(itemname)[4].value = v_subtotal;
            document.getElementsByName(itemname)[6].value = v_total;
        }

        function setOnChangeEvent(itemname){
            for(i=0;i<4;i++){
                document.getElementsByName(itemname)[i].setAttribute("onchange","setTotals(\"" + itemname + "\")");
            }
            $("[name=" + itemname + "]").eq(5).change(function(){
                $("[name=" + itemname + "]").eq(6).val(Number($("[name=" + itemname + "]").eq(4).val()) + Number($("[name=" + itemname + "]").eq(5).val()));
            });
            $("[name=" + itemname + "]").eq(4).attr("disabled","disabled");
            $("[name=" + itemname + "]").eq(6).attr("disabled","disabled");
        }
  setTotals(itemname );
  setOnChangeEvent(itemname );
 setTotals("f02");
 setOnChangeEvent("f02");
<input onchange="setTotals('f02')" name="f02" size="20" maxlength="2000" value="5" type="text">

Dynamic calculation in oracle apex tabular form : sys.htp.p


Tag : javascript , By : Arun Thomas
Date : March 29 2020, 07:55 AM
To fix this issue I have tabular form where col1 and col2 are numbers, and col3 should contain the difference between col1 and col2. , Instead of using expensive AJAX call to database, use JavaScript.
function f_CALC_DIFF(pThis) {    

 var row_id     = pThis.id.substr(4);    
 var s          = $('#f18_'+row_id).val().replace(/[^\d.-]/g, '');   
 var curr       = $(pThis).val().replace(/[^\d.-]/g, '');   

if(!s){  
var s= 0;  
var prev = s;}    
else{  
var prev = s;}  

  var diff = 0; 
 diff = (curr - prev);
 var n = diff.toFixed(2);
 alert (n);
 $('#f23_'+row_id).val(n);

// Please remove the AJAX call to database.
 /*apex.server.process   
( "CALC_DIFF", { x01: prev, x02: curr },   
{ success: function( pData ) {   
$('#f23_'+row_id).val(pData);}}  
);    
}   */

Need help for dynamic calculation in oracle sql query


Tag : sql , By : delphiace
Date : March 29 2020, 07:55 AM
seems to work fine I Have a table tab_1 with below values. ,
"Need help writing the query for below logic."
create or replace function dyn_calc
     (p_id in number)
      return number
is 
    result number;
    n1 number;
    n2 number;
    l_rec t23%rowtype;
    l_val number;

    type split_calc_r is record (
        val1 number
        , operator varchar2(1)
        , val2 number
    );
    l_calc_rec split_calc_r;

    function get_rec
        (p_id in number)
        return t23%rowtype
    is 
        rv t23%rowtype;
    begin
        select *
        into rv   
        from t23
        where id = p_id;
        return rv;
    end get_rec;    

    procedure split_calc
       (p_calc in varchar2
        , p_n1 out number
        , p_n2 out number
        , p_operator out varchar2)
    is
    begin
         p_n1 := regexp_substr(p_calc, '[0-9]+', 1, 1);
         p_n2 := regexp_substr(p_calc, '[0-9]+', 1, 2);
         p_operator := translate(p_calc, '-+*%01923456789','-+*%'); --regexp_substr(p_calc, '[\-\+\*\%]', 1, 1);
    end split_calc;

    function exec_calc 
        (p_n1 in number
          , p_n2 in number
          , p_operator in varchar2)
        return number
    is 
        rv number;
    begin
        execute immediate
            'select :n1 ' ||  p_operator ||  ' :n2 from dual'
            into rv
            using p_n1, p_n2; 
        return rv;
    end exec_calc;    

begin
    l_rec := get_rec(p_id);

    if l_rec.value is not null then
        result := l_rec.value;
    else        
         split_calc(l_rec.calculation
                     , l_calc_rec.val1
                     , l_calc_rec.val2
                     , l_calc_rec.operator);
         n1 := dyn_calc (l_calc_rec.val1);
         n2 := dyn_calc (l_calc_rec.val2);
         result := exec_calc(n1, n2, l_calc_rec.operator);
    end if;

    return result;
end;
/
SQL>  select dyn_calc(6) from dual;

DYN_CALC(6)
-----------
20

SQL>
select id, calculation, dyn_calc(id) as value
from t23
where calculation is not null;
Related Posts Related QUESTIONS :
  • Use returned values from a query in the same procedure
  • Sum all numeric columns in database and log results
  • Release item in current year
  • Conditional function not working as intended postgresql
  • SQL trigger, results not Grouped by
  • getting more records than expected with simple query in postgresql
  • get first occurrence of a certain combination of columns
  • Converting string timestamp to local-time zone in Oracle
  • Redshift - Filter out rows that are blank to left or right of a character
  • Teradata - Know the load volume by times of a table
  • How do I replace duplicate data with random data of same length in SQL
  • How to group by using pivot table
  • How can I achieve a Join for merging two tables FULL OUTER JOIN and avoid duplicates?
  • Multi Keyword search on multi-column sql
  • Computing the median of salaries under each manager in BigQuery SQL
  • How can insert value by selecting from another
  • Retrieving only the latest item for a set of data
  • update column with concatenating for that group only
  • SQL `SELECT 'some string value' AS fieldname` equivalent in Powershell?
  • How to determine the AVERAGE number of associated records
  • In Django, how can I build a set of criteria from a list?
  • Using replace() inside a loop
  • select user on create view
  • Calculate and aggregate column depending on a value in another colum (SQL)
  • Trigger to track all the insert or update on Oracle Table
  • Update column - remove first and last characters
  • T-SQL - Adding FK to table, make it do drop/null/efault automagically?
  • Get constraints and indexes on materialized views
  • SQL perform AVG after MAX
  • How do you join two tables A and B in order to get a non join column in A with data not in B?
  • Move Non-NULL Values Up Within Partition
  • Selecting rows based on two many-to-many relations
  • I have a table on which i want to do some advance calculation and get the table in a new shape
  • How to convert decimal to time in Oracle SQL?
  • Combine numbers to 1 row
  • Error: column does not exist when in fact it does? why?
  • How to fix: "Operation must use an updateable query" on update statement in Access VBA
  • Does information passes to a new table via CREATE TABLE update itself?
  • How to write a SQL statement which filters between two dates/times, with the date and time in separate columns
  • MS-Access-2016 Query Language 'ORDER BY' Clause
  • How to get totals for each age category?
  • get the first n characters of getdate()
  • Query to handle the dates
  • SQL grouping/counting on a string split function
  • ORDER BY and TOP statements in SQL works different on listing down the records and why?
  • SQL Server Computed Column: how to set it to NOT NULL, without persistence
  • Is nested select statement better than where statement?
  • Postgresql sequential scan slow performance on 500 million rows
  • Aggregate first order data to all orders - Microsoft SQL Server
  • Sorting comma delimited datasets in row
  • Question about the correct data structure
  • Showing two temp tables data in a single table
  • How can I find out the query execution time in Oracle SQL using Toad?
  • convert quarter number to month name in sql
  • Find max value from column that has a json object with key-value pairs
  • case statement based on 3 columns in 1 table
  • How to write a SQL query to select batsman wiht highest score than the previous year
  • Find Substring in SQL
  • Using VALUE to make temporary table
  • What is the correct way of writing an exclusive left-join on two tables?
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com