Please let me know if the following logic can be implemented using PL/SQL and if you can please post an example that would help me greatly.
I would like to increment the value subtracted from sysdate, have the statement executed for each value incremented against database and have a script output.
The output I am after can be achieved using something like the following sql select statement:
select ENTITYID,UNITSIZE,DAYS,trunc(sysdate-24) as VALUEDATE from entityvalues where days <trunc(sysdate-23) and days >=trunc(sysdate-24) and entityid=5
union all
select ENTITYID,UNITSIZE,DAYS,trunc(sysdate-23) as VALUEDATE from entityvalues where days <trunc(sysdate-22) and days >=trunc(sysdate-23) and entityid=5
union all
select ENTITYID,UNITSIZE,DAYS,trunc(sysdate-22) as VALUEDATE from entityvalues where days <trunc(sysdate-21) and days >=trunc(sysdate-22) and entityid=5
union all
select ENTITYID,UNITSIZE,DAYS,trunc(sysdate-21) as VALUEDATE from entityvalues where days <trunc(sysdate-20) and days >=trunc(sysdate-21) and entityid=5
union all
select ENTITYID,UNITSIZE,DAYS,trunc(sysdate-20) as VALUEDATE from entityvalues where days <trunc(sysdate-19) and days >=trunc(sysdate-20) and entityid=5;
But what if I would like output data for 30 days, would I have to write this select statement 30 times, 60 times for 60 days and so on?
Sample Data :
create table EntityValues (
EntityId number(10,0),UnitSize number(19,4),Days DATE
);
---------INSERTING DATA V V V ---------------------
Insert into EntityValues values( 1, 1.000 ,to_date('2018-01-11 03:47:00','YYYY-MM-DD HH24:MI:SS') );
Insert into EntityValues values( 2, 2.000 ,to_date('2018-01-11 13:47:22','YYYY-MM-DD HH24:MI:SS') );
Insert into EntityValues values( 3, 120.897 ,to_date('2018-01-11 12:47:21','YYYY-MM-DD HH24:MI:SS') );
Insert into EntityValues values( 4, 501.206 ,to_date('2018-01-11 11:42:21','YYYY-MM-DD HH24:MI:SS') );
Insert into EntityValues values( 5, 30.010 ,to_date('2018-01-11 16:42:15','YYYY-MM-DD HH24:MI:SS') );
Insert into EntityValues values( 6, 30.000 ,to_date('2018-01-11 12:30:18','YYYY-MM-DD HH24:MI:SS') );
Insert into EntityValues values( 1, 20.001 ,to_date('2018-01-12 01:30:18','YYYY-MM-DD HH24:MI:SS') );
Insert into EntityValues values( 2, 25.502 ,to_date('2018-01-12 05:30:13','YYYY-MM-DD HH24:MI:SS') );
Insert into EntityValues values( 3, 0.012 ,to_date('2018-01-12 07:30:12','YYYY-MM-DD HH24:MI:SS') );
Insert into EntityValues values( 4, 250.002 ,to_date('2018-01-12 19:30:12','YYYY-MM-DD HH24:MI:SS') );
Insert into EntityValues values( 5, 200.000 ,to_date('2018-01-12 15:30:13','YYYY-MM-DD HH24:MI:SS') );
Insert into EntityValues values( 6, 7.000 ,to_date('2018-01-12 17:14:11','YYYY-MM-DD HH24:MI:SS') );
Insert into EntityValues values( 1, 23.001 ,to_date('2018-01-13 01:30:18','YYYY-MM-DD HH24:MI:SS') );
Insert into EntityValues values( 2, 24.502 ,to_date('2018-01-13 05:30:13','YYYY-MM-DD HH24:MI:SS') );
Insert into EntityValues values( 3, 0.452 ,to_date('2018-01-13 07:30:12','YYYY-MM-DD HH24:MI:SS') );
Insert into EntityValues values( 4, 330.002 ,to_date('2018-01-13 19:30:12','YYYY-MM-DD HH24:MI:SS') );
Insert into EntityValues values( 5, 121.000 ,to_date('2018-01-13 15:30:13','YYYY-MM-DD HH24:MI:SS') );
Insert into EntityValues values( 6, 2.000 ,to_date('2018-01-13 17:14:11','YYYY-MM-DD HH24:MI:SS') );
Insert into EntityValues values( 1, 23.001 ,to_date('2018-01-14 01:30:18','YYYY-MM-DD HH24:MI:SS') );
Insert into EntityValues values( 2, 24.502 ,to_date('2018-01-14 05:30:13','YYYY-MM-DD HH24:MI:SS') );
Insert into EntityValues values( 3, 0.452 ,to_date('2018-01-14 07:30:12','YYYY-MM-DD HH24:MI:SS') );
Insert into EntityValues values( 4, 330.002 ,to_date('2018-01-14 19:30:12','YYYY-MM-DD HH24:MI:SS') );
Insert into EntityValues values