Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Derivation of values falling on date ranges
Top Forums Programming Derivation of values falling on date ranges Post 302966405 by rohit_shinez on Thursday 11th of February 2016 03:35:52 PM
Old 02-11-2016
Derivation of values falling on date ranges
Hi Guys,

I am having below tables used in oracle
Code:
bal
 
ID	BALANCE	BAL_DATE
1	-11.71	01-JAN-05 00.00.00
1	-405.71	02-JAN-05 00.00.00
1	-760.71	03-JAN-05 00.00.00
 
ref_table
PRODUCT	EFF_FROM_DATE	EFF_TO_DATE	TYPE	MIN_AMT	MAX_AMT	CHARGE
12	01-JAN-05 00.00.00	01-JAN-06 00.00.00	T1	0	15	0
12	01-JAN-05 00.00.00	01-JAN-06 00.00.00	T2	16	1000	0.75
12	01-JAN-05 00.00.00	01-JAN-06 00.00.00	T3	1001	2000	1.5
12	01-JAN-05 00.00.00	01-JAN-06 00.00.00	T4	2001	5000	3
 
periods:
START_DATE	END_DATE	ID
02-JAN-05 00.00.00	13-OCT-05 00.00.00	1
 
Output required
 
 
T1_VAL	T2_VAL	T3_VAL	T4_VAL	BAL_DATE	T1	T2	T3	T4
-11.71	0	0	0	01-JAN-05 00.00.00	0	0	0	0     
-405.71	0	0	0	02-JAN-05 00.00.00	0	0	0	0     
0	-760.71	0	0	03-JAN-05 00.00.00	0	0.75	0	0     

Query i have used to modify:
 
select        SUM (CASE r.type WHEN 'T1' THEN b.balance ELSE 0 END)                AS t1_val
,         SUM (CASE r.type WHEN 'T2' THEN b.balance ELSE 0 END)                AS t2_val
,         SUM (CASE r.type WHEN 'T3' THEN b.balance ELSE 0 END)                AS t3_val
,         SUM (CASE r.type WHEN 'T4' THEN b.balance ELSE 0 END)                AS t4_val,
b.bal_date
,         MIN (CASE WHEN r.type='T1' THEN r.charge  ELSE 0 END)  AS t1
,         MIN (CASE WHEN r.type='T2' THEN r.charge  ELSE 0 END)  AS t2
,         MIN (CASE WHEN  r.type='T3' THEN r.charge  ELSE 0 END)  AS t3
,         MIN (CASE WHEN r.type='T4' THEN r.charge  ELSE 0 END)  AS t4
from bal b, reference_table r
where b.bal_date between r.eff_from_date and r.eff_to_date
and abs(round(b.balance)) between r.min_amt and r.max_amt
GROUP BY  b.id, b.bal_date
order by bal_date;

Derivation condition:

01. First to check bal_date falling in start and end dates of reference table taking into first record
- 01-JAN-05 falling between the dates and check balance i.e 11.71 falls between T1 i.e 0 -15 then place it at T1_val and rest T2_val,t3_val,t4_val as zero

01-JAN-05 00.00.00-11.71000
Then place the charge value in T1 - say here it will be 0 hence
1 01-JAN-05 00.00.00 -11.71 0 0 0 0 0 0 0
If there bal_date falling in periods table then adjust T1 range like 0-500 (500 will default value)and T2 range 501-1000, T3 range 1001 - 2000, T4 2001-5000

Code:
The ranges needs to be shifted like below from reference table
 
T1	0	500	0
T2	501	1000	0.75
T3	1001	2000	1.5
T4	2001	5000	3

