SQL Question.

UDA
Enthusiast

SQL Question.


User would like a view built on table that would return a record for each date between eff_bgn_dt and eff_end_dt having invry_qty constant for each group of records.

Table Structure:
CREATE TABLE PRODUCT.RTL_INVRY_RDF_EOD
(
Loc_Id SMALLINT NOT NULL ,
Wd_Cd INTEGER NOT NULL ,
Eff_Bgn_Dt DATE NOT NULL
FORMAT 'YYYY-MM-DD' ,
Eff_End_Dt DATE NOT NULL
FORMAT 'YYYY-MM-DD' ,
Invry_Qty DECIMAL(9,2) NOT NULL ,
Adt_Id INTEGER NOT NULL
)
PRIMARY INDEX RTL_INVRY_RDF_EOD_NUPI
(
Loc_Id,
Wd_Cd
)
;

sel loc_id,wd_cd,eff_bgn_dt,eff_end_dt,invry_qty
from dsb.rtl_invry order by 3 ;
Loc_Id Wd_Cd Eff_Bgn_Dt Eff_End_Dt Invry_Qty
2 200,041 2009-10-13 2009-10-17 35.00
2 200,041 2009-10-18 2009-10-19 33.00
2 200,041 2009-10-20 2009-10-20 30.00
2 200,041 2009-10-21 2009-10-21 21.00
2 200,041 2009-10-22 9999-12-31 33.00

View:
example:
sel loc_id,wd_cd,eff_bgn_dt ,invry_qty
from dsb.rtl_invry order by 3 ;
Loc_Id Wd_Cd Eff_Bgn_Dt Invry_Qty
2 200041 2009-10-13 35
2 200041 2009-10-14 35
2 200041 2009-10-15 35
2 200041 2009-10-16 35
2 200041 2009-10-17 35
2 200041 2009-10-18 33
2 200041 2009-10-19 33
2 200041 2009-10-20 30
2 200041 2009-10-21 21
2 200041 2009-10-22 33

RDBMS 12.00.02.17

Any Ideas appreciated.
Thanks, R Glass

2 REPLIES
Enthusiast

Re: SQL Question.

Hi,
The simplest solution I see is to Inner Join your current table to a Table containing all dates as columns with a BETWEEN clause for the eff_bgn_date and eff_end_date. There is usually a DATE datamart in every DWH implementation for such purposes. Get in touch if you need further help.

Sample
SELECT ..,..,..,..
FROM TAB A
INNER JOIN {DATE TABLE} B
ON B.Date_of_day BETWEEN A.eff_bgn_date AND A.eff_end_date;

Re: SQL Question.

HI there ,

The following query will provide exact results for your requirement.

sel loc_id,wd_cd,calendar_date,eff_bgn_dt ,invry_qty

from dsb.rtl_invry,

sys_calendar

where calendar_date between eff_bgn_dt AND eff_end_dt

order by 3 ;

Eswar