Subtract values based on records


 
Thread Tools Search this Thread
Top Forums Programming Subtract values based on records
# 1  
Old 02-13-2016
Subtract values based on records

Hi Guys,

I am having below tables in oracle

Code:
T1			
ID	F_TYPE	F_AMT	DATE_COL
1	F	6	11-Feb-16
1	D	2	11-Feb-16
1	D	2	11-Feb-16
1	F	6	11-Feb-16
1	F	2	12-Mar-16
1	D	3	12-Mar-16
1	F	4	10-Apr-16
1	F	4	11-Apr-16
1	D	1	11-Apr-16

T2
ID	START_DATE	END_DATE	F_ID	FLAG
1	11-Feb-16	11-Mar-16	1	Y
1	12-Mar-16	11-Apr-16	1	Y
1	12-Mar-16	11-Apr-16	1	Y
1	12-Apr-16	21-Apr-16	2	N
1	22-Apr-16	02-May-16	3	N

Output required if

To check how many records are with flag 'N' based on f_id from T2 table if there are more than one dont apply the subtract , in this case there is only one record
02. The date_col from T1 should be falling between the start date and end dates of T2 table where flag is Y
For eg

ID F_TYPE F_AMT DATE_COL
1 F 6 11-Feb-16
date_col is falling between start and end date of T2 table for first f_id and flag = 'Y';
03. If there is a record with f_type as D for same date_col then subtract f_amt of F - f_amt with D
D F_TYPE F_AMT DATE_COL Derived
1 F 6 11-Feb-16 6-2 = 4
And also only once the subtraction should happen, in this case there is another record with f_type F with same date_col
1 F 6 11-Feb-16
Then it should be subtracted with zero
1 F 6 11-Feb-16 6-0 = 6
04. If there are not record with D for that date_col of F then it should be subtracted with zero
1 F 4 10-Apr-16 4-0 = 4

Code:
Output
Output required
  
ID	F_TYPE	F_AMT	DATE_COL	Derived
1	F	6	11-Feb-16	6-2 = 4
1	F	6	11-Feb-16	6-0 = 6
1	F	2	12-Mar-16	2-3 = -1
1	F	4	11-Apr-16	4-1 = 3
1	F	4	10-Apr-16	4-0 = 4

The above output should only work if there are no more than one record with flag as N in T2 table

If its something like this
Code:
ID	START_DATE	END_DATE	F_ID	FLAG
1	11-Feb-16	11-Mar-16	1	Y
1	12-Mar-16	11-Apr-16	1	Y
1	12-Mar-16	11-Apr-16	1	Y
1	12-Apr-16	21-Apr-16	2	N
1	22-Apr-16	02-May-16	3	N

Then output should be
  
ID	F_TYPE	F_AMT	DATE_COL	Derived
1	F	6	11-Feb-16	6-0 = 6
1	F	6	11-Feb-16	6-0 = 6
1	F	2	12-Mar-16	2-0 = 2
1	F	4	11-Apr-16	4-0 = 4
1	F	4	10-Apr-16	4-0 = 4

I,e no subtraction should happen with f_type as 'D'

Scripts
Code:
CREATE TABLE "T1"
(
  "ID"       NUMBER,
  "F_TYPE"   CHAR(1 BYTE),
  "F_AMT"    NUMBER(10, 0),
  "DATE_COL" DATE
);
 
CREATE TABLE "T2"
(
  "ID"         NUMBER,
  "START_DATE" DATE,
  "END_DATE"   DATE,
  "F_ID"       NUMBER,
  "FLAG"       CHAR(1 BYTE)
);
 
Insert into T1 (ID,F_TYPE,F_AMT,DATE_COL) values (1,'F',6,to_date('11-FEB-2016','DD-MON-YYYY'));
Insert into T1 (ID,F_TYPE,F_AMT,DATE_COL) values (1,'D',2,to_date('11-FEB-2016','DD-MON-YYYY'));
Insert into T1 (ID,F_TYPE,F_AMT,DATE_COL) values (1,'D',2,to_date('11-FEB-2016','DD-MON-YYYY'));
Insert into T1 (ID,F_TYPE,F_AMT,DATE_COL) values (1,'F',6,to_date('11-FEB-2016','DD-MON-YYYY'));
Insert into T1 (ID,F_TYPE,F_AMT,DATE_COL) values (1,'F',2,to_date('12-MAR-2016','DD-MON-YYYY'));
Insert into T1 (ID,F_TYPE,F_AMT,DATE_COL) values (1,'D',3,to_date('12-MAR-2016','DD-MON-YYYY'));
Insert into T1 (ID,F_TYPE,F_AMT,DATE_COL) values (1,'F',4,to_date('10-APR-2016','DD-MON-YYYY'));
Insert into T1 (ID,F_TYPE,F_AMT,DATE_COL) values (1,'F',4,to_date('11-APR-2016','DD-MON-YYYY'));
Insert into T1 (ID,F_TYPE,F_AMT,DATE_COL) values (1,'D',1,to_date('11-APR-2016','DD-MON-YYYY'));
 
Insert into T2 (ID,START_DATE,END_DATE,F_ID,FLAG) values (1,to_date('11-FEB-2016','DD-MON-YYYY'),to_date('11-MAR-2016','DD-MON-YYYY'),1,'Y');
Insert into T2 (ID,START_DATE,END_DATE,F_ID,FLAG) values (1,to_date('12-MAR-2016','DD-MON-YYYY'),to_date('11-APR-2016','DD-MON-YYYY'),1,'Y');
Insert into T2 (ID,START_DATE,END_DATE,F_ID,FLAG) values (1,to_date('12-MAR-2016','DD-MON-YYYY'),to_date('11-APR-2016','DD-MON-YYYY'),1,'Y');
Insert into T2 (ID,START_DATE,END_DATE,F_ID,FLAG) values (1,to_date('12-APR-2016','DD-MON-YYYY'),to_date('21-APR-2016','DD-MON-YYYY'),2,'Y');
Insert into T2 (ID,START_DATE,END_DATE,F_ID,FLAG) values (1,to_date('22-APR-2016','DD-MON-YYYY'),to_date('02-MAY-2016','DD-MON-YYYY'),3,'N');

Code i have used and stuck with

Code:
WITH  got_f_id_cnt    AS
(
    SELECT  DISTINCT start_date, end_date
    ,       COUNT (DISTINCT f_id) OVER ()  AS f_id_cnt
    FROM    t2
    WHERE   flag  = 'N'
)
SELECT    t1.id
,         SUM (CASE WHEN t1.f_type = 'F' THEN f_amt END)  AS f_amt
,         t1.date_col,max(f_type)
,         SUM ( CASE
                    WHEN  t1.f_type   = 'F'  THEN  f_amt
                    WHEN  t2.f_id_cnt = 1    THEN  0
                    WHEN  t1.f_type   = 'D'  THEN  -f_amt
                                             ELSE  0
                END
              )                                           AS derived_amt
FROM      t1
JOIN      got_f_id_cnt  t2  ON   t1.date_col  BETWEEN  t2.start_date
                                              AND      t2.end_date
GROUP BY  t1.id, t1.date_col
ORDER BY  t1.id, t1.date_col


Last edited by Scrutinizer; 02-13-2016 at 09:55 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to subtract from values in file

data.txt: 0,mq_conn_open_error,1444665949,734,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62022,0,733--734 0,mq_conn_open_error,1444666249,734,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62022,0,734--734... (7 Replies)
Discussion started by: SkySmart
7 Replies

2. Shell Programming and Scripting

Copy header values into records

I'm using a shell script to manipulate a data file. I have a large file with two sets of data samples (tracking memory consumption) taken over a long period of time, so I have many samples. The problem is that all the data is in the same file so that each sample contains two sets of data.... (2 Replies)
Discussion started by: abercrom
2 Replies

3. Shell Programming and Scripting

Add values in 2 columns and subtract from third

Hi All, I have a file with thousands of lines in the following format, where Field1=First 8 characters Field2-9-16 characters Field3=17-26 characters I need to add Field 1 and Field2 and subtract the result from Field 3. Field3=Field3 - (Field1 + Field2) 0012.00 0010.00 0001576.53... (4 Replies)
Discussion started by: nua7
4 Replies

4. Shell Programming and Scripting

Multiple records based on :

Hi , I have the below source source data 1|2|3|:123:abc|4 1|2|a| | 5 1|2|3|4|:a:s:D.....:n|t Target data should be 1|2|3|:123:abc|4 1|2|3|:123:abc|4 1|2|a| | 5 1|2|3|4|:a:s:D.....:n|t 1|2|3|4|:a:s:D.....:n|t 1|2|3|4|:a:s:D.....:n|t 1|2|3|4|:a:s:D.....:n|t (3 Replies)
Discussion started by: mora
3 Replies

5. UNIX for Advanced & Expert Users

Split records based on '-'

HI, I have a pipe delimiter file , I have to search for second field pattern, if the second field does not contain a '-' , I need to start capturing the record from this line till I find another second field with '-' value. Below is the sample data SOURCE DATA ABC|ABC_702148-PARAM... (3 Replies)
Discussion started by: mora
3 Replies

6. Shell Programming and Scripting

Ignore records with no values through awk

Hi Guys, Hope you are doing well out there. I have to format the output of a script. Current output is auktltbr.dc-dublin.de:4322 ICCIR2Test13-PB-01 active auktltbr.dc-dublin.de:8322 ICCIR2Test13-SB-02 active auktlttr.dc-dublin.de:4422 ICCIR2Test24-CB-02 active... (10 Replies)
Discussion started by: singh.chandan18
10 Replies

7. Shell Programming and Scripting

Subtract field values

I've got a long logfile of the form network1:123:45:6789:01:234:56 network2:12:34:556:778:900:12 network3:... I've got a similar logfile from a week later with different values for each of the fields eg network1:130:50:6800:10:334:66 network2:18:40:600:800:999:20 network3:... ... (5 Replies)
Discussion started by: Yorkie99
5 Replies

8. Shell Programming and Scripting

How to pick values from column based on key values by usin AWK

Dear Guyz:) I have 2 different input files like this. I would like to pick the values or letters from the inputfile2 based on inputfile1 keys (A,F,N,X,Z). I have done similar task by using awk but in that case the inputfiles are similar like in inputfile2 (all keys in 1st column and values in... (16 Replies)
Discussion started by: repinementer
16 Replies

9. Shell Programming and Scripting

Based on num of records in file1 need to check records in file2 to set some condns

Hi All, I have two files say file1 and file2. I want to check the number of records in file1 and if its atleast 2 (i.e., 2 or greater than 2 ) then I have to check records in file2 .If records in file2 is atleast 1 (i.e. if its not empty ) i have to set some conditions . Could you pls... (3 Replies)
Discussion started by: mavesum
3 Replies

10. UNIX for Dummies Questions & Answers

Generating key values for leader records

All, I have a file with text as shown below. I want the o/p file with generated values in the first column as shown in the o/p file. Pls note that the size of my file is 6 GB. How do i do this ? Input file 999999abcdef 999999ghijkl 999999mnopq 777777rosesarered 777777skyisblue Output... (1 Reply)
Discussion started by: ajfaq
1 Replies
Login or Register to Ask a Question