Home » SQL & PL/SQL » SQL & PL/SQL » Time Bucket Outer (Oracle 10g)
Time Bucket Outer [message #563771] Thu, 16 August 2012 23:43 Go to next message
vino06cse57
Messages: 131
Registered: July 2011
Location: chennai
Senior Member
Hi

I need a Help in joining the below 2 tables.i had used "betweeen and " for Joining the table by using Inner Bound and Outer bound Key Columns and i use (current date - due_dt) for day calulation

My Requrement is to show all the time bukets if at all no days falls in the particulat time bucket.as like Outer join on Time bucket table


Loan_Account_summary

SNo    	Due_dt       	AMT
-----------------------------
1	02-08-2012	50000
2	03-05-2012	45000
3	09-05-2012	30000
4	15-09-2012	25000
5	31-05-2012	15000
6	08-06-2012	35788
7	19-10-2012	55000


Time Bucket

TB_ID	TB_DESC		Reserve%	Innner Bound  outer Bound
------------------------------------------------------------------
101	1-7		2.25                1            7
102	8-30		2.5                 8            30
103	31-60		3                   31           60
104	61-90		1.75                61           90
105	91-120		1.5                 91           120
106	121-180		1.25                121          180
107	180+		0                   181          99999


Expected Output

TB_DESC       Reserve AMT
----------------------------
1-7		500000
8-30		154822
31-60		0
61-90		750000
91-120		0
120-180		0
180+		65842	

Re: Time Bucket Outer [message #563775 is a reply to message #563771] Fri, 17 August 2012 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 59087
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:

Michel Cadot wrote on Tue, 12 June 2012 07:49
From your previous topics:

Michel Cadot wrote on Mon, 20 February 2012 12:55
...Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
...
Also always post your Oracle version, with 4 decimals.

...


jrnayak wrote on Mon, 23 January 2012 12:07
...Please provide the test data and test case.


Michel Cadot wrote on Tue, 10 January 2012 13:42
You learn nothing since your previous topics.
...For any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
...



And also

Michel Cadot wrote on Tue, 24 January 2012 15:47
Michel Cadot wrote on Tue, 24 January 2012 15:45

A feedback should welcome and fair.
...



Something you NEVER gave in your previous topics.
I don't see any reason to spend more time to help someone who despises us after getting his answer.
...


Follow what is in red.

Regards
Michel

Re: Time Bucket Outer [message #563787 is a reply to message #563775] Fri, 17 August 2012 02:29 Go to previous message
vino06cse57
Messages: 131
Registered: July 2011
Location: chennai
Senior Member
Hi

The Below code is for Time Bucket Table,Business Unit and Product
when i join this three tables i must get all business units all products and All time buckets


CREATE TABLE BI_BUSINESS_UNIT_MST
(
  BU_NO  VARCHAR2(20 BYTE),
  BU_NM  VARCHAR2(30 BYTE)
);
Insert into BI_AD_BUSINESS_UNIT_MST
   (BU_NO, BU_NM)
 Values
   ('100', 'Head Office');
Insert into BI_AD_BUSINESS_UNIT_MST
   (BU_NO, BU_NM)
 Values
   ('101', 'Magomeni');
Insert into BI_AD_BUSINESS_UNIT_MST
   (BU_NO, BU_NM)
 Values
   ('102', 'Ilala');
Insert into BI_AD_BUSINESS_UNIT_MST
   (BU_NO, BU_NM)
 Values
   ('103', 'Temeke');
Insert into BI_AD_BUSINESS_UNIT_MST
   (BU_NO, BU_NM)
 Values
   ('201', 'Morogoro');
Insert into BI_AD_BUSINESS_UNIT_MST
   (BU_NO, BU_NM)
 Values
   ('202', 'Dodoma');
Insert into BI_AD_BUSINESS_UNIT_MST
   (BU_NO, BU_NM)
 Values
   ('203', 'Ifakara');
Insert into BI_AD_BUSINESS_UNIT_MST
   (BU_NO, BU_NM)
 Values
   ('204', 'Mpwapwa');
Insert into BI_AD_BUSINESS_UNIT_MST
   (BU_NO, BU_NM)
 Values
   ('205', 'Kibaya');
Insert into BI_AD_BUSINESS_UNIT_MST
   (BU_NO, BU_NM)
 Values
   ('301', 'Iringa');
Insert into BI_AD_BUSINESS_UNIT_MST
   (BU_NO, BU_NM)
 Values
   ('302', 'Mbeya');
Insert into BI_AD_BUSINESS_UNIT_MST
   (BU_NO, BU_NM)
 Values
   ('303', 'Njombe');
Insert into BI_AD_BUSINESS_UNIT_MST
   (BU_NO, BU_NM)
 Values
   ('304', 'Songea');
Insert into BI_AD_BUSINESS_UNIT_MST
   (BU_NO, BU_NM)
 Values
   ('401', 'Mwanza');
Insert into BI_AD_BUSINESS_UNIT_MST
   (BU_NO, BU_NM)
 Values
   ('402', 'Mara');
Insert into BI_AD_BUSINESS_UNIT_MST
   (BU_NO, BU_NM)
 Values
   ('403', 'Bukoba');
Insert into BI_AD_BUSINESS_UNIT_MST
   (BU_NO, BU_NM)
 Values
   ('404', 'Bunda');
Insert into BI_AD_BUSINESS_UNIT_MST
   (BU_NO, BU_NM)
 Values
   ('405', 'Tarime');
Insert into BI_AD_BUSINESS_UNIT_MST
   (BU_NO, BU_NM)
 Values
   ('501', 'Shinyanga');
Insert into BI_AD_BUSINESS_UNIT_MST
   (BU_NO, BU_NM)
 Values
   ('502', 'Kahama');
Insert into BI_AD_BUSINESS_UNIT_MST
   (BU_NO, BU_NM)
 Values
   ('503', 'Geita');
Insert into BI_AD_BUSINESS_UNIT_MST
   (BU_NO, BU_NM)
 Values
   ('601', 'Arusha');
Insert into BI_AD_BUSINESS_UNIT_MST
   (BU_NO, BU_NM)
 Values
   ('602', 'Tanga');
Insert into BI_AD_BUSINESS_UNIT_MST
   (BU_NO, BU_NM)
 Values
   ('603', 'Muheza');


CREATE TABLE BI_PROD_MST
(
  PROD_CD    VARCHAR2(10 BYTE),
  PROD_DESC  CHAR(50 BYTE)
);
Insert into BI_AD_PROD_MST
   (PROD_CD, PROD_DESC)
 Values
   ('101', 'Village Banking');
Insert into BI_AD_PROD_MST
   (PROD_CD, PROD_DESC)
 Values
   ('102', 'Business Loan');
Insert into BI_AD_PROD_MST
   (PROD_CD, PROD_DESC)
 Values
   ('103', 'Small Group Loan');
Insert into BI_AD_PROD_MST
   (PROD_CD, PROD_DESC)
 Values
   ('104', 'Staff Salary Loan');
COMMIT;

CREATE TABLE BI_MGMRT_TM_BUCKET_MST
(
  TB_ID               NUMBER,
  TB_OUTER_BOUND      NUMBER,
  TB_DESC             VARCHAR2(20 BYTE),
  TB_RESERVE_PERCENT  NUMBER,
  TB_INNER_BOUND      NUMBER
)
/

SET DEFINE OFF;
Insert into BI_MGMRT_TM_BUCKET_MST
   (TB_ID, TB_OUTER_BOUND, TB_DESC, TB_RESERVE_PERCENT, TB_INNER_BOUND)
 Values
   (101, 7, '1-7', 0.01, 1);
Insert into BI_MGMRT_TM_BUCKET_MST
   (TB_ID, TB_OUTER_BOUND, TB_DESC, TB_RESERVE_PERCENT, TB_INNER_BOUND)
 Values
   (102, 30, '8-30', 0.01, 8);
Insert into BI_MGMRT_TM_BUCKET_MST
   (TB_ID, TB_OUTER_BOUND, TB_DESC, TB_RESERVE_PERCENT, TB_INNER_BOUND)
 Values
   (103, 60, '31-60', 0.25, 31);
Insert into BI_MGMRT_TM_BUCKET_MST
   (TB_ID, TB_OUTER_BOUND, TB_DESC, TB_RESERVE_PERCENT, TB_INNER_BOUND)
 Values
   (104, 90, '61-90', 0.5, 61);
Insert into BI_MGMRT_TM_BUCKET_MST
   (TB_ID, TB_OUTER_BOUND, TB_DESC, TB_RESERVE_PERCENT, TB_INNER_BOUND)
 Values
   (105, 120, '91-120', 1, 91);
Insert into BI_MGMRT_TM_BUCKET_MST
   (TB_ID, TB_OUTER_BOUND, TB_DESC, TB_RESERVE_PERCENT, TB_INNER_BOUND)
 Values
   (106, 180, '121-180', 1, 121);
Insert into BI_MGMRT_TM_BUCKET_MST
   (TB_ID, TB_OUTER_BOUND, TB_DESC, TB_RESERVE_PERCENT, TB_INNER_BOUND)
 Values
   (107, 99999999, '180+', 1, 181);
COMMIT;

CREATE TABLE LN
(
  BU_NO       VARCHAR2(10 BYTE)                 NOT NULL,
  DUE_DAYS    NUMBER,
  PROD_CD     VARCHAR2(10 BYTE)                 NOT NULL,
  BALANCE     NUMBER,
  AMT_PAID    NUMBER,
  AMT_UNPAID  NUMBER
)
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('402', 156, '102', 3609136708.51, 1996321.27, 
    423761306.72);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('102', 383, '103', 1555205932.54, 2965640.02, 
    238899084.82);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('201', 30, '101', 6339377964.78, 5692465.97, 
    906815826.16);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('501', 170, '102', 3049097484.44, 832277.53, 
    394485076.9);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('601', 165, '102', 5136990348.92, 3298542.42, 
    757215643.56);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('100', 242, '104', 922595040.96, 1389285.93, 
    65038566.12);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('602', 166, '102', 2736861245.9, 485539.37, 
    367445298.68);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('503', 3, '101', 3398341939.65, 1966416.56, 
    456512808.23);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('303', -39, '101', 1843968450.1, 695355.71, 
    280181477.7);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('401', 46, '103', 1837988062.58, 1840628.52, 
    330212136.83);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('401', 170, '102', 7056103405, 9515312.34, 
    854123856.36);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('403', 115, '102', 2991083614.37, 1650061.39, 
    395297229.14);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('103', 158, '101', 3534238244.84, 6686560.23, 
    528729937.54);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('302', 87, '103', 1376457755.25, 728502.29, 
    282301644.11);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('502', 120, '102', 4029870749.59, 5084297.88, 
    506909599.24);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('202', 52, '101', 4792831748.03, 4176209.05, 
    691998252.38);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('603', -159, '102', 428766666.71, 0, 
    50400000.01);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('103', 171, '102', 4604067337.91, 2913643.04, 
    499811635.5);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('403', 171, '101', 5645900288.32, 2302732.19, 
    723840600.6);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('602', 165, '101', 4470739357.49, 6439247.32, 
    549907327.59);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('404', 161, '102', 3103887494.52, 1039130.95, 
    352952423.51);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('205', 29, '101', 3437562848.12, 3189419.15, 
    532126579.77);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('601', 31, '101', 5145551869.87, 1052723.64, 
    639892115.82);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('301', -161, '103', 220408333.9, 0, 
    43408333.46);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('202', -166, '103', 120700000, 0, 
    21450000);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('204', -160, '103', 24000000, 0, 
    4000000);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('401', 150, '101', 5501825611.11, 18002775.86, 
    702581859.84);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('203', 138, '101', 3146539801.7, 12571259.32, 
    633259376.53);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('302', 101, '102', 4030903586.11, 3651975.25, 
    610011273.34);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('302', 145, '101', 4635217154.37, 1338470.1, 
    668150202.68);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('301', 169, '101', 3392293725.97, 5258638.25, 
    518803350.17);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('405', 200, '102', 3321531713.85, 3200372.74, 
    462840152.5);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('304', -6, '102', 1107690660.99, 99333.31, 
    130560665.35);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('501', 161, '101', 3758759996.94, 2156042.19, 
    568579955.42);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('601', -159, '103', 473141667.62, 0, 
    90258333.6);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('203', -168, '103', 178866666.65, 0, 
    30283333.33);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('201', 170, '102', 2528897546.05, 3678374.17, 
    384903526.35);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('101', 505, '102', 8000781931.18, 4575853.91, 
    1042756882.38);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('102', 171, '102', 2836680419.27, 2248216.79, 
    368200109.31);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('603', 171, '101', 2514394795.69, 3020944.92, 
    317969221.34);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('204', 37, '101', 2045375486.59, 1890792.46, 
    404571482.08);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('103', 169, '103', 1345692131.63, 5735778.78, 
    286343576.91);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('202', 131, '102', 5701311246.16, 2847912.81, 
    741211853.27);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('304', 53, '101', 3054598040.41, 137973.92, 
    356827441.23);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('304', -158, '103', 114466666.76, 0, 
    22316666.7);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('101', 164, '101', 9962867117.79, 19945194.53, 
    1351469863.53);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('102', 166, '101', 4040994626.02, 11654262.32, 
    507787393.98);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('203', 94, '102', 3037041571.95, 676700.22, 
    345551907.47);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('303', 155, '102', 2767188111.97, 4032173.16, 
    381802348.76);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('405', 163, '101', 3900357025.18, 20170955.11, 
    575016178.08);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('303', -158, '103', 163983109.01, 0, 
    32406621.83);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('101', 171, '103', 1926411607.84, 4716839.84, 
    374609700.28);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('301', 158, '102', 7920021118.46, 3834125.92, 
    930400640.65);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('503', 99, '102', 5553270374.44, 3118703.48, 
    719657845.41);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('502', 151, '101', 2405737964.27, 6469149.05, 
    322693266.75);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('602', 302, '103', 1021337175.93, 2179158.22, 
    195896677.71);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('204', 150, '102', 938815025.19, 304094.03, 
    120247854.62);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('402', 156, '101', 3104480862.43, 4189830.89, 
    469436981.19);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('205', 47, '102', 1284474609.26, 118441.93, 
    140321837.16);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('404', 163, '101', 5079531923.72, 2877124.36, 
    592017125.12);
Insert into LN
   (BU_NO, DUE_DAYS, PROD_CD, BALANCE, AMT_PAID, AMT_UNPAID)
 Values
   ('603', -155, '103', 882174558.51, 2193.69, 
    174794472.9);
COMMIT;

Expected out put

BU_NO  Prod_cd  TB_ID Bal Payment Reserve Late Chrg BAL_DT
------------------------------------------------------------
100     101       101     0         0         0      03-08-2012
100     101       102    100        25       .25     03-08-2012
100     101       103    250        125      .5      03-08-2012
100     101       104    0           0         0      03-08-2012
100     101       105    0           0          0     03-08-2012
100     101       106    0           0         0       03-08-2012
100     101       107    0           0         0        03-08-2012

Previous Topic: Handling exceptions in bulk collect
Next Topic: ORA-01031,ORA-06512 dbms_session.set_context error
Goto Forum:
  


Current Time: Tue Sep 16 18:50:57 CDT 2014

Total time taken to generate the page: 0.10681 seconds