Code:
Scripts:
CREATE TABLE "REFERENCE_TABLE"
(
  "PRODUCT"       NUMBER(4, 0),
  "EFF_FROM_DATE" DATE,
  "EFF_TO_DATE"   DATE,
  "TYPE"          CHAR(2 BYTE),
  "MIN_AMT"       NUMBER(10, 0),
  "MAX_AMT"       NUMBER(10, 0),
  "CHARGE"        NUMBER(5, 2)
);
 
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,CHARGE) values (12,to_date('01-JAN-05 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('01-JAN-06 00.00.00','DD-MON-RR HH24.MI.SS'),'T1',0,15,0);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,CHARGE) values (12,to_date('01-JAN-05 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('01-JAN-06 00.00.00','DD-MON-RR HH24.MI.SS'),'T2',16,1000,0.75);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,CHARGE) values (12,to_date('01-JAN-05 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('01-JAN-06 00.00.00','DD-MON-RR HH24.MI.SS'),'T3',1001,2000,1.5);
Insert into reference_table (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,CHARGE) values (12,to_date('01-JAN-05 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('01-JAN-06 00.00.00','DD-MON-RR HH24.MI.SS'),'T4',2001,5000,3);
 
CREATE TABLE "BAL"
(
  "ID"       NUMBER,
  "BALANCE"  NUMBER,
  "BAL_DATE" DATE
);
 
Insert into bal (ID,BALANCE,BAL_DATE) values (1,-11.71,to_date('01-JAN-05 00.00.00','DD-MON-RR HH24.MI.SS'));
Insert into bal (ID,BALANCE,BAL_DATE) values (1,-405.71,to_date('02-JAN-05 00.00.00','DD-MON-RR HH24.MI.SS'));
Insert into bal (ID,BALANCE,BAL_DATE) values (1,-760.71,to_date('03-JAN-05 00.00.00','DD-MON-RR HH24.MI.SS'));
 
CREATE TABLE "PERIODS"
(
  "START_DATE" DATE,
  "END_DATE"   DATE,
  "ID"         NUMBER
);
 
Insert into periods (START_DATE,END_DATE,ID) values (to_date('02-JAN-05 00.00.00','DD-MON-RR HH24.MI.SS'),to_date('13-OCT-05 00.00.00','DD-MON-RR HH24.MI.SS'),1);

 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to print mon and max values of ranges

HI all I'm trying to write an awk script to print the min and max value in a range(s) contained in another file - the range values are in $2 EG 114,7964,1,y,y,n 114,7965,1,y,y,n 114,7966,1,y,y,n 114,7967,1,y,y,n 114,7969,1,y,y,n 114,7970,1,y,y,n 114,7971,1,y,y,n 114,7972,1,y,y,n... (3 Replies)
Discussion started by: Mudshark
3 Replies

2. Shell Programming and Scripting

find command: various date ranges

Hi, I have writtena script that will recursivly go into subdirecotries and report out what files there are in there that have not been accessed over various date ranges. I do this using a number of find commands: find . -path './.snapshot' -prune -o -type f -atime -8 find... (4 Replies)
Discussion started by: littleIdiot
4 Replies

3. Shell Programming and Scripting

Search values between ranges in File1 within File2

Hi people, I have 2 files, one with a list of non consecutive ranges (File1.txt), where each range begins with the value in column 1 and finishes with the value in column 2 in the same line, as can be seen above. 215312581156279 215312581166279 215312582342558 215312582357758... (4 Replies)
Discussion started by: cgkmal
4 Replies

4. Shell Programming and Scripting

date ranges

Hi, Please anyone help to achive this using perl or unix scripting . This is date in my table 20090224,based on the date need to check the files,If file exist for that date then increment by 1 for that date and check till max date 'i.e.20090301 and push those files . files1_20090224... (2 Replies)
Discussion started by: akil
2 Replies

5. Shell Programming and Scripting

getting files between specific date ranges in solaris

hi ! how can i get files in a directory between certain date ranges ? say all files created/modified between Jan24 - Jan31 thanks (10 Replies)
Discussion started by: aliyesami
10 Replies

6. Shell Programming and Scripting

extracting columns falling within specific ranges for multiple files

Hi, I need to create weekly files from daily records stored in individual monthly filenames from 1999-2010. my sample file structure is like the ones below: daily record stored per month: 199901.xyz, 199902.xyz, 199903.xyz, 199904.xyz ...199912.xyz records inside 199901.xyz (original data... (4 Replies)
Discussion started by: ida1215
4 Replies

7. Shell Programming and Scripting

Values between ranges

Hi, I have two files file1 chr1_22450_22500 chr2_12300_12350 chr1_34500_34550 file2 11000_13000 15000_19000 33000_44000 If the file 1 ranges fall between file2 ranges then assign the value of file2 in column 2 to file1 output: chr2_12300_12350 11000_13000 chr1_34500_34550 ... (7 Replies)
Discussion started by: Diya123
7 Replies

8. UNIX for Dummies Questions & Answers

Extracting rows from a text file based on the values of two columns (given ranges)

Hi, I have a tab delimited text file with multiple columns. The second and third columns include numbers that have not been sorted. I want to extract rows where the second column includes a value between -0.01 and 0.01 (including both numbers) and the first third column includes a value between... (1 Reply)
Discussion started by: evelibertine
1 Replies

9. Shell Programming and Scripting

Date derivation

Need to get saturday's date of the previous week. Input will be the sysdate (today's date) Output should be previous weeks's saturday format Your help is required extremely. I have done all the date arthimetic calculation, but not getting the desired output. Please help (10 Replies)
Discussion started by: help_scr_seeker
10 Replies

10. Shell Programming and Scripting

Grepping the data between 2 date ranges

Hi There, Good Day !! I have txt file containing data in the below format. There are many lines, here i have mentioned for example. cat remo.txt 2/3/2017 file1 3/4/2016 file2 6/6/2015 file5 1/1/2018 file3 4/3/2014 file4 - - - I need to grep the file names for given date rage... (11 Replies)
Discussion started by: kumar85shiv
11 Replies
All times are GMT -4. The time now is 10:46 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy