DSXchange

this is what i was looking for...
thanks a lot for proving different approaches

i think i didnot explain my requirements clearly.. the time_dim table contains week_code for each fiscal date. and week code ranges from 200701.......200752,200801......200852 and so on.i need to run the job every month and every month i need latest 13 weeks of data.So its better to write a where co...

actually i want to pull the data on the basis of week_code.
where week_code between recent week_code and week_code 13 weeks before.
i want to pass data_date and extract the week_code values refering to another table time_dim

hi all i have a table attr_fact where i am storing 52 weeks of history.I want to pull data from that table and put it in a sequential table.I need only 13 weeks(latest 13 weeks) of data.I am having a field week_code in that table which is being populated by look up on time_dim table.time_dim table i...

ok...i got the soultion to some extent..let me try this sql...i am also thinking of using rank so that i could be able to do further processing

Hi all... I am having a Time_Dim table in my database where there are 3 columns..Fiscal_Date,Year_Week,Year_Month.For each date in fiscal_date field there is Year_Month and Year_Week.I mean for 365 days in a year the Time_Dim is populated with 365 entries.Suppose for fiscal date 20070101 the table h...

yes...handling this situation with two jobs is a good and effective idea.but as per my requirement i need to create a single job to handle the situation.please advise me

Our requirement is like this: For the fields CUST_ID , CURRENT_ACCOUNT_NUMBER from the source table CUST_ACCT_DIM ,nonmon information will be captured from different sources and kept in the table CUST_C360_DIM Layout of CUST_ACCT_DIM ****************************** CUST_ACCT_DIM_KEY CUST_ID CURRENT_A...

yesi got the result.i was also trying the same thing but i missed the subquery.
thanks a lot for your help

can you please suggest me how to make this kind of join if my data base is oracle

CREATE TABLE TAB1 AS select a.ID,a.MTHCODE FROM CBFACT a where a.mthcode in(select max(mthcode) FROM CBFACT) CREATE TABLE TAB3 AS SELECT b.ID,b.ABC,b.XYZ,b.weekcode FROM ATTR b WHERE b.weekcode in (select max(weekcode) from attr) SELECT TAB1.ID,TAB1.MTHCODE,TAB3.WEEKCODE,TAB3.XYZ,TAB3.ABC FROM TAB1,...

cbfact | attr *********** ********* id monthcode pqr id weekcode abc xyz aa 200606 1234 | aa 200625 1256 2563 bb 200606 4563 | bb 200625 1563 1563 cc 200606 4589 | cc 200625 1256 2563 dd 200606 4568 dd 200624 7865 4569 ee 200605 4189 | output id montcode weekcode abc xyz aa 200606 200625 1256 2563 b...

this is the sample data what i want cbfact attr id monthcode pqr id weekcode abc xyz aa 200606 1234 aa 200625 1256 2563 bb 200606 4563 bb 200625 1563 1563 cc 200606 4589 cc 200625 1256 2563 dd 200606 4568 dd 200624 7865 4569 ee 200605 4189 output id montcode weekcode abc xyz aa 200606 200625 1256 25...

i have tried this sql but its giving incorrect records..
the problem here is that from cb fact we need to find the latest month code data and from attr we need to find the latest week data.and perform left outer join.

i have two tables cbfact and attr.there are 3 columns in cb fact: id,monthcode,pqr and 4 columns in attr:id,weekcode,abc,xyz.i want to have a left outer join between the two tables on the basis of latest partition.latest partition means from mothcode current monthcode data will be compared with curr...