All Forums UDA
RGlass 35 posts Joined 09/04
26 Oct 2009
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_Qty2 200,041 2009-10-13 2009-10-17 35.002 200,041 2009-10-18 2009-10-19 33.002 200,041 2009-10-20 2009-10-20 30.002 200,041 2009-10-21 2009-10-21 21.002 200,041 2009-10-22 9999-12-31 33.00View: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_Qty2 200041 2009-10-13 352 200041 2009-10-14 352 200041 2009-10-15 352 200041 2009-10-16 352 200041 2009-10-17 352 200041 2009-10-18 332 200041 2009-10-19 332 200041 2009-10-20 302 200041 2009-10-21 212 200041 2009-10-22 33RDBMS 12.00.02.17Any Ideas appreciated.Thanks, R Glass

27 Oct 2009

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.SampleSELECT ..,..,..,..FROM TAB AINNER JOIN {DATE TABLE} BON B.Date_of_day BETWEEN A.eff_bgn_date AND A.eff_end_date;

g.eswar 2 posts Joined 10/09
27 Oct 2009

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_dtorder by 3 ;

Thanks,
Eswar

You must sign in to leave a comment.