|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/26/2009 2:51:50 PM
Posts: 4,
Visits: 13
|
|
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
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: Yesterday @ 12:42:08 AM
Posts: 74,
Visits: 32
|
|
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;
Strive to success. Arun.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/28/2009 12:33:17 PM
Posts: 2,
Visits: 17
|
|
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
Teradata consultant
|
|
|
|