Hi Guys,
I am having below tables in oracle
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
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
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
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
